Number formats and their shortcuts
Make plain numbers look like money, percentages and dates.
Select a column, then a format shortcut such as Ctrl+Shift+4. The value stays the same; only the look changes.
This lesson is about keyboard shortcuts, so it needs a computer with a keyboard.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Product | Price | Discount | Ordered | Units sold | ||
| 2 | Stapler | 9 | 0.1 | 46122 | 28871 | ||
| 3 | USB-C Hub | 26 | 0.15 | 46113 | 78513 | ||
| 4 | Monitor Stand | 33 | 0.2 | 46110 | 74618 | ||
| 5 | Office Chair | 118.99 | 0.15 | 46122 | 97471 | ||
| 6 | Desk Organiser | 12.25 | 0.05 | 46099 | 41197 | ||
| 7 | Notebook A5 | 3 | 0.2 | 46112 | 13514 | ||
| 8 | Filing Cabinet | 125 | 0.2 | 46127 | 24286 | ||
| 9 | Whiteboard | 85.99 | 0.15 | 46098 | 66755 | ||
| 10 | |||||||
| 11 | |||||||
| 12 |
In a bit more detail
What does a number format do?
It changes how a number looks, not what it is. 0.15 formatted as a percentage shows 15%, but formulas still see 0.15. The formula bar always shows the real value.
Which shortcuts should I know?
Ctrl+Shift+4 (Ctrl+$) for currency, Ctrl+Shift+5 (Ctrl+%) for percentages, Ctrl+Shift+3 for dates and Ctrl+Shift+1 for a number with commas and two decimals. Ctrl+Shift+~ puts a cell back to General.
Why does a date show as 46096?
Excel stores a date as the number of days since 1 January 1900. Format it as a date to see the day it means.
Which currency do I get?
The one your computer is set to: £ in the UK, $ in the US, € in much of Europe. In Excel, more formats (decimals, other currencies, custom formats) are in Format Cells, Ctrl+1.
And on a Mac?
Use Control, not Cmd: Control+Shift+4 and so on. Cmd+Shift+3, 4 and 5 take screenshots on a Mac.
Know someone fighting with a spreadsheet? Send them this page.