How To Truncate Decimals In Excel
Using The Function “TRUNC

Written by co-founder Kasper Langmann, Microsoft Office Specialist.

Excel is great about giving you exactly the numbers you want.

You can tell it how many decimal places you’re looking for, how it should round, and how many digits to display.

But sometimes you just want a shorter number. Excel can make that difficult.

With the TRUNC function, you can get shorter versions of numbers without getting rid of the original version. And that makes data easier to work with.

Let’s check it out.

Get your FREE exercise file

Truncating is pretty easy, but we’ve put together a spreadsheet to help you practice.

Download it below and follow along!

Download the FREE Exercise File

Download exercise file

How TRUNC works

The TRUNC function takes a number, cuts it down to the requested number of decimal places, and returns the result.

That’s it.

Note that Excel won’t round the number.

Let’s take a quick look at the syntax to see exactly how it works:

The syntax of the TRUNC function

=TRUNC(number, [digits])

number is the number you want to truncate. This can be specified as a number or a cell reference.

digits is an optional argument, and specifies the number of decimal places you’d like to remain after the truncation. If you leave this argument blank, Excel will truncate everything after the decimal point.

It’s very important to remember that TRUNC doesn’t do any rounding. The INT function, however, both gets rid of decimal places and rounds.

If you use TRUNC on 5.9, the result will be 5. If you use INT on 5.9, the result will be 6.

Kasper Langmann, Co-founder of Spreadsheeto

Using TRUNC with one argument

As we saw, only one argument is necessary to run the TRUNC function.

Let’s take a look at how that works.

Open the example workbook. On the first sheet, you’ll see a column full of numbers.

Click into cell B1, and type the following formula:

=TRUNC(5.67)

Then hit Enter.

trunc-one-arg

As you can see, the function returns 5.

Remember, when you don’t provide a second argument, TRUNC removes everything after the decimal point.

Kasper Langmann, Co-founder of Spreadsheeto

Let’s try the same thing with a cell reference. Click into B2 and type the following formula:

=TRUNC(A2)

Then hit Enter.

trunc-reference

As you probably expected, we get a similar result. All the numbers behind the decimal point have been removed.

Using TRUNC with two arguments

Now let’s take a look at where TRUNC gets more useful.

In cell B3, type the following formula:

=TRUNC(A3, 3)

And, again, hit Enter.

trunc-three-places

The result, 3.141, goes out to three decimal places, which is exactly what we asked Excel to do when we put a 3 in the second argument.

Try using TRUNC on the next two numbers to get a feel for how the function works!

Kasper Langmann, Co-founder of Spreadsheeto

In cells A6 and A7, we have two numbers that don’t have any numbers after the decimal point.

Let’s try using TRUNC on A6:

=TRUNC(A6, 2)

trunc-no-decimals

Notice that TRUNC doesn’t display extra zeroes when you tell it to show more decimals places than you had in the first place.

If you want to see 28917.000 this cell, for example, you’d need to change the number formatting. TRUNC won’t do it for you.

Kasper Langmann, Co-founder of Spreadsheeto

Negative digits in TRUNC

You can also use negative numbers for the digits argument in a TRUNC formula.

This is a bit counterintuitive, but once you see how it works, it’ll make more sense.

Click into cell B7, and type the following formula:

=TRUNC(A7, -1)

Hit Enter to see what it does.

trunc-negative

When you use a negative number as the second argument, Excel truncates the numbers before the decimal point.

It doesn’t, however, change the number of digits. So 721 became 720.

Again, TRUNC doesn’t round; so if you truncate 279, it will become 270.

Try truncating the number in A7 with a digits argument of -2 to see what happens!

Kasper Langmann, Co-founder of Spreadsheeto

Slice and dice decimals like a pro

The biggest advantage to using TRUNC is that you retain all of the information you started with—because the new number gets placed in a new cell.

If you want to see a slightly simpler version of your dataset, but you don’t want to lose data or round to the nearest integer, truncating will help.

Remember that if you want to get rid of decimals and round, you need the INT function.

If you can remember these two functions, you’ll be able to manipulate decimal places like a pro in no time!

Kasper Langmann, Co-founder of Spreadsheeto