How to Use the TRUNC Function in Microsoft Excel
Need to truncate a number in Excel to return a true integer? You can use the TRUNC function. Here’s how.
Analyzing Excel data is hard enough without dealing with decimal numbers. While it’s possible to round numbers up or down in Excel, this doesn’t necessarily change the data—it just changes how it’s displayed.
If you want to remove or set the number of decimal places for a number directly, you’ll need to use the TRUNC function. Here’s how to use the TRUNC function in Excel.
How to Use the TRUNC Function in Excel
The TRUNC function doesn’t round up a number but instead removes the decimal places, truncating it in the process (hence the function’s name). This creates a true integer number (a whole number, like 8 or 10), which may be useful in certain situations.
Using TRUNC with a number like 10.8, for instance, won’t round it up to 11. TRUNC will instead return 10, removing the .8 from the number. If you need to round up (or down) a number, you can use the ROUND function instead.
The basic format of a formula using the TRUNC function is =TRUNC(number), where the number is replaced with a decimal number. For instance, =TRUNC(10.8) will return 10, rather than 10.8.
You can also use cell references with TRUNC. If cell F2 contained the number 10.8, you could use the formula =TRUNC(B2) to achieve the same effect and return the number 10.
Setting the Number of Decimal Places Using TRUNC
If you don’t want to return a true integer number but instead allow for a certain number of decimal places, you can still use the TRUNC function to do so.
An optional argument to a formula using TRUNC can be used to specify the number of decimal places you want to leave intact. For instance, a number such as 10.54201 has five decimal places. If you want to leave just one (10.5), you could do this using TRUNC.
The format for this formula would be =TRUNC(number, num_digits), where number is the target number, and num_digits is the number of decimal places you want to keep. By default, the num_digits argument is optional—you don’t need to use it for a basic TRUNC formula. If you exclude it, it defaults to zero.
To truncate the number of decimal places from the number 10.54201, you could use the formula =TRUNC(10.54201,1). This will return the number 10.5, removing the following four decimal places.
Alternatives to the TRUNC Function
The TRUNC function isn’t useful for rounding up numbers, as we’ve previously mentioned. Other functions exist to do that, including INT and ROUND.
INT will round down a decimal number to the nearest integer. The formula =INT(10.8) will return the number 10, for instance.
You can also use ROUND to round a number to a set number of decimal points. For instance, =ROUND(10.8,0) will round to the nearest full integer number (11), while =ROUND(10.823,1) will round to one decimal place (10.8).
Only the TRUNC function, however, truncates the number. If that isn’t appropriate for your use case, and you need to manipulate the data to round up or down, use ROUND or INT or another Excel function instead.
Manipulating Data Using Microsoft Excel
Like ROUND, INT, and others, TRUNC is just one function that you can use to manipulate your data in Excel. Unlike those other functions, however, you don’t need to round up (or down) your numbers with TRUNC—it can be used to create true integer numbers without altering the integer itself.
Functions like these prove the rule—Excel functions sound difficult but are, in many cases, easy to master. If you’re new to Excel and you’re looking for help, try out these Microsoft Excel tips to get up-to-speed quickly.
