Excel Custom Number Format Guide

I’ve put together this Excel custom number format guide as a resource for our members. There are loads of ways to apply custom number formats and as a result I find myself answering questions that are covered in this post on a daily basis.

To be clear, number formatting in Excel is used to specify how a value should appear in a cell or chart, but it doesn’t alter the underlying value that you can see in the formula bar. Unless of course you format a number as text, in which case it can no longer be treated as a number in math formulas.

Download the workbook

Enter your email address below to download the sample workbook.

By submitting your email address you agree that we can email you our Excel newsletter.

Applying Custom Number Formats

To apply a custom number format first select the cell or cells you want to format, then CTRL+1 to open the Format Cells Dialog box, or go to the Home tab and click on the arrow in the Number group:

This opens the Format Cells dialog box in the Number tab (below), where you can choose ‘Custom’ from the Category list and insert your format in the ‘Type’ field:

Number Format Structure

Let’s start with the anatomy of a number format.

A number format is made up of 4 components, with each component separated by a semi-colon:

Component1;Component2;Component3;Component4

Component1 formats the POSITIVE value

Component2 formats the NEGATIVE value

Component3 formats ZERO values

Component4 formats TEXT

In other words; POSITIVE ; NEGATIVE ; ZERO ; TEXT

Note: spaces around semi-colons above are added for clarity but are not required in the format.

You may be familiar with number formats you find in the Format Cells dialog box, like this:

In the above example only the positive and negative formats are specified, in which case the zero format will be the same as the positive format and text will be formatted as it was entered.

In fact when we omit components Excel behaves as follows:

When just one component is provided with no semi-colons in the code, Excel will check to see if the format is one of the default ‘Number’ formats and revert to that format. Otherwise, if the format is unique then all numbers will be formatted according to the format provided.

When the first two components are provided Excel will use the same format for zero, and text will appear as entered.

When the first three components are provided Excel will use the formats specified, and text will appear as entered.

If any components are skipped or left blank e.g. in this format the negative component has been skipped, #,##0;;0 and so the negative value will not display as demonstrated below:

Hiding All Values

When all components are omitted and just the 3 semi-colons specified, Excel will not display anything in the cell. It’s a clever way to hide data in cells and charts.

Hiding Zero Values

Another common question is ‘how do I hide zero values from displaying in my chart labels’. The answer is simply to use the technique in point 4 above and omit the format for the zero component. See the example below:

Custom Number Format Characters

You may have noticed custom number formats that contain unusual characters like this:

These characters are used as codes to tell Excel how to format the value. Let’s look at them in turn.

Literal Characters – these appear as entered in the custom number format:

Special Characters – define how other characters are treated

Digit Placeholders – used to control how numbers that contain decimal places are displayed:

Thousand Separators and Scaling

The comma is used to display the thousand separators in a number when it is enclosed in # signs or by zeros, and when it follows a digit placeholder it scales a number by 1000.

Tip 1: We can scale a value to millions by using two commas, as you can see in the example in the last column above. And for billions we add 3 commas.

Tip 2: When appending ‘K’ for thousands we simply type it in the format, but ‘M’ for millions must be entered with a preceding \ as the example above, or inside double quotes. This is because ‘M’ is already reserved as a character for the date formats. More on date formats soon.

Currency Formats and Parentheses for Negative Values

Tip 1: In the example in the last column above I’ve used the underscore and right parenthesis to align the decimal places. You can see them in red in the number format here: $#,##0.0,K_);($#,##0.0,K)

Remember the underscore is a special character that adds a space according to the width of the following character, in this example the right parenthesis.

Tip 2: if you don’t have a particular currency key on your keyboard, you can enter it into the Custom Number Format dialog box using the ANSI code. To enter an ANSI code, hold the ALT key and type in the 4 digit number. Here are some example ANSI codes that might be useful:

Alternatively, you can use the Symbol tool (Home or Insert tab) to find the character you want and then insert it into a cell. From the cell you can copy it to your clipboard and paste it into the Custom Number Format dialog box.

Tip: You can also insert symbols like up or down triangles, just make sure they’re the Arial Geometric Shapes (subset) variety and not Wingdings:

For example, this format displays positive percentages with an upward facing triangle and negative values with a downward facing triangle.

▼0.0%;▲0.0%

Symbols can be quite effective when used in reports or charts like the one below:

Colors

We can specify a color for a format, for example you might like to format negative values in red. Color formatting is done in one of two ways:

We can use the color name surrounded by square brackets, like so:

$#,##0.0,K_);[red]($#,##0.0,K)

Which results in negative values like this: ($1.3K)

However, with color names we’re limited to these:

[black]

