Excel Trim Decimal Places: Format or Change Values

Formulas & functions · Updated November 18, 2026

To trim decimal places in Excel, select the cells and choose Home > Number > Decrease Decimal to change only how numbers look. To change the values themselves, use a formula such as =ROUND(A2,2) to round to two decimal places or =TRUNC(A2,2) to cut off extra digits without rounding.

Formatting versus changing the number

The first decision is whether you want fewer decimal places on screen or a different underlying value. Excel’s number formatting changes the display, not the stored number. A cell containing 12.345 can be shown as 12.35 while Excel still uses 12.345 in calculations. A formula such as ROUND, by contrast, returns a rounded result that other formulas can use.

This distinction matters for totals, comparisons, and exported data. If you only need a cleaner-looking report, formatting is usually the safest choice because it preserves the original precision. If a business rule requires every stored result to be rounded or truncated, use a formula and then, if needed, replace the original values with the results.

  • Use formatting when the goal is readability, such as showing prices to two decimal places.
  • Use ROUND when the value must be rounded to a specified number of decimal places.
  • Use TRUNC when you need to discard digits after a specified position without rounding.

Reduce decimal places with the ribbon

In Excel for Microsoft 365 on Windows, select the cells, open the Home tab, and in the Number group select Decrease Decimal. Each click reduces the number of displayed decimal places by one. Select Increase Decimal in the same group to show more places again.

  1. Select the cells, column, or range you want to format.
  2. On the Home tab, find the Number group.
  3. Select Decrease Decimal until the display has the number of places you want.

The button changes the number format, not the values. For example, if A2 contains 7.896 and you reduce its display to two decimal places, the cell shows 7.90; a formula that references A2 can still use 7.896. The displayed result is rounded for presentation, rather than simply chopped off. If you need a particular format, select the cells and press Ctrl+1 on Windows to open Format Cells. On Mac, use Command+1. Choose the Number category and set Decimal places.

Set a consistent number format

Format Cells is useful when a whole column needs the same precision or when you want to control separators and negative-number presentation. Select the range, open Format Cells, choose Number, and enter the required count under Decimal places. You can also select a suitable category such as Currency or Accounting, though those categories apply additional formatting beyond the decimal count.

For a custom format, select the cells, open Format Cells, choose Custom, and enter a pattern. For example, 0.00 displays two decimal places, including trailing zeros; 0.## allows up to two decimal places but does not require trailing zeros. These patterns affect appearance, not the stored value. A value of 4.5 appears as 4.50 with 0.00, but as 4.5 with 0.##.

Use a fixed number of places when consistent alignment or a standard such as two-place currency display is important. Use an optional-place pattern when trailing zeros add clutter and are not needed. Keep in mind that a format cannot make text into a number: if a cell contains text that looks like 4.567, number formatting will not reliably treat it as a numeric value.

Round values with ROUND

ROUND changes the result to the requested number of decimal places. With the source value in A2, enter this in B2 to calculate a two-decimal result:

With the original value in A2, the formula in B2 is =ROUND(A2,2). If A2 is 12.346, B2 returns 12.35.

Fill the formula down to apply it to other rows. The second argument specifies the number of digits to keep: 2 means two decimal places, 0 means the nearest whole number, and a negative number rounds to the left of the decimal point. For example, with A2 containing 1264, =ROUND(A2,-2) returns 1300.

Rounding can change a value upward or downward depending on the discarded digits. Excel rounds halfway cases away from zero; for ordinary decimal data, the visible result follows the familiar rule of rounding a final 5 upward in magnitude. Calculations use binary representations for many decimal fractions, so a value that looks like an exact halfway case may not always be stored as an exact decimal halfway value. For normal spreadsheet work, use ROUND when the intended rule is to round, and check the results around important thresholds.

Cut off digits with TRUNC

TRUNC removes digits beyond a specified position and does not round the remaining digits. With the original value in A2, enter this in B2 to keep two decimal places:

With A2 equal to 12.349, the formula in B2 is =TRUNC(A2,2). It returns 12.34, whereas ROUND would return 12.35.

