Six Things Excel Custom Number Formatting can Do for You

Excel Custom Number formatting is the clothing for data in excel cells. You can dress these the way you want. All you need is a bit of know-how of how Excel Custom Number Format works.

Excel Custom Number Format Construct

Before I show you the awesomeness of it, let me briefly explain its construct.

All the formats in excel are divided into four parts:

<POSITIVE>;<NEGATIVE>;<ZERO>;<TEXT>
  • <POSITIVE> – Here goes the format for positive numbers.
  • <NEGATIVE> – Here goes the format for negative numbers.
  • <ZERO> – Here goes the format for 0.
  • <TEXT> – Here goes the format for Text.

Note that all these are separated by semi-colons (;).

Anything that you enter in a cell in excel would fall in either of these four categories and hence a custom format for it can be specified.

How this construct works

You can specify a format for any or all of these four parts.

For example, if you write General;General;General;General then everything would be displayed in the General format.

But if you write 0.00;-0.00;0.00;General, positive numbers are displayed with 2 decimals, negative with a negative sign and 2 decimals, zero as 0.00 and text as normal text string.

Similarly, you can specify the format for any of the four parts.

If you mention only:

  • One format: It is applied to all the four sections.
    • For example, if you just write General, it will be applied for all the four sections.
  • Two formats: First one is applied to positive numbers and zeros, and the second one is applied to negative numbers. Text format by default becomes General.
  • Three Formats: First one is applied to positive numbers, the second one is applied to negative numbers, third is applied to zero, and text disappears as nothing is specified for text.

If you want to learn more about custom number formatting, I would highly recommend Office Help section.

In this blog, I will show you 6 amazing things you can achieve using Excel Custom Number Format.

#1 – Display Negative Numbers in Red Color  

Excel Custom Number Format - Negative in Red

  1. Select the data set where you want to highlight the negatives numbers in red
  2. Open Format Cells dialogue box (Key Board Shortcut – Control + 1) 
  3. Select Custom number format category
  4. In the Type field write
General;[Red]-General

Excel Custom Number Format - Negative in Red Dialogue Box

If you want the negative numbers in red color, without the negative sign (-) use the following:

General;[Red]General
#2 – Add text to Numbers (such as Millions/Billions)

Excel Custom Number Format - Numbers as Text

  1. Select the data set.
  2. Open Format Cells dialogue box (Key Board Shortcut – Control + 1).
  3. Select Custom number format category.
  4. In the Type field write:
$General" Million"

Note that this custom number format will only affect numbers. If you enter text in the same cell, it would appear as it is..

#3 – Disguise Numbers and Text

Excel Custom Number Format - Number as Text

  1. Select the data set
  2. Open Format Cells dialogue box (Key Board Shortcut – Control + 1)
  3. Select Custom number format category
  4. In the Type field write

To display 0 as False and 1 as True

[=0]"False";[=1]"True"


To display marks less than 35 as Fail and more than 35 as Pass

[<35]"Fail";[>=35]"Pass"

Again, these look like text, but these are in fact numbers, and can be used in calculations or formulas.

#4 – Hide text but display Numbers

Excel Custom Number Format Hide Text

This could be useful when you import data and there are NA or Not Available in some cells, instead of numbers. You can use this trick to quickly hide all the text while the numbers get displayed.

  1. Select the data set
  2. Open Format Cells dialogue box (Key Board Shortcut – Control + 1)
  3. Select Custom number format category
  4. In the Type field write
General; -General; General;

Note that I have used General format. You can any format you wish (such as 0, 0.#, #0.0%). Also, note that there is a negative sign (-) before the second General format, as it represents the format for negative numbers.

This works as we have specified the format for the positive numbers, negative numbers, and zero only (and not for Text). So the text is not displayed.

If you wish to hide all type of contents (numbers and text) from a cell, use the following excel custom number format:

;;;;
 #5 – Display Numbers as Percentages (%)

Excel Custom Number Format number as percentage

  1. Select the data set
  2. Open Format Cells dialogue box (Key Board Shortcut – Control + 1)
  3. Select Custom number format category
  4. In the Type field write
0%

This will change the numbers to their percentages. For example, it changes 0.11 to 11%.

TIP: In case you want to keep 0.11 as 0.11%, use 0\% instead.

#6 – Display Numbers in a Different Unit (Millions as Billions or Grams as Kilograms) 

Excel Custom Number Format Millions as Billions

  1. Select the data set
  2. Open Format Cells dialogue box (Key Board Shortcut – Control + 1)
  3. Select Custom number format category
  4. In the Type field write
0.0,,

If you want to display 2 decimal points, use 0.00,, instead.

These Excel custom number format tricks can be used in your daily work. If you use anything else, do share it with,e by leaving a comment.

Related Tutorials:
  • Manu says:

    Hi, thank for the tips, but once we’ve created the format we want, is there a way to create a shortcut to the new custom number format?

  • >