[green]

[white]

[blue]

[magenta]

[yellow]

[cyan]

[red]

Alternatively we can specify a color index number e.g.:

$#,##0.0,K_);[color 3]($#,##0.0,K)

Using the color number opens up a total of 56 different colors, although 5 of them are duplicates, as you can see by the hex codes in red below:

Tip: Apply color formats to chart axes or labels. Note: if you have labels then you don’t also need the vertical axis or gridlines, unlike my example below:

Date Characters

We can combine the month, day and year characters together in various configurations to get the desired format. Here are a few examples:

Time Formats

Just like the date characters, we can combine the time characters to get the format we’re after. For example:

And when we’re adding time that exceeds 24 hours, or 60 minutes, or 60 seconds we can display the elapsed time like so:

Fractions

Let’s go back to school and recap some of the different types of fractions:

Proper – where the absolute value of the fraction is less than 1 e.g. 1/2

Improper – where the absolute value of the fraction is greater than or equal to 1 e.g. 4/3

Mixed Fractions – are another way to write improper fractions where the whole is written separate to the fraction, for example 4/3 would be written 1 1/3

Use the ? symbol to specify the varying number of digits to display.

It’s important to remember that the underlying value in the cell, as displayed in the ‘Value’ column above, is what is used in your formulas that reference those cells. The Formatted result is simply how it appears, but you must be careful if you are using those formatted results in calculations that don’t reference the cell directly. For example if you were to enter those formatted values into a calculator you’d get a different result to a formula that referenced that cell.

Percentages

Use the % sign to display numbers as a percentage of 100. Remember, a zero in the custom number format displays both significant and insignificant digits, and the # will only display significant digits as per the examples below:

Scientific Notation

Scientific Notation allows us to express very large numbers in a decimal form of shorthand. It’s commonly used by scientists and mathematicians. Let’s use 7,830,000,000 as our example value. I think you’ll agree it’s a pretty big number.

In scientific notation we can express it as 7.83 x 109

We raise 10 to the power of 9 because 9 decimal places have been removed from the original number to be left with 7.83. And when we multiply 7.83 by 109 we get our original value of 7,830,000,000. Ok, math lesson over.

In Excel (and some calculators) we can’t use ‘x’ in a number, nor can we insert superscript. Instead we use the upper (or lower case) E+ notation like so:

7.83E+09 or 7.83e+09

To apply this formatting the custom number format is 0.00E+00 or 0.##E+00.

Remember we can use 0 to display both significant and insignificant digits, or if we only want to display significant digits we use #.

Here are some examples:

Did you notice the precision in the ‘Formatted Result’ of the 3rd example above? See how it appears to round the value to one decimal place and therefore omits the ‘3’ in 7.83. Be wary of this if you’re likely to refer to the face value of these cells, as opposed to the actual underlying value.

Remember to use the 0 digit place holder to show both significant and insignificant digits, and use the # to only display significant digits.

Engineering Notation

Engineering notation is a version of scientific notation in which the exponent of ten must be divisible by three. The format contains 3 # before the decimal place and 3 zeros after the decimal place to force the result to return an exponent that is divisible by 3.

Text/Labels

We can append or prefix our values with text while still maintaining the underlying number for use in formulas. This can be handy when formatting values like kilometres or days/hours/minutes, Over/Under etc.

To display both text and numbers in a cell simply insert your text in double quotes, or if it’s only a single character then precede it with a backslash (\).

The last example uses the @ symbol for Text. In other words, the cell is formatted as text, which is also why it is left aligned, but the custom cell format displays the # tag at the front of the text without the need to actually type it in the cell.

Color Code based on Values/Conditions

Remember that by default the custom number format is broken into the following components:

Positive ; Negative ; Zero ; Text

However, we can override the default components with up to two conditions and add color coding based on criteria we set.

For example, let’s say we have a threshold of 500 units per day. Anything below this should be red and anything 500 or above should be formatted green. We can use the format:

[red] [<500] #,##0;[color 10] [>=500] #,##0

Which yields the following results:

Tip: once a condition is met Excel doesn’t test the other conditions so it’s important you get the order of tests correct so it doesn’t stop at the first criteria. Just like a nested IF formula.

Of course we could also write this format like so:

[color 10] [>=500] #,##0; [red] [<500] #,##0

What if we want 3 conditions? Well, we can’t insert 3 conditions as such, but we can insert 2 and then apply a font color to the cells, which will be picked up for all remaining values. This works because custom cell formats override the font color.

In the example below values less than 500 are red, values less than 1000 are orange/amber and values 1000 and over, for which there is no criteria, pick up the cell font color, which is green:

Repeating Leading and Trailing Characters

