How to Increase or Decrease Decimal Places in Excel

To change the decimal places in Excel, select your cells and click Increase Decimal or Decrease Decimal on the Home tab. Each click adds or removes one place. That changes only what you see. When the stored number itself must have fewer decimals, for example in a price list or a test answer, use the ROUND function instead. This guide shows both ways and explains when each one is right.

How to increase or decrease decimal places with the Home tab buttons#

The quickest way is the two buttons in the Number group of the Home tab. Their icons show a small ".0" with an arrow.

1. Select the cells you want to change. A whole column works too.
2. Click Increase Decimal to show one more decimal place, or Decrease Decimal to show one fewer.
3. Keep clicking until the numbers look right.

Here is what a cell holding 3.14159 shows as you click:

Notice the last column. The stored value never changes. You can see it at any time in the formula bar when you click the cell.

On Windows you can press the ribbon keys one after another instead of clicking: Alt, H, 0 for Increase Decimal and Alt, H, 9 for Decrease Decimal. On a Mac, use the buttons on the Home tab.

How to set an exact number of decimal places with Format Cells#

Format Cells lets you type the exact number of decimal places, which is faster than clicking many times.

1. Select the cells.
2. Press Ctrl+1 (Mac: ⌘1) to open Format Cells.
3. On the Number tab, choose Number, Currency, Accounting or Percentage.
4. Type the number you want in the Decimal places box and click OK.

There is also a shortcut for the most common case. Ctrl+Shift+! (Mac: ⌃⇧!) applies the Number format with two decimal places and a thousands separator, so 53122.5 shows as 53,122.50. The guide to formatting numbers as currency, percent and dates covers the other number formats.

For full control, such as a fixed two decimals with a unit after the number, write a custom format like 0.00 "kg". The article on custom number formats in Excel explains the codes.

Formatting vs rounding: which one changes the value?#

Formatting changes the look; ROUND changes the value. This difference matters every time you add up or compare numbers.

Say A2 and A3 both hold 2.4, and you show them with zero decimal places. The cells display 2 and 2. A total of =A2+A3 gives 4.8, which displays as 5. Your reader sees 2 + 2 = 5 and thinks the sheet is broken.

To make the numbers agree, round each value before you add:

=ROUND(A2,0)+ROUND(A3,0)

This gives 4, because each 2.4 becomes 2 before Excel adds them. The ROUND syntax is simple:

=ROUND(number, num_digits)

ROUND rounds 5 and above up, away from zero. If you always need to round up or down, use ROUNDUP or ROUNDDOWN. The guide to rounding numbers with ROUND, ROUNDUP and ROUNDDOWN shows all three, and the ROUND function guide is a quick syntax reference.

When to use formatting and when to use ROUND#

Use formatting when you only want a tidy report. Use ROUND when the rounded number is the real answer.

A good habit: keep raw data unrounded, and round only in the cells that hold final answers.

How to show decimals as text with TEXT#

The TEXT function turns a number into text with a fixed number of decimals. This is useful when you join a number to words:

="Average score: "&TEXT(B8,"0.00")

If B8 holds 4.136, this shows Average score: 4.14. Without TEXT, the sentence would show every decimal, such as 4.136. The result is text, so don't use it in further calculations.

Why are my decimal places wrong in Excel?#

Most decimal problems come from one of four causes. Check them in this order:

1. The column is too narrow. Adding decimals makes a number wider. If it no longer fits, Excel shows #####. Widen the column by double-clicking the right edge of the column header. See why Excel shows hash symbols in a cell for other causes.
2. The cell is formatted as General. General shows as many decimals as fit the column width, so the number of places changes when you resize. Pick Number with a fixed number of places instead.
3. Numbers you type get a decimal point added. If typing 1234 gives 12.34, the option Automatically insert a decimal point is on. Go to File > Options > Advanced (Mac: Excel > Preferences > Edit) and clear it.
4. Tiny differences like 0.30000000000000004. Excel stores numbers in binary, so some decimals can't be stored exactly. Wrap the result in ROUND. The article on floating-point rounding errors in Excel explains why this happens.

A warning about "Set precision as displayed"#

Excel has an option that makes every value equal to what it shows: File > Options > Advanced > Set precision as displayed. It sounds handy, but it permanently cuts the hidden digits from every constant in the workbook, and you can't get them back. Microsoft warns about this on its precision settings page. ROUND in the cells that need it is safer, because the original data stays intact.

Practise it#

Clicking a button is easy. Knowing whether a task wants formatting or a rounded value is what trips people up in job tests. Practise ROUND, ROUNDUP and ROUNDDOWN in a live spreadsheet: you type each formula and it checks the result instantly. Then work through the formatting shortcuts lesson and the more formatting lesson to set decimals, currency and percentages by keyboard.