How to How to Use TRUNC Function in Excel in Excel - Practical Guide

Learn to use the TRUNC function to remove decimal places from numbers without rounding. This tutorial covers basic syntax, practical examples, and how to truncate to any decimal position. Essential for financial calculations, data cleaning, and precise number formatting in spreadsheets.

Why This Matters

TRUNC is critical for financial reporting and data analysis where you need exact truncation instead of rounding. It ensures consistent decimal handling across large datasets.

Prerequisites

•Basic understanding of Excel formulas and cell references
•Familiarity with the formula bar and how to enter functions

Step-by-Step Instructions

Open Excel and Select a Cell

Launch Microsoft Excel and click on the cell where you want the truncated result to appear (e.g., cell C2).

Enter the TRUNC Formula

Type =TRUNC(number, [num_digits]) where 'number' is your value and 'num_digits' is the decimal places to keep. Example: =TRUNC(15.789, 2) returns 15.78.

Reference Your Data

Replace 'number' with a cell reference (like A2) containing your decimal value. Keep num_digits as your desired decimal places or omit it for whole numbers.

Press Enter to Execute

Press Enter on your keyboard to execute the formula and display the truncated result in the selected cell.

Copy Formula Down (Optional)

Select the cell with the formula, copy it (Ctrl+C), then select the range below and paste (Ctrl+V) to apply TRUNC to multiple rows.

Alternative Methods

Using INT Function

INT removes all decimal places and rounds down, similar to TRUNC for positive numbers. Use =INT(value) for quick whole number conversion.

Using ROUND Function

ROUND both truncates and rounds based on num_digits. Use =ROUND(value, 2) if you want mathematical rounding instead of truncation.

Using TEXT Function

TEXT formats numbers as text while truncating decimals. Use =TEXT(value, "0.00") for display purposes only, not calculations.

Tips & Tricks

✓TRUNC truncates without rounding—it simply removes digits beyond your specified decimal position.
✓Use negative num_digits to truncate before the decimal: =TRUNC(1234.567, -2) returns 1200.
✓TRUNC works with both positive and negative numbers accurately.

Pro Tips

★Combine TRUNC with other functions like SUM or AVERAGE for precise financial calculations: =SUM(TRUNC(A:A, 2)).
★Use TRUNC in conditional formulas to enforce maximum decimal places in data validation rules.
★For currency, TRUNC is safer than ROUND when calculating totals to avoid cumulative rounding errors.

Troubleshooting

Formula shows #VALUE! error

Ensure the first parameter is a numeric cell reference or value, not text. Check that your cell doesn't contain apostrophes or special characters.

Results show unexpected decimals

Verify your num_digits parameter—it specifies how many decimals to keep, not remove. Adjust the second parameter accordingly.

TRUNC not working with percentages

Convert percentages to decimals first or multiply by 100: =TRUNC(A1*100, 2) to truncate percentage values correctly.

Related Excel Formulas

ROUND INT FLOOR CEILING TEXT ROUNDDOWN

Frequently Asked Questions

What's the difference between TRUNC and ROUND?

TRUNC removes decimal places without rounding; ROUND adjusts the last digit up or down based on the next digit. For 15.78, TRUNC(15.789, 2)=15.78, but ROUND(15.789, 2)=15.79.

Can TRUNC handle negative numbers?

Yes, TRUNC works with negative numbers correctly. For example, TRUNC(-15.789, 2) returns -15.78, truncating toward zero without rounding.

What if I omit the num_digits parameter?

Omitting num_digits defaults to 0, so TRUNC(15.789) returns 15, removing all decimal places and converting to a whole number.

Can I use TRUNC with cell ranges?

TRUNC works on individual cells or values, not entire ranges directly. Use it in an array formula or combine with other functions for multiple cells.

Why use TRUNC instead of just formatting decimals?

Formatting only changes display; TRUNC actually modifies the underlying value, essential for calculations and preventing rounding errors in financial data.