Master the TRUNC Formula in Excel: Complete Guide to Truncating Numbers
=TRUNC(number, [num_digits])The TRUNC function is one of Excel's most straightforward yet powerful mathematical tools for removing decimal places from numbers without rounding. Whether you're working with financial data, scientific calculations, or simple data cleanup, TRUNC provides a reliable way to truncate numbers to a specified number of decimal places. Unlike the ROUND function which rounds values based on mathematical rules, TRUNC simply cuts off digits after a specified position, making it ideal for situations where you need precise control over decimal precision. Understanding TRUNC is essential for anyone working with numerical data in Excel. This beginner-friendly formula has been available since Excel 2007 and remains consistent across all modern versions including Excel 365. The function works seamlessly with both positive and negative numbers, making it versatile for various business applications. From preparing financial reports to cleaning up measurement data, TRUNC helps you maintain data integrity while simplifying complex datasets. Learning this formula will enhance your Excel skills and improve your data management efficiency significantly.
Syntax & Parameters
The TRUNC function uses a simple two-parameter syntax: =TRUNC(number, [num_digits]). The first parameter, 'number', is required and represents the value you want to truncate. This can be a cell reference, a direct number, or even a formula result. The second parameter, 'num_digits', is optional and specifies how many decimal places to keep. If you omit num_digits, Excel defaults to zero, removing all decimal places entirely. When num_digits is positive, TRUNC keeps that many decimal places. For example, TRUNC(3.7529, 2) returns 3.75, not 3.76. When num_digits is negative, TRUNC truncates to the left of the decimal point. TRUNC(12345, -2) returns 12300, removing the last two digits. This parameter flexibility makes TRUNC incredibly useful for various scenarios. A crucial distinction from ROUND is that TRUNC never rounds values—it simply removes digits. This makes it perfect for situations where you need absolute precision without mathematical rounding affecting your results. Always remember that TRUNC preserves the original number's sign and works identically for both positive and negative values.
numbernum_digitsPractical Examples
Removing Decimal Places from Product Prices
=TRUNC(A2, 2)Cell A2 contains 19.9876. Using TRUNC with num_digits set to 2 removes all decimal places beyond the second, resulting in exactly 19.98. This prevents unintended price increases that would occur with rounding.
Converting Decimal Hours to Whole Hours
=TRUNC(B3, 0)Cell B3 contains 8.75. TRUNC with num_digits set to 0 removes all decimal places, leaving only the integer portion. This gives the exact number of complete hours worked without rounding.
Simplifying Large Numbers for Reports
=TRUNC(C4, -5)Cell C4 contains 1234567.89. Using TRUNC with num_digits set to -5 removes the last five digits, truncating to the nearest hundred thousand. This returns 1200000, providing a clean figure for high-level reporting.
Key Takeaways
- TRUNC removes decimal places without rounding, providing absolute precision control for numerical data manipulation
- The optional num_digits parameter can be positive (decimals) or negative (left of decimal), offering flexible truncation at any position
- Unlike INT which rounds toward negative infinity, TRUNC truncates toward zero, making it consistent for both positive and negative numbers
- TRUNC integrates seamlessly with other Excel functions, enabling powerful combinations for data formatting, validation, and complex calculations
- Available across all Excel versions from 2007 to 365, TRUNC remains a reliable, beginner-friendly formula for professional data management
Pro Tips
Use TRUNC with negative num_digits to simplify large numbers. For example, TRUNC(1234567, -3) returns 1234000, perfect for creating simplified figures in executive summaries without manually editing cells.
Impact : Saves time when preparing rounded figures for presentations and reduces manual data entry errors significantly.
Combine TRUNC with IFERROR to handle potential errors gracefully. Use =IFERROR(TRUNC(A1, 2), 0) to return 0 if the cell contains text or invalid data instead of displaying error messages.
Impact : Creates more robust spreadsheets that handle unexpected data types without breaking formulas or displaying unsightly error codes.
Remember that TRUNC truncates toward zero for both positive and negative numbers, while INT always rounds down. Choose TRUNC when you need consistent truncation behavior regardless of sign.
Impact : Prevents logic errors in calculations involving negative values, ensuring predictable results across all data types.
Use TRUNC in data validation formulas to ensure numbers meet specific precision requirements. For example, validate that TRUNC(A1, 2) equals A1 to confirm entries have maximum two decimal places.
Impact : Maintains data quality and consistency by enforcing decimal precision standards across entire datasets automatically.
Useful Combinations
Combine TRUNC with ROUND for Conditional Precision
=IF(MOD(A1, 1)>0.5, ROUND(A1, 0), TRUNC(A1, 0))This formula rounds up only when the decimal portion exceeds 0.5, otherwise truncates. Useful for custom rounding logic that combines both functions based on specific conditions, providing flexible control over decimal handling.
Use TRUNC with TEXT for Formatted Display
=TEXT(TRUNC(B2, 2), "$0.00")Truncates a number to two decimal places, then formats it as currency. This combination ensures both precise truncation and professional formatting, perfect for financial reports where you need exact decimal control with proper currency display.
Combine TRUNC with CONCATENATE for Data Formatting
=CONCATENATE(TRUNC(C3, 0), " hours and ", TRUNC(MOD(C3, 1)*60, 0), " minutes")Converts decimal hours into readable time format by truncating the whole hours and calculating remaining minutes. This powerful combination extracts time components from decimal values for human-readable reporting.
Common Errors
Cause: The 'number' parameter contains text or non-numeric data that Excel cannot process. For example: =TRUNC("ABC", 2) or =TRUNC(A1, 2) where A1 contains text.
Solution: Verify that your number parameter references cells containing actual numbers, not text. Use the ISNUMBER function to check data types first. If converting text to numbers, use VALUE function: =TRUNC(VALUE(A1), 2)
Cause: The formula is misspelled or the function name is not recognized. Common mistakes include =TRUNK(number, 2) or =TRUNCATE(number, 2) instead of =TRUNC.
Solution: Double-check the spelling is exactly 'TRUNC' with no extra letters. Use Excel's formula autocomplete feature which suggests the correct function name as you type.
Cause: The num_digits parameter is too extreme or invalid. While rare, providing an extremely large negative value might cause issues in some scenarios.
Solution: Ensure num_digits is a reasonable integer value. For most business applications, keep it between -5 and 5. If you need extreme truncation, break the operation into steps or use alternative approaches.
Troubleshooting Checklist
- 1.Verify the first parameter is numeric data—check for hidden text or spaces using the ISNUMBER function
- 2.Confirm num_digits is an integer—decimal values in this parameter may cause unexpected behavior
- 3.Check that your formula syntax exactly matches =TRUNC(number, [num_digits])—verify no typos or extra spaces
- 4.Ensure cells referenced in the formula contain actual values—empty cells or formula errors will propagate
- 5.Test with sample data first—use simple values like =TRUNC(3.7, 1) to confirm the formula works before applying to complex datasets
- 6.Compare results with ROUND and INT functions—this helps identify if TRUNC is the correct choice for your specific need
Edge Cases
Truncating a number that is already an integer
Behavior: TRUNC returns the original number unchanged. For example, TRUNC(5, 2) returns 5, not 5.00.
This is expected behavior. TRUNC doesn't add decimal places; it only removes them.
Using TRUNC with extremely large num_digits values
Behavior: If num_digits exceeds the actual decimal places in the number, TRUNC returns the original number. For example, TRUNC(3.14, 10) returns 3.14.
No error occurs; Excel simply returns the number as-is since there are no additional decimals to truncate.
Truncating numbers very close to zero
Behavior: TRUNC(0.001, 2) returns 0, and TRUNC(-0.001, 2) returns 0. Very small numbers truncate to zero when precision is reduced.
Solution: Use conditional logic with ABS function if you need to preserve sign information for near-zero values: =IF(A1=0, 0, SIGN(A1)*TRUNC(ABS(A1), 2))
This edge case matters when working with precise scientific or financial calculations where near-zero values have significance.
Limitations
- •TRUNC cannot truncate text representations of numbers—the number parameter must be actual numeric data, not text. Use VALUE() function to convert text to numbers first.
- •TRUNC has no built-in rounding logic—if you need mathematical rounding instead of truncation, use ROUND, ROUNDUP, or ROUNDDOWN functions instead.
- •TRUNC's num_digits parameter must be an integer—using decimal values in this parameter may produce unexpected results or errors in some scenarios.
- •TRUNC doesn't preserve formatting—truncating a formatted number returns only the numeric value. You must reapply formatting using TEXT function or cell formatting if needed.
Alternatives
Provides similar truncation behavior with slightly different syntax. ROUNDDOWN(number, num_digits) also removes decimals without rounding.
When: Use ROUNDDOWN when you want explicit control over rounding direction or when working in environments where TRUNC might not be available. Both achieve identical results for truncation purposes.
Removes all decimals in a simpler one-parameter format. INT(number) returns the integer portion of any number.
When: Use INT for quick integer extraction when you don't need decimal precision control. However, note that INT rounds toward negative infinity, making it unsuitable for negative numbers if you need truncation toward zero.
Rounds down to the nearest specified significance level, offering more granular control than TRUNC for specific rounding intervals.
When: Use FLOOR when you need to round down to specific increments (e.g., nearest 0.05 or nearest 100). This provides more flexibility than TRUNC for custom rounding scenarios.
Compatibility
✓ Excel
Since 2007
=TRUNC(number, [num_digits])✓Google Sheets
=TRUNC(number, [num_digits])Fully compatible with identical syntax and behavior. Works seamlessly in Google Sheets with no modifications required.
✓LibreOffice
=TRUNC(number, [num_digits])