To remove decimal places without rounding, use TRUNC. With the original number in A2 and a target of two decimal places, enter =TRUNC(A2,2); for example, 18.739 becomes 18.73. If you mean round the value instead, use ROUND. Excel’s TRIM function removes extra spaces from text—it does not remove decimal digits from a number.
First decide whether you want to change the stored value, round it, or only change how it looks on the worksheet. These are different jobs, and choosing the right one helps avoid unexpected calculations later.
What You’ll Need
- Excel for Microsoft 365 on Windows, or access to a compatible Excel version.
- A worksheet containing the numbers you want to change. The examples below assume the source value is in A2.
- An empty destination cell if you want to preserve the original values while testing a formula.
Step-by-Step
- Decide what “trim” means for your task. If you want to discard digits after a chosen decimal place, use TRUNC. If you want to round to the nearest value, use ROUND. If you only want fewer digits to appear on screen, change the number format instead. For example, turning 18.739 into 18.73 by truncation is not the same as rounding it to 18.74.
- Choose a destination cell. Keep the source column unchanged at first so you can compare results. With the original number in A2 and the formula entered in B2, use
=TRUNC(A2,2)to keep two decimal places without rounding. If A2 contains 18.739, B2 returns 18.73. The second argument, 2, specifies the number of decimal places to retain. - Enter the formula. Select B2, type the formula, and press Enter. You can also select the cell, type an equals sign, click A2, and complete the rest of the formula. The source cell reference should point to the number you want to trim. If your data begins in another cell, replace A2 with that cell reference.
- Check that the result is numeric and correct. Click the result cell and look at the formula bar to confirm the formula is present. Compare the result with the original number. With 18.739 in A2, the two-place TRUNC formula returns 18.73; it does not round the final 9 up. If you see the original number unchanged, check the cell reference and the number format.
- Fill the formula down for more rows. With source values in A2:A20 and the first result formula in B2, select B2 and drag its fill handle down through B20. Excel adjusts each relative reference, so the formula in B3 refers to A3, and so on. You can also select the result range and use Home > Fill > Down. Check a few rows, especially values near a rounding boundary and any negative values.
- Use ROUND if you want conventional rounding. With the source number in A2 and a two-decimal result in B2, enter
=ROUND(A2,2). For 18.739, the result is 18.74 because the next digit is 9. ROUND changes the calculated value, not merely its appearance. Use it when the intended rule is to round to the nearest hundredth. - Use ROUNDDOWN or ROUNDUP when you need a specific direction. With 18.739 in A2 and two decimal places required,
=ROUNDDOWN(A2,2)returns 18.73, while=ROUNDUP(A2,2)returns 18.74. ROUNDDOWN moves toward zero, and ROUNDUP moves away from zero. These functions are useful when the direction matters more than ordinary rounding, but confirm that the rule suits your task. - Handle whole numbers and negative values deliberately. With a number in A2,
=TRUNC(A2,0)removes the fractional part toward zero. For 18.739 it returns 18; for -18.739 it returns -18. By contrast, with -18.739 in A2,=INT(A2)returns -19 because INT rounds down toward the next lower integer. This difference matters when negative values are possible. - Use number formatting if you only want to hide decimal places. Select the cells, then choose Home > Number > Decrease Decimal until the desired number of places is displayed. This changes the display, not the underlying stored number. A value of 18.739 may appear as 18.74 while calculations still use 18.739. To set an exact display format, open Format Cells with Ctrl+1 on Windows, choose Number, and set Decimal places. On Mac, Command+1 opens Format Cells; ribbon labels can vary slightly by Excel release.
- Replace formulas with fixed values only if needed. A formula result changes if its source changes. If you need a fixed copy, copy the result cells and use Home > Paste > Paste Values. Keep the original source data until you have checked the pasted values. On Excel for the web, the same formula functions work, though some ribbon labels and the Format Cells dialog may be arranged differently.
Common Mistakes
- Using TRIM to remove decimal places. TRIM cleans extra spaces from text; it does not truncate a numeric value. For example, if A2 contains the text value with surrounding spaces,
=TRIM(A2)removes those spaces but does not round or truncate the digits. If the cleaned text contains a number that Excel should calculate with, and A2 contains text such as 125.40 with extra spaces,=VALUE(TRIM(A2))returns the numeric value 125.4. The displayed trailing zero may disappear unless the cell is formatted to show two decimal places. - Confusing a displayed number with its stored value. Decrease Decimal can make a number look shorter without changing it. This can lead to totals that appear inconsistent with the visible figures. Use TRUNC or ROUND when calculations must use the altered value; use formatting when only the display needs to change.
- Assuming TRUNC rounds down. TRUNC cuts off digits toward zero. For negative numbers, that is different from rounding down. For instance, TRUNC of -18.739 to an integer gives -18, whereas INT gives -19. Check how your formula should treat negatives before filling it down.
- Overwriting the source column too soon. Replacing original values with formula results makes it harder to verify or change the rule. Calculate in a separate column, inspect the outcomes, and only then decide whether to paste values over the source.
- Forgetting that a formula can return a number without showing trailing zeros. A result of 12.5 and a result displayed as 12.50 can have the same numeric value. Apply a two-decimal number format if the visible zeros are important for consistency.
Tips
- Keep the original data and put the trimmed results in a neighboring column until the output is confirmed.
- Use TRUNC with a positive second argument to keep decimal places, zero to keep only the integer part, and a negative argument to truncate to the left of the decimal point. For example, with 1234 in A2,
=TRUNC(A2,-2)returns 1200. - Use cell formatting to show a consistent number of decimal places in reports. A format with two decimal places can display 12.5 as 12.50 without changing its value.
- When copying a formula, check that relative references move as expected. If every row should refer to the same fixed cell, use an absolute reference such as $A$2; otherwise leave the reference relative.
- Excel formulas here use commas between arguments. Depending on your regional settings, Excel may require semicolons instead. If Excel flags a correctly written formula as invalid, check the list separator configured for your system.
Troubleshooting
The formula appears as text instead of calculating: The cell may be formatted as Text, or the formula may begin with an apostrophe. Change the cell format to General, edit the cell, and press Enter again. Also confirm that the formula begins with an equals sign and that Show Formulas is not enabled.
The result has the wrong number of decimal places: Check the second argument in TRUNC or ROUND. A 2 keeps two decimal places; a 0 keeps none. Also check the result cell’s number format, because formatting can hide digits or add displayed zeros without changing the calculated value.
A formula returns #VALUE!: The source may contain text that is not recognized as a number, or a mixture of numbers and labels. Check the source cell for spaces, punctuation, or text markers. If the value is text with harmless surrounding spaces and a number format Excel recognizes, use VALUE with TRIM as shown above. If the decimal or thousands separators do not match your regional settings, correct the source or import settings first.
The result differs for negative numbers: Confirm whether you need truncation toward zero, rounding to the nearest value, or rounding down. TRUNC and ROUNDDOWN move toward zero; INT moves down to the next integer. Test a negative example before applying the formula to the full range.
The total still uses digits that are not visible: You probably changed the display format rather than the stored values. Use a formula such as TRUNC or ROUND in a helper column if calculations must use the shortened values. Recalculate totals from that helper column.
Frequently Asked Questions
What Excel formula removes decimal places without rounding?
Use TRUNC. With the value in A2 and two places to retain, =TRUNC(A2,2) removes later digits without rounding; 18.739 becomes 18.73.
Does Excel TRIM remove decimals?
No. TRIM removes extra spaces from text. Use TRUNC to cut off decimal digits, ROUND to round to a chosen number of places, or number formatting to change only the display.
What is the difference between TRUNC and ROUND?
TRUNC cuts off digits at the specified position. ROUND adjusts the last retained digit based on the next digit. For 18.739 at two decimal places, the results are 18.73 and 18.74, respectively.
How can I remove all digits after the decimal point?
With a number in A2, =TRUNC(A2,0) returns the integer part toward zero. If you instead want the next lower integer for negative values, use INT.
Can I keep two decimal places without changing the value?
Yes. Apply a number format that displays two decimal places. This can show trailing zeros, but calculations still use the original stored value.
Can I trim decimals in Excel for the web?
Yes. TRUNC, ROUND, ROUNDDOWN, and ROUNDUP are available in Excel for the web. Enter the formula in a cell as you would in desktop Excel; formatting controls may appear in a slightly different location.
Related
- Excel Custom Number Format: How to Create and Apply It
- How to Select a Sheet in Excel With Keyboard Shortcuts
- Select in Excel Without Scrolling: Fast, Simple Methods
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Percent Change Calculator Excel Formula and ExamplesIn Excel, calculate percent change as (new value − old value) ÷ old value. If the old value…
- Freeze a Formula in Excel: Keep Cell References FixedTo freeze a cell reference while copying a formula in Excel, edit the formula, select the reference, and…
- Remove Filter From an Excel Table: Steps and OptionsTo remove the filter arrows from an Excel table in Microsoft 365 for Windows, click a cell in…
- Copy Formulas in Excel Exactly: Keep References FixedTo copy a formula in Excel, select its cell, press Ctrl+C, select the destination, and press Ctrl+V. Excel…
- Excel Goal Seeking: Find a Target Value with What-If ToolsExcel Goal Seek finds the input value that makes a formula return a result you specify. In Excel…
- Excel: How to Filter a Table Based on a List of ValuesYes. In Excel for Microsoft 365, you can return only the rows in a table whose values appear…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.