When working with financial data, scientific measurements, or complex pricing formulas in Microsoft Excel, you frequently encounter numbers with long strings of decimal places. The standard approach to cleaning up these numbers is to use the formatting tools to hide the decimals, or to use the ROUND function. However, both of these methods alter the underlying value of the data based on mathematical rounding rules.
If you have a value of 14.89 and you use the ROUND function to remove the decimals, Excel will change the number to 15. If your specific accounting workflow or engineering specification requires you to simply cut off the decimals without altering the whole number—leaving you with exactly 14—you must use the TRUNC function.
What is the TRUNC Function?
TRUNC stands for “truncate,” which literally means to cut short. The TRUNC function forces Excel to aggressively chop off a specific number of digits from a value without performing any rounding calculations whatsoever. It completely ignores whether the decimal is a .1 or a .9; it simply deletes the characters.
This is critical in scenarios like calculating discounted retail prices where you always round down to the nearest dollar, or when determining the maximum number of whole units that can fit into a container, where a fractional unit is useless.
How to Use the TRUNC Function
The syntax for the TRUNC function is incredibly simple. It only requires two arguments, and the second argument is entirely optional.
=TRUNC(number, [num_digits])
- number: This is the value, or the cell reference containing the value, that you want to truncate. This is mandatory.
- num_digits: This tells Excel exactly how many decimal places you want to keep. If you leave this blank, Excel assumes it is zero and will remove all decimals.
Example 1: Removing All Decimals
If cell A2 contains the value 356.987 and you want to reduce it to a whole number without rounding up to 357.
- Click on an empty cell (for example, B2).
- Type the formula:
=TRUNC(A2) - Press Enter.
The cell will instantly display 356. The .987 has been discarded completely.
Example 2: Keeping Specific Decimal Places
Perhaps you are working with currency and you need to keep exactly two decimal places, but you want to discard any fractions of a cent generated by an interest calculation.
If cell A3 contains the value 104.5582, and you need it to be exactly 104.55.
- Click on an empty cell.
- Type the formula:
=TRUNC(A3, 2) - Press Enter.
The cell will display 104.55. Notice that even though the third decimal was an 8, Excel did not round the number up to 104.56.
The Difference Between TRUNC and INT
Many Excel users confuse the TRUNC function with the INT (Integer) function. While they behave identically when dealing with positive numbers (both will turn 10.9 into 10), they operate differently when handling negative numbers.
The INT function rounds numbers down to the nearest whole integer. Therefore, if you use =INT(-10.9), Excel will round it further down to -11.
The TRUNC function simply chops off the decimal regardless of the sign. If you use =TRUNC(-10.9), Excel will return -10.
Because of this predictability, TRUNC is generally the safer and more accurate function to use when your only goal is to remove decimal places without altering the mathematical value of the whole number.