Tl;dr: We’re going to learn to do excel number format so you can make your sheets prettier, professional and more informative.
Did you know that you can do all sorts of magic in Excel to format your cells and make magic happen without formulas?
Maybe you know some basics, but I’m sure you don’t know all the awesome tricks that are possible.
Here is a free excel template which will explain all the key tips and tricks you need to know to create crazy flexibility in your Excel models. If you study this sheet you will be a total boss and be able to do at least 80% of all formatting you will need. Yes, there are of course a never-ending number of other things you ‘could’ do, but these are the ones you’ll most likely be to use.
This is what the excel number format cheat sheet looks like
The format
We group out the format learnings into groups, set what the format code looks like, what the input is, what the format shows and then a note to explain each format.
How to edit formats
Cool, so you’re aware that you can change each format. How do you actually do an Excel custom format?
- Mac: Press CMD+1
- PC: Press CTRL+1
This pulls up the edit box.
You then want to click on the last box called ‘custom.’
Then you can see a format you like. You can click on a format you made already and apply it by pressing ok.
Or you just need to select the text, delete it and then add the new custom format by copying and pasting the code from the format sheet and pressing enter
You probably have a lot of cells you want to apply the format to? The fastest way is to copy then paste the formats.
On a PC you would press CTRL+C on the format you want, then select the cells you want and press CTRL+E+S+T.
On a Mac, you would go buy a PC. 😉
The Excel custom format syntax
There are four parts to a format (You can make this simpler, of course, using just one). It consists of:
POSITIVE; NEGATIVE; ZERO; TEXT
- Positive= What the format looks like when the input is positive
- Negative= What the format looks like when the input is negative (such as using brackets (99))
- Zero= What the format looks like when the input is exactly zero
- Text= What the format looks like when the input is text
Each segment is parsed with a “;” or semi-colon. So if you only want to format the text you need to have three ;;; to format that text part. Understand?
Some tips:
- If you want the default Excel number format, type “General”
- If you want to format positive, negative and zeros all the same then you just input one section (no “;”)
- If you only put in two format types, the first section is used for positive numbers and zeros, and the second section – for negative numbers.
- You can only have a format for text when a custom format is applied to all four sections “; ; ;”
- “0”= If you want two decimal places you write like this: #.00 – So 7.7 will display as 7.70. It forces extra digits
- “#”= If you want optional digits instead of forcing extra digits, use #. Extra digits won’t be shown. “#.##” displays UP TO 2 decimal places. So 7.7 will show 7.7 not 7.70. 7.777777 will show as 7.77
There you go! Have a play with the sheet and you’ll be a boss at Excel custom formats in no time.
Free email training excel course
You can join the free email training course and learn to be a pro for free.