How to Change the Number of Decimal Places in Excel

Decimals are the tiny, judgy digits that show up when you least expect themlike when your “simple” budget turns into
$2,493.6666667 because someone divided by 3 and Excel decided to be extremely honest.
The good news: changing decimal places in Excel is easy. The even better news: you can choose whether you’re only
changing how numbers look (formatting) or changing the numbers themselves (rounding).

In this guide, you’ll learn the fastest buttons, the “Format Cells” power move, custom number formats, rounding
formulas, and a few common gotchas that make people think Excel is haunted (it’s notusually).

Formatting vs. rounding: the difference that saves spreadsheets

Before you click anything, decide what you actually want:

• Formatting changes how a number displays (how many digits you see). The underlying value stays the same.
  Example: 1/3 might display as 0.33, but Excel may still store 0.3333333333 behind the scenes.
• Rounding changes the stored value. If you round 0.3333333333 to 2 decimals, the cell becomes 0.33 for real.

If you’re making a report, dashboard, invoice, or anything meant for human eyes, formatting is often enough.
If you’re preparing data for exports, billing, taxes, or strict calculations, you may need rounding (or truncation).

Method 1: Use the Increase Decimal / Decrease Decimal buttons (fastest)

This is the quickest way to change decimal places visually. Great for cleanup, presentations, and “please stop showing me 12 decimals”
emergencies.

Steps (Excel for Windows and Mac)

1. Select the cell(s) you want to change.
2. Go to the Home tab.
3. In the Number group, click Increase Decimal or Decrease Decimal.
4. Repeat until it looks right.

Steps (Excel for the web)

1. Select your cells.
2. Open the Home tab and find the number-formatting controls.
3. Use the decimal increase/decrease controls to show more or fewer digits.

Tip: If your cell shows fewer decimals but the formula bar still shows more, that’s normalformatting changes the display,
not the stored value.

Method 2: Format Cells for an exact decimal setting (clean and consistent)

If you want every selected number to show exactly 2 decimals (or 3, or 0), “Format Cells” is your best friend.
It’s especially helpful when you want consistency across columns and reports.

Open the Format Cells dialog

• Keyboard: Press Ctrl + 1 (Windows) or Cmd + 1 (Mac in many setups).
• Mouse: Right-click the selection and choose Format Cells.

Set decimal places (Number, Currency, Accounting, Percentage)

1. In Format Cells, click the Number tab.
2. Choose a category:
   • Number for standard values
   • Currency for money with a symbol near the number
   • Accounting for lined-up symbols and decimals in a column
   • Percentage for percent display
3. Set Decimal places to your preferred number (0, 2, 4, etc.).
4. Optional: choose negative-number formatting and thousands separators.
5. Click OK.

When to use this: invoices (2 decimals), measurements (1–3 decimals), KPIs (maybe 0 decimals),
and any time you want a whole column to behave the same way without manual button clicking.

Method 3: Custom number formats (for when “2 decimals” isn’t the whole story)

Custom formats let you control decimals with surgical precision: fixed decimals, optional decimals, commas,
leading zeros, and even “pretty” display rules without changing values.

Common custom formats (copy/paste friendly)

• 0 → show no decimals (13.9 becomes 14 if rounded, but as formatting it displays as 14)
• 0.0 → one decimal (13.94 displays as 13.9)
• 0.00 → two decimals (13.9 displays as 13.90)
• #,##0.00 → thousands separator + two decimals (1234.5 displays as 1,234.50)
• 0.## → up to two decimals (13 becomes 13, 13.5 becomes 13.5, 13.57 becomes 13.57)
• 0.000% → percent with three decimals (0.12345 displays as 12.345%)

How to apply a custom format

1. Select the cells.
2. Open Format Cells (Ctrl+1).
3. Go to Number tab → choose Custom.
4. Type or paste your format code into the Type box.
5. Click OK.

A practical example: optional decimals for clean reports

