TRUNC function

TRUNC removes decimal places from a number without rounding, returning a truncated integer—use it when you need precise decimal removal without rounding.

=TRUNC(number, [num_digits])

Generate a TRUNC formula

Describe what you need. The generator will reach for TRUNC where TRUNC is the right tool, and tell you when it is not.

How to get a better answer
  • Name your columns by letter and by header: "column F (Net Value)" beats "the amount column".
  • State every condition, including the negatives — "not cancelled" changes the formula's shape.
  • Say where the data starts if it is not row 1, and whether it will grow.
  • Check the settings above match your spreadsheet: the wrong argument separator is a syntax error on your machine.

Arguments

How TRUNC reads its arguments
numberrequirednum_digitsoptionalTRUNC →
ArgumentRequiredDescription
numberRequiredThe numeric value to truncate; can be a cell reference, formula result, or literal number.
num_digitsOptionalOptional. Number of decimal places to keep (positive), or digits left of decimal to remove (negative); defaults to 0 if omitted.

Returns

A number with the specified decimal places or digits removed, without rounding.

Availability

Excel: All · Google Sheets: Supported

Worked examples

1. Truncate product cost to 2 decimal places

SKUCost
A00112.5625
=TRUNC(E2, 2)

Result: 12.56

TRUNC(12.5625, 2) keeps only two decimal places and removes everything beyond. The result is 12.56, not 12.57—unlike ROUND, TRUNC simply discards the extra digits. This is useful when you need precise decimal truncation for display without rounding uncertainty.

2. Remove excess decimals from cost data

SKUCost
A00315.9999
=TRUNC(E3, 1)

Result: 15.9

TRUNC(15.9999, 1) keeps one decimal place. The result is 15.9 because TRUNC discards everything beyond the first decimal without rounding. This preserves one decimal of precision while removing the trailing 999, which is helpful for standardizing inconsistent cost data.

3. Truncate total inventory value to nearest $10

On HandCost per Unit
4512.5625
=TRUNC(C2*E2, -1)

Result: 560

This formula multiplies 45 units by $12.5625 each, giving $565.3125 total. TRUNC(..., -1) truncates to the nearest 10 by removing the ones place, yielding 560. Negative num_digits are powerful for rounding down to the nearest 10, 100, 1000, etc.

Common errors

Which TRUNC error are you seeing?
TRUNC returned an error#VALUE!
Verify the cell contains a number. If it contains text that looks like a number, wrap it with VALUE(): =TRUNC(VALUE(A1), 2)
#NUM!
Reduce the magnitude of num_digits to a reasonable range. For instance, use -5 instead of -500. Verify that the number being truncated is a valid number.
#NAME?
Check the spelling and use TRUNC exactly. Replace the misspelled function name with the correct spelling: =TRUNC(...)
ErrorWhy it happensHow to fix it
#VALUE!Passing a text string or a cell containing text to the number parameter, rather than a numeric value.Verify the cell contains a number. If it contains text that looks like a number, wrap it with VALUE(): =TRUNC(VALUE(A1), 2)
#NUM!num_digits is so large in magnitude (particularly negative) that it exceeds Excel's calculation limits, or number contains an invalid numeric representation.Reduce the magnitude of num_digits to a reasonable range. For instance, use -5 instead of -500. Verify that the number being truncated is a valid number.
#NAME?The function name is misspelled as TRUNCATE, TRUNK, TRUNCC, or another variant, preventing the spreadsheet from recognizing it as a valid function.Check the spelling and use TRUNC exactly. Replace the misspelled function name with the correct spelling: =TRUNC(...)

Tips and when to use something else

  • Use TRUNC when you need to discard decimals without rounding; use ROUND if you need proper rounding instead.
  • Negative num_digits removes digits before the decimal point: TRUNC(1234, -2) returns 1200.
  • For removing trailing decimals, TRUNC(value, 0) is equivalent to INT(value) for positive numbers.
  • TRUNC truncates toward zero, so TRUNC(-3.7) returns -3, not -4.

Related functions

ROUNDROUND returns a number rounded to a specified number of decimal places, using standard rounding rules (0.5 rounds up).ROUNDDOWNROUNDDOWN returns a number rounded down toward zero to the specified number of decimal places, useful for inventory and billing calculations.INTINT rounds any number down to the nearest whole integer, always discarding decimal places and rounding toward negative infinity.FLOOR.MATHRounds a number down to the nearest multiple of a specified significance value, commonly used for budget categories and interval-based rounding.ROUNDUPRounds a number up to a specified number of decimal places, always rounding away from zero, useful for conservative financial estimates.

Frequently asked questions

What's the difference between TRUNC and ROUND?
TRUNC discards decimals without rounding, while ROUND adjusts the last kept digit based on the next digit. ROUND(3.7, 0) = 4, but TRUNC(3.7, 0) = 3.
Can TRUNC remove digits before the decimal point?
Yes. Use negative num_digits. TRUNC(1234.56, -2) returns 1200, removing digits from the hundreds place.
Does TRUNC work with negative numbers?
Yes. TRUNC(-3.7, 0) returns -3 (truncating toward zero). It removes decimals but preserves the sign.
When would I use TRUNC instead of INT?
For most cases with positive numbers, they're equivalent. But TRUNC works correctly with negative numbers (toward zero), while INT always rounds down. Use TRUNC for consistency across positive and negative values.

Need a different formula?

The full generator is not scoped to one function — describe any spreadsheet problem and it will pick.

Open the formula generator

Reviewed 2026-09-17