The same number, 5, can be shown as plain 5, as 5.00, as GBP 5.00, or even mistaken for a date, depending entirely on how the cell is formatted. By the end of this page you will understand number formats well enough to make figures show exactly the way you intend.
Excel always keeps track of a cell's real, underlying value separately from how that value is displayed on screen. This is a useful trick, because it means you can make figures look neat and consistent, such as always showing two decimal places or a currency symbol, without ever changing the actual number being used in any calculations.
- The stored value never changes
- Only the display, currency here, changes
To change a number's format, select the cell or cells, then look on the Home tab in the Number group, where you will find a dropdown that may already say General, plus quick buttons for currency, percentage and comma-separated thousands, and buttons to add or remove decimal places. For more control, right-click the selection and choose Format Cells, which opens a dialogue with categories including Number, Currency, and Date, each with further options such as how many decimal places to show or which currency symbol to use.
Try it yourself
Type 5 into a cell and press Enter.
This gives you a plain number to reformat without changing its actual value.
Select that cell, right-click, choose Format Cells, pick Number, and set two decimal places.
This changes the display to 5.00, which looks more deliberate on a financial sheet, without altering the underlying value of 5.
Select the same cell again, open Format Cells, and choose Currency with your preferred symbol.
This adds a currency symbol and typically two decimal places automatically, useful for showing amounts of money clearly.
In a new cell, type 1 4 with a forward slash between them, such as 1/4, and press Enter.
This shows how Excel guesses you mean a date, 1 April, rather than the fraction one quarter, because of the format it applies automatically.
Select that date cell, open Format Cells, choose Date, and try a different date style from the list.
This shows that the same underlying date can be displayed many different ways, from 01/04/2024 to 1 April 2024.
Words people use
Understanding that a cell's appearance and its true value are two separate things will save you a lot of confusion, particularly when a date appears where you expected a plain number, or the other way around.
Check yourself
If you can say yes to each of these, you are ready for the next lesson.
- I understand that formatting changes appearance, not the underlying value.
- I can set decimal places and apply a currency format.
- I can change how a date is displayed using Format Cells.
- I know why typing something like 3/4 can turn into a date unexpectedly.