Let’s say you’re showing product weights. Whole numbers should display cleanly (2 lb), but fractional weights
should show precision (2.5 lb). Try:

• 0.## (shows up to two decimals only when needed)

The result feels human-friendly: less visual noise, more readability, fewer “why does this say 2.00?” questions.

Method 4: Change the actual value (ROUND, ROUNDUP, ROUNDDOWN, TRUNC)

If you need the value itself to be limited to a set number of decimalslike for billing, exports, or matching a system
that only accepts two decimal placesuse a formula.

ROUND: standard rounding

Use ROUND(number, num_digits).
Example: if A2 is 23.7825, then =ROUND(A2,2) returns 23.78.

ROUNDUP and ROUNDDOWN: always up or always down

• =ROUNDUP(A2,2) → rounds away from zero to 2 decimals
• =ROUNDDOWN(A2,2) → rounds toward zero to 2 decimals

These are useful when you have business rules like “always round up for shipping weight” or “always round down
for discounts.” (Your accountant may have Opinionsuse responsibly.)

TRUNC: remove decimals without rounding

Use TRUNC(number, [num_digits]). This cuts off extra decimals like a paper cutterclean, fast, and emotionless.
Example: =TRUNC(12.119999,2) returns 12.11 (not 12.12).

How to make rounding “stick” (convert formulas to values)

Rounding formulas create results that update when source data changes. If you want permanent rounded numbers:

1. Use rounding in a helper column (e.g., in B2: =ROUND(A2,2)).
2. Copy the results.
3. Paste Special → Values back over the original (or into a final output column).

Method 5: Automatically insert decimals while typing (Fixed Decimal)

If Excel is placing decimals weirdly when you typeor you want it tothere’s a setting called
Automatically insert a decimal point. It’s handy for repetitive data entry (like cents)
and also a common cause of “Why did 2450 turn into 24.50?!” confusion.

Turn on or off Fixed Decimal

1. Go to File → Options.
2. Select Advanced.
3. Under Editing options, find Automatically insert a decimal point.
4. Set Places (for example, 2 places means typing 2450 becomes 24.50).
5. Click OK.

Tip: You can override Fixed Decimal for a specific entry by typing the decimal point manually.
So if Excel insists on 24.50 but you need 2450, type 2450. or include the decimal explicitly.

The tempting trap: “Set precision as displayed”

Excel has an option called Set precision as displayed. It does exactly what it sounds like:
it forces Excel to store numbers at the displayed precision.

Here’s why people warn you about it: it can permanently change values and can cause cumulative calculation errors,
especially in large models. In other words, it’s the spreadsheet equivalent of sanding a table while you’re still
measuring it.

Safer alternatives: use rounding formulas in helper columns, or round only in final outputs
(reports, exports, invoices) instead of changing your source data.

Troubleshooting: why your decimals won’t behave

Problem 1: “I decreased decimals but nothing changed”

• Check if the cell is text. If it’s left-aligned by default, or you see a green triangle warning, it may be stored as text.
• Fix: Convert to number (Data → Text to Columns, or multiply by 1 in a helper cell, or use VALUE()).

Problem 2: “The cell shows 2 decimals, but calculations still act like there are more”

That’s formatting. The stored value still has full precision. If you need math to use 2 decimals, round the value with
ROUND (or TRUNC) in the formula itself, or round the inputs in a helper column.

Problem 3: “My totals are off by a cent (or five)”

This usually happens when you format values to 2 decimals but sum full-precision values, or when importing data with
long decimal expansions. Decide a rounding rule:

• Round each line item to 2 decimals, then sum.
• Or sum full precision, then round the final total.

Those can produce different resultschoose the method that matches your business rule and document it.

Problem 4: “I see ###### instead of decimals”

That’s not a decimal issue; it’s a column width issue. Widen the column (double-click the right edge of the column header)
or reduce the displayed decimals.

Problem 5: “My number looks rounded weirdly”

