Formatting numbers in Excel: how it works
A number format changes how a number looks, for example as currency, a percentage or a date, without changing the number itself. You choose it on the Home tab or with Ctrl + 1.
Try the first exercises for freeIn the lesson Formatting cells
What does a number format do?
A number format decides what a number looks like: with or without decimal places, with a currency symbol, as a percentage or as a date. The number itself doesn't change. If you type 0.2 and choose Percentage, you see 20%, but Excel still calculates with 0.2.
That difference between what you see and what's in the cell matters. A price of 2.456 with two decimal places shows 2.46, and yet Excel calculates with 2.456. If you really want to calculate with a rounded number, use ROUND. You can always see what's really in a cell in the formula bar.
Which format do you choose when?
- Amounts: Currency or Accounting, with two decimal places and a currency symbol.
- Percentages, such as growth or a discount: Percentage, so that 0.15 appears as 15%.
- Dates: a short date format, or a long one, such as Sunday 15 March 2026.
- Large numbers: Number, with a thousands separator, so that 1250000 becomes readable as 1,250,000.
Choosing a number format, step by step
C2 contains =A2*B2. A2 shows 2.46, but Excel calculates with 2.456. That's why 1000 items cost 2,456.00 and not 2,460.00. Change the numbers or the formula and see what happens.
This is how to go about it:
- Select the cells you want to format, for example the column with prices.
- On the Home tab, in the Number group, choose a format from the drop-down list, such as Currency or Percentage. With the Increase Decimal and Decrease Decimal buttons below it, you choose how many decimal places you see.
- If you want more choice, press Ctrl + 1, or Cmd + 1 on a Mac. In the Format Cells window, on the Number tab, you choose the category, the number of decimal places and the symbol.
- Look in the formula bar: the number is still there as you typed it. Only what you see in the cell has changed.
More examples
A percentage is a number
C2 contains =A2*B2. A2 shows 20%, but contains 0.2. That's why rate times amount gives the tax straight away: 40.00.
Really rounding with ROUND
C2 contains =ROUND(A2,2)*B2. ROUND really turns 2.456 into 2.46. Now 1000 items cost 2,460.00, as you'd expect from the screen.
Decimal places and thousands
B2 contains =A2/12. With the Number format, two decimal places and a thousands separator, B2 shows 104,166.67. Excel keeps all the decimal places and keeps calculating with them.
The main formats
General shows the number as you type it. Number lets you choose the decimal places, with a thousands separator. Currency and Accounting add a currency symbol. Percentage shows 0.2 as 20%. Date shows a date in the short or long format of your regional settings.
Where it often goes wrong
- The Decrease Decimal button only changes what you see. If you really want to calculate with a rounded number, use ROUND.
- A number stored as text doesn't change format: it sits on the left of the cell. Convert it to a number first.
- If you see ##### in a cell, the column is too narrow for the number. Make the column wider.
Practise this in the lesson Formatting cells. The first exercises are free.
Frequently asked questions
Why do I get a date when I type 1-2?
Excel reads 1-2 as a date and applies a date format straight away. If you really want 1-2 as text, type an apostrophe first: '1-2.
How do I show a number with a unit, such as 12 kg?
With a custom format: press Ctrl + 1, choose Custom and type 0" kg". The cell still holds the number 12, so you can calculate with it.
Read next
Creating a formulaFilling and copying formulasOrder of operationsAbsolute references ($)Calculating percentages