This article explains how to use Excel’s TRUNC function, which is a rounding function that can be used to truncate (shorten) the target number to a set number of decimal places—without rounding the remaining digits or whole number.

What to Know

  • Select a cell > Formulas tab > Math & Trig > TRUNC. In dialog box, select Number line.Go back to active cell to enter reference into dialog box. In box, select Num_digit line.Type number of desired decimal places > OK.

TRUNC Function Example: Truncate to a Set Number of Decimal Places

This example covers the steps used to enter the TRUNC function into cell B4 in the image above to truncate the mathematical value of Pi in cell A4 to two decimal places.

Options for entering the function include manually typing in the entire function =TRUNC(A4,2), or using the function’s dialog box – as outlined below:

  • Click on cell B4 to make it the active cell.
  • Click on the Formulas tab of the ribbon menu.
  • Choose Math & Trig from the ribbon to open the function drop-down list.
  • Click on TRUNC in the list to bring up the function’s dialog box.
  • In the dialog box, click on the Number line.
  • Click on cell A4 in the worksheet to enter that cell reference into the dialog box.
  • In the dialog box, click on the Num_digit line.
  • Type a " 2" (no quotations) on this line in order to reduce the value of Pi to two decimal places.
  • Click OK to complete the function and close the dialog box.
  • The answer 3.14 should be present in cell B4.
  • When you click on cell B4 the complete function =TRUNC(A4,2) appears in the formula bar above the worksheet.

Truncate Values to a Set Number of Decimal Places

The function only rounds numbers when the Num_digits argument is a negative value – rows seven to nine above.

In these instances, the function removes all decimal values and, depending on the value of Num_digits, rounds the number down to that many digits.

For example, when Num_digits is:

  • Equal to -1, the number is rounded down to the nearest value of 10. Shown in row seven.Equal to -10, the number is rounded down to the nearest value of 100. Shown in row eight.Equal to -100, the number is rounded down to the nearest value of 1,000. Shown in row nine.

The TRUNC Function’s Syntax and Arguments

A function’s syntax refers to the layout of the function and includes the function’s name, brackets, and arguments.

The syntax for the TRUNC function is:

= TRUNC (Number, Num_digits)

Number - the value to be truncated. This argument can contain:

  • The actual data to be truncated. Shown in rows two and three above.
  • The results of a formula. Shown in row three above, where TRUNC is used to shorten the value returned by the PI function.
  • A cell reference to the location of the data in the worksheet. Shown in rows four to nine above.
  • The reference can also point to a cell containing the value returned by a formula – the value in cell A4 is generated by the PI function.

Num_digits (Optional): The number of decimal places to be left by the function.

  • If Num_digits is omitted a default value of zero is used for this argument and all decimal places are removed and a non-rounded integer value is returned by the function. Shown in row five above.

Using the Truncated Number in Calculations

Like other rounding functions, the TRUNC function actual alters the data in your worksheet and will, therefore affect the results of any calculations that use the truncated values.

There are, on the other hand, formatting options in Excel that allow you change the number of decimal places displayed by your data without changing the numbers themselves.

Making formatting changes to data has no effect on calculations.

Get the Latest Tech News Delivered Every Day