TRUNC is not the same as rounding down toward negative infinity. It moves toward zero by removing digits. For example, with -12.349 in A2, =TRUNC(A2,2) returns -12.34. That can matter for negative numbers: truncating and rounding down do not necessarily produce the same result. TRUNC can also take zero or a negative number of digits. With A2 equal to 1264, =TRUNC(A2,-2) returns 1200 because digits to the right of the hundreds place are removed.

Choose TRUNC only when the rule is specifically to discard extra digits. It is not a substitute for standard rounding in situations where the result must be rounded to the nearest cent, nearest unit, or another defined increment.

Choose the right method for calculations

Formatting and formulas can show the same screen result while producing different downstream calculations. Suppose A2 contains 1.236 and A3 contains 1.236. If both are formatted to two places, they display as 1.24 each, but their actual sum is 2.472. If you need the sum of the individually rounded values, round each row first and then add the rounded results.

With the source values in A2:A3, enter this in B2 and fill it down; then sum B2:B3 in B4:

=ROUND(A2,2) returns 1.24 for each of the two example rows, and =SUM(B2:B3) returns 2.48. By comparison, =SUM(A2:A3) returns 2.472, which displays as 2.47 if formatted to two decimal places. These are different calculation rules, not an Excel error.

Whether to round individual line items or only a final total depends on the requirements of the calculation. Do not infer the rule from the display alone. In reports, retain unrounded source data where possible and make the intended rounding step explicit in formulas, so another person can see how the result was produced.

Replace original values with trimmed results

When formulas return the right results, you may want to store those results as fixed numbers rather than keep formulas. First put the ROUND or TRUNC formula in a separate column and check the output. Then copy the result cells and use Home > Paste > Paste Values in the destination range. This replaces formulas with their current results while leaving the original source column intact if you paste into a new location.

If you intend to replace the source cells, make a copy of the data first. A formula placed in the same cell it refers to, such as a formula in A2 that also refers to A2, creates a circular reference rather than safely changing the original value. Excel formulas calculate results in cells; they do not rewrite their own inputs. Paste Values is a deliberate conversion step, so verify that the copied values are correct before overwriting source data.

For a quick visual change, do not use Paste Values at all. Set the number format and preserve the full precision. This keeps the workbook flexible if you later need to display more places or perform calculations using the original values.

Common problems and checks

  • The number still has too many decimals: Check that you selected the intended cells and that their format is not General or a custom format with optional decimal placeholders. Format Cells lets you set an exact count.
  • The formula shows as text: Confirm that the cell is not formatted as Text and that the formula begins with an equals sign. If necessary, change the format to General, then re-enter the formula.
  • The total does not match the displayed rows: The cells may only be formatted to look rounded. Decide whether the calculation should use the original values or rounded row results.
  • Digits appear to disappear without a formula: Decrease Decimal changes display precision, but it does not delete stored digits. Increase Decimal or inspect the formula bar to see the underlying value.
  • Some cells do not change: A range may contain a mixture of numbers and text. Number formats affect numeric values, so convert valid numeric text to numbers before relying on numeric formatting or arithmetic.

Frequently Asked Questions

Does Excel Decrease Decimal permanently remove digits?
No. It changes the displayed number format. The underlying value remains available to formulas. Use ROUND or TRUNC, then paste values if you need fixed results.

How do I show two decimal places in Excel?
Select the cells and choose Home > Number > Decrease Decimal or open Format Cells with Ctrl+1 on Windows or Command+1 on Mac and set Decimal places to 2.

What is the difference between ROUND and TRUNC?
ROUND adjusts the retained digit based on the digits that follow. TRUNC discards the later digits without rounding. For 12.349 at two places, ROUND returns 12.35 and TRUNC returns 12.34.

How can I remove trailing zeros but keep up to two decimals?
Apply the custom number format 0.##. It can display a value with zero, one, or two decimal places without forcing unused zeros. It changes only the display.

Can I trim decimal places for an entire column?
Yes. Select the column or the relevant cells before using Decrease Decimal, Format Cells, ROUND, or TRUNC. For formulas, enter the formula in the first row of a separate results column and fill it down, then check the results before replacing any source values.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.