Excel Custom Number Format Conditions

Custom number format conditions allow you to specify a different format based on the size of a number. In this tutorial we’ll look at the different ways we can use them, including some fun with emojis!

In the examples below I’ve used emojis with custom number formats to visualise temperatures. And instead of boring old grades like A, B, C etc. for student test scores, I’ve used hand signs. And for a more serious application, I’ve simply used scaling for millions and thousands.

Note: only Excel Online displays colour emojis. The Excel desktop app displays them in greyscale. Emojis are available in Excel 2010 onward.

How to Apply Custom Number Format Conditions and Emojis Video

Download Workbook

Custom Number Format Conditions with Emojis

In the image below the emoji column (D) actually contains the test score numbers, as you can see in the formula bar. They’ve had a custom number format applied, which you can see in the Format column (E), that displays an emoji instead of the underlying value.

It formats values greater than 80 with the clapping hands and values greater than 50 with the ok sign, with the remaining values given the thumbs down.

We can have lots of fun with emojis, here’s another example using temperatures:

The temperatures idea came from a Twitter post by @benlcollins where he used them in <cough> Google Sheets </cough>, and it reminded me that we have emojis in Excel too.

The limitation is that only Excel Online shows them in their coloured glory. In the Excel desktop app they are greyscale, as you can see below:

Inserting Emojis in Excel

You can use the keyboard shortcut:

To bring up a list of emojis available in Excel:

Or you can get a full list of emojis here. To use these emojis in Excel you need to take the code after U+ in the Code column on that page, as shown below. For example, the face with sunglasses code is 1F60E:

And then use the function HEX2DEC to convert the hexadecimal code into decimal, and the UNICHAR function to convert the decimal code into an emoji like so:

Note: just like emojis, UNICHAR is only available in Excel 2010 onward.

Thanks to fellow MVP, Frederic Le Guen for his post on using Emojis in Excel.

Custom Number Format Conditions for Scaling

Ok, enough frivolities, we can also use custom number format conditions for serious work, like formatting values with different scaling. For example, here I have values ranging from millions to hundreds, with each scaled accordingly:

A single custom number format is used on the Sales values above, which is shown in the Format column of the image. Notice the conditions start with the largest number and reduce from there.

Now, I don’t recommend using them in scenarios where the scaling is mixed, like the PivotTable above, because this could slow down interpretation and make the values more difficult to compare.

However, in the chart example below it makes more sense because the different categories warrant different scaling and they only contain one format per category:

Custom Number Format Conditions Font Colours

We can also apply different colours based on a scale. This is typically used to colour code negative values in red, but we can also specify a scale. In the example below I’ve colour coded values less than 500 in red, then values less than or equal to 1000 in green, with the remaining values in orange:

Tip: take note of my criteria order; the first value is the smallest. If you wrote the custom number format with <=1000 first, see below, then the first two values (200 and 790) would be green.

[Color10][<=1000]  #,##0; [Red][<500]  #,##0 ; [Color46]  #,##0

Note: if your system settings are UK English, replace 'color' with 'colour'.

You can get a complete list of colour codes here.

Custom Number Format Condition Limitations

You can only have 2 conditions in a custom number format with each condition separated by a semicolon. i.e. the first condition; the second condition; and then everything else.

If you require more conditions you can use Conditional Formatting to override the custom number formatting.

Remember, the Excel desktop app only shows emojis in greyscale and they are only available in Excel 2010 onward.

More Custom Number Formatting Ideas

Custom Number Format Guide – comprehensive guide to custom number formats available in Excel.

Charting variances with emojis!

Complete list of emoji codes – as shown in the video.

Complete list of colour numbers.