Some decimal fractions can’t be represented perfectly in binary floating-point, so Excel may store a value that’s
microscopically different than what you expect. Usually you’ll only notice it when comparing or rounding.
The fix is boring but effective: round the output you care about.

Best practices: decimals that stay consistent (and keep you sane)

• Keep raw data raw. Store full precision in your source data; format outputs for readability.
• Use helper columns for rounding. Don’t destroy the originalcreate a “Rounded for Reporting” column.
• Be consistent by column. If a column is currency, make it 2 decimals everywhere.
• Pick a rounding policy. Especially in finance: round-per-line vs round-at-total matters.
• Use custom formats for presentation. Optional decimals (0.##) reduce clutter without lying about the value.
• Watch Fixed Decimal settings. If typing “2450” becomes “24.50,” you’ve found the culprit.

Conclusion

Changing decimal places in Excel can be as simple as clicking Increase/Decrease Decimalor as precise as writing custom
formats and rounding formulas. The key is knowing whether you want to change the appearance (formatting)
or the actual value (rounding/truncation). Once you choose the right tool, your spreadsheet stops shouting
unnecessary digits at you and starts looking like it belongs in a real report.

Real-world experiences: what actually happens when people “just need two decimals”

In the real world, “Change the number of decimal places in Excel” rarely shows up as a calm, peaceful request.
It usually arrives like this: “Why does this total not match the invoice?” or “Why does our dashboard show 12 decimals?”
or the classic, “Excel is rounding wrong” (spoiler: it’s almost always doing exactly what you told it to do, just not what
you meant).

One of the most common scenarios is finance reporting. Someone exports transactions from a system that stores values at
four or more decimal places (think tax rates, exchange rates, or interest calculations). In Excel, the column is formatted
to show two decimals because it’s money. Everyone looks at the sheet and thinks, “Greattwo decimals.” Then the sum at the
bottom doesn’t match the accounting system’s total by a few cents. Cue panic.
What’s happening is simple: Excel is summing the full-precision values, while the human eye is summing the displayed values.
The fix isn’t a magical buttonit’s choosing a policy. Do you round each transaction to two decimals and then add them?
Or do you add everything in full precision and round the final total? Both methods are defensible; they can just produce
different answers. The “right” method is whichever matches your organization’s rules and the system you’re reconciling to.

Another frequent experience happens in operations and inventory. Quantities might be stored with three decimals because you
track partial units (like 1.375 pounds, 2.125 meters, or 0.750 gallons). But the people using the spreadsheet don’t want
three decimals for everythingespecially when half the values are whole numbers. This is where custom formats shine.
Using a format like 0.## can make the sheet immediately more readable: whole numbers stay whole, and decimals
appear only when they matter. You get clarity without changing the underlying numbers, which is crucial if those values feed
downstream calculations.

Then there’s the “Fixed Decimal” moment. A team is doing rapid data entrymaybe entering prices as cents or inputting
measurements where the decimal is always the same place. Fixed Decimal can be a productivity booster: you type 1234 and
Excel turns it into 12.34 automatically. The problem is that someone turns it on, forgets it exists, and now every new
workbook feels cursed. When 2450 becomes 24.50, people don’t think “setting”; they think “Excel bug.”
The lesson: if numbers suddenly shift while typing (not just displaying), check Advanced options before rewriting your
entire dataset in frustration.

Finally, there’s the high-stakes version: exporting data to another system that rejects “too many decimals.”
In these cases, formatting is not enough because the receiving system reads the stored value, not the pretty display.
The reliable workflow is to create a final export column using ROUND (or TRUNC, depending on your rules), then paste values.
That way, what you send is exactly what you intendno hidden precision, no surprise rejections, no last-minute “why won’t it upload?”
chaos five minutes before a deadline.

The big takeaway from all these situations is that decimals aren’t just cosmeticsthey’re policy. Excel gives you multiple
tools because different jobs require different outcomes. Once you decide whether you’re changing appearance or reality,
the rest becomes a straightforward (and surprisingly satisfying) cleanup job.