**How To Truncate Decimals In Excel**

Using The Function “TRUNC“

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.

**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.

**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**.

As you can see, the function returns 5.

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

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

**=TRUNC(A2)**

Then hit **Enter**.

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**.

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!

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)**

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.

**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.

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!

**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!