We can fill a cell containing text values with a specific character using the asterisk symbol. For example:

Padding with Zeros

Remember that zero is a digit placeholder which displays both significant and insignificant digits, so we can exploit this to force Excel to display an insignificant zero at the front of a value, or to apply spacing between digits.

It’s handy for phone number formats, bar codes, product codes etc. Here are a few examples:

Notes on Custom Number Formats

Custom Number formats are saved in the workbook in which they’re created and are not available in any other workbooks.

Custom Number formats do not change the underlying value. This is important if you want to reference those cells in formulas.

Formats can appear to round values which will not be reflected in the results of formulas that reference those cells

Regional Settings – this post assumes your region uses the full stop/period character for a decimal place and a comma for the thousand separators. If your region differs, please amend accordingly.

Sources

I’d like to recognise the following resources which I learnt from in the writing of this guide:

I have subscribed to a number of your courses and continue to be impressed by your flaws delivery. I also teach Excel in Lagos, Nigeria and find myself resorting to “err”s and “emm”s whilst trying to find the best way to put a point across.

FLIGHT DURATION ACROSS TIME ZONES

I recently found myself in a situation where I needed to prepare a spreadsheet of my journey which involved crossing time zones.

The usual “arrival time – departure time” gives erroneous results unless modified in the following way:-

At the heart of the solution is the fact that Excel views time in fractions of 24hrs so you would need to convert the difference in hours between the two time zones into a fraction of 24 then add or subtract this from the departure time in the formula above e.g.

This is a great article, Mynda! It got me wondering about what would happen if you changed the color palette (because almost all my old templates use a custom color palette). It turns out that the color codes for Color1-56 CAN be changed. For example, [Color10] doesn’t always have to be green. You can go to File > Options > Save > Colors and modify the color palette.

As another side note, the Color1-56 codes in Google Sheets are fixed colors (but match the default Excel color palette), so it doesn’t look like customizing a color will translate into Google Sheets if you upload/convert your customized Excel file.

It is really amazing efforts. Do you mind if I translate your post to Arabic along with the example file with keeping all the credit to you and your website?
viaexcel.com (website field is not working in comment area).

Hi Mynda,
I released several year ago a similar workbook with a presentation to explain and illustrate the custom formating however I never imagine to use symbols to illustrate the grow of a metric like you done for charts. This tip is very interesting and this is what i love from MVP simple tips that make a big difference. Thanks a lot Mynda for sharing this.
Best regards
Mehdi

Well, you moved the bar for “pillar” posts. Let’s just call this a “foundational” post! I learned quite a few new tricks, some of which are eye-popping. This will be my go-to guide for formatting numbers!

This guide is a great tool, the kind I like to save for future use. Is there a better way to save it for offline use than a .PDF? The challenge with printing it to .PDF is the banner ads are still there and they block the guide.

In Mozilla, you can view the page in “Reader View” (this option is towards the extreme right of the URL bar. In Reader View you get to see only the article minus all the ads and sidebar items. You can then either print a .pdf file using any of the free pdf conversion utilities like CutePDF, PDF-Viewer, etc., and save the pdf file to your system for offline use.

Well done for keeping this to such a short article; it really is a HUGE topic!
a few additional points to confabulate your readers further (and there’s more!)

Using a number formatted as text as a number – can be done if you add a double minus before it to force excel to evaluate it
Full year can also be formatted by using “e” instead of “yyyy”
Line feeds can be added to a format using ANSI code 10 (ALT+0010) – but dates will still show as ###### if the column isn’t wide enough (use the TEXT function if this really bugs you)
If you use a condition then the next rule is still used for negative numbers and the next for others (eg [<-1] -0;0.0;-0.0 will apply the -0.0 format to all zeroes and positives)
Colouring text using conditional formatting trumps number formatting (the two condition limit in normal formatting can be annoying)

Good article. Better than any I have seen done before. One point to be clear on, you are absolutely correct that the format does not affect the underlying value. However, I don’t think you can say “…it doesn’t alter the underlying value that you can see in the formula bar”. If you input a number like 43000 and format it as a date then it does affect what you see in the formula bar. Even if you format it as a percentage it changes what you see in the formula bar slightly.

Thank you Mynda for the insight into custom number formatting. I have a question. Is there a way to build your own custom number formatting? I need to have a field in my spreadsheets that is a letdown ratio ie: 25 to 1. I currently need to format the column as text and type 25:1. I’d like to be able to have the column as a custom number format rather than as text.

Resources

Affiliate Program

Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.

✕

Hang On, Don't Go Just Yet.

As a thank you for visiting how would you like a10% Discount Code to use with any of my courses?