Excel number format cheat sheet

Alexander Jarvis
Nov 19, 2017 · 4 min read

Tl;dr: We’re going to learn to do excel number format so you can make your sheets prettier, professional and more informative.

DOWNLOAD HERE

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. ;)

DOWNLOAD HERE

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.

DOWNLOAD HERE

Free email training excel course

You can join the free email training course and learn to be a pro for free.


Originally published at .

Alexander Jarvis

Written by

Sort out your pitch deck at PerfectPitchDeck.com. Blog at AlexanderJarvis.com. Build at 50Folds.com.

Welcome to a place where words matter. On Medium, smart voices and original ideas take center stage - with no ads in sight. Watch
Follow all the topics you care about, and we’ll deliver the best stories for you to your homepage and inbox. Explore
Get unlimited access to the best stories on Medium — and support writers while you’re at it. Just $5/month. Upgrade