How to How to Remove Decimals Without Rounding in Excel in Excel - Practical Guide

Learn how to remove decimal places from numbers in Excel while preserving the actual value without rounding. This tutorial covers the INT, TRUNC, and TEXT functions, plus formatting techniques to truncate decimals for display or data manipulation purposes.

Why This Matters

Removing decimals without rounding is essential for financial data, inventory counts, and reporting where precise truncation (not rounding) ensures accurate calculations and cleaner displays.

Prerequisites

•Basic Excel navigation and cell selection
•Understanding of decimal numbers and rounding differences

Step-by-Step Instructions

Select Your Data Range

Click on the cell containing decimal numbers you want to modify, or select a range by clicking and dragging across multiple cells.

Use the INT Function

Enter the formula =INT(A1) in a new cell to remove all decimals by truncating toward zero (works for positive numbers). For example, 23.87 becomes 23.

Apply TRUNC Function for Negative Numbers

Use =TRUNC(A1) instead if your data includes negative numbers, as it truncates toward zero for both positive and negative values. Specify decimals: =TRUNC(A1,1) keeps one decimal.

Copy Formula Down to All Rows

Select the cell with your formula, copy it (Ctrl+C), then select the target range and paste (Ctrl+V) to apply truncation to all numbers.

Replace Original Values

Copy the formula results, then use Paste Special (Ctrl+Shift+V) and select 'Values only' to replace original decimals with truncated numbers.

Alternative Methods

TEXT Function for Display Only

Use =TEXT(A1,"0") to format decimals as whole numbers for display without changing actual values. This is ideal when you need original data intact.

Format Cells Dialog

Right-click cells > Format Cells > Numbers tab > set Decimal places to 0. This hides decimals visually but doesn't change underlying values.

Find & Replace with Regex

Use Find & Replace (Ctrl+H) with regular expressions enabled to remove decimal portions in bulk. Pattern: (\d+)\.\d+ > Replace: $1.

Tips & Tricks

✓INT works only on positive numbers; use TRUNC for mixed positive/negative data.
✓Always duplicate your data before applying formulas to preserve original values as backup.
✓Use TRUNC(A1,2) to keep exactly 2 decimals if you need selective truncation, not complete removal.
✓Paste Special > Values prevents formula references breaking if you move or delete source cells.

Pro Tips

★Combine TRUNC with ROUND to control both direction and precision: =TRUNC(A1*100)/100 removes decimals beyond 2 places.
★For large datasets, use Find & Replace with regex patterns to truncate thousands of cells in seconds without formulas.
★Create a helper column with TRUNC, then use Data > Consolidate to merge and replace original columns efficiently.

Troubleshooting

Formula shows #VALUE! error

Ensure the cell references valid numbers, not text. If data is text, use VALUE() function first: =TRUNC(VALUE(A1)).

Decimals still appear after applying TRUNC

You likely used TEXT or formatting instead of actual truncation. Replace with =TRUNC() formula and paste as values.

Results differ between INT and TRUNC

INT rounds down for positive and negative; TRUNC always truncates toward zero. Choose based on your data type.

Related Excel Formulas

TRUNC INT ROUND TEXT VALUE

Frequently Asked Questions

What's the difference between removing decimals and rounding?

Removing decimals (truncation) simply cuts off decimal places: 5.99 becomes 5. Rounding considers the next digit: 5.99 becomes 6. Truncation preserves exact values without mathematical adjustment.

Can I remove decimals without creating a new column?

Yes, use Find & Replace with regex (Ctrl+H), enable 'Regular expressions', and use pattern (\d+)\.\d+ to replace with $1 directly in your original data.

Which function works for both positive and negative numbers?

TRUNC is the best choice for mixed positive/negative data because it truncates toward zero consistently. INT rounds toward negative infinity, giving different results for negative numbers.

How do I keep some decimals but remove others?

Use TRUNC with a second parameter: =TRUNC(A1,1) keeps 1 decimal, =TRUNC(A1,2) keeps 2 decimals. This lets you control precision while removing unwanted decimal places.