Use the Excel TRUNC Function to Remove Decimals Without Rounding

01
of 01

Truncate Values to a Set Number of Decimal Places

Truncate vs. Rounding Numbers in Excel with the TRUNC Function
Truncate vs. Rounding Numbers in Excel with the TRUNC Function. © Ted French

The TRUNC function is one of Excel's group of rounding functions even though it may or may not round the identified number.

As its name suggests, it can be used to truncate or shorten the target number to a set number of decimal places without rounding the remaining digits or whole number.

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 - row seven;
  • Equal to -10, the number is rounded down to the nearest value of 100 - row eight;
  • Equal to -100, the number is rounded down to the nearest value of 1,000 - 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 - rows two and three above;
  • The results of a formula - row three above where TRUNC is used to shorten the value returned by the PI function.
  • cell reference to the location of the data in the worksheet - 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 - row five above.

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

Entering the TRUNC Function

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

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.

Format
mla apa chicago
Your Citation
French, Ted. "Use the Excel TRUNC Function to Remove Decimals Without Rounding." ThoughtCo, Jun. 4, 2017, thoughtco.com/excel-trunc-function-remove-decimals-3123987. French, Ted. (2017, June 4). Use the Excel TRUNC Function to Remove Decimals Without Rounding. Retrieved from https://www.thoughtco.com/excel-trunc-function-remove-decimals-3123987 French, Ted. "Use the Excel TRUNC Function to Remove Decimals Without Rounding." ThoughtCo. https://www.thoughtco.com/excel-trunc-function-remove-decimals-3123987 (accessed January 20, 2018).