Hide Zeros & Other Custom Number Formatting Tricks

October 20, 2022 - by Bill Jelen

Problem: I don’t want zeros to appear in my document.

Strategy: Excel’s custom number formatting codes have an amazing array of options that not many people know about. You can specify multiple formats within one custom number code. Each format is separated by semi-colons. Read on for details.

To assign a custom number format, you select the range and press Ctrl+One. On the Format dialog, you select the Number tab and then select Custom from the Category list. Finally, you type any valid custom number format in the Type box.

You’ve probably run into some custom number formats, such as these:

#,##0 will display numbers with thousands separators.
$#,##0.00 will display two decimal places and a currency symbol.
#,##0,K will display numbers in thousands.
mm/dd/yyyy will display a date as 02/17/2014.
[h]:mm will display hours in excess of 24 hours.
[blue]0 will display a number in blue text.
[color12]0 will use color index 12, often olive.

In these simple formats, there is only one format being used. If you enter two formats separated by a semicolon, the first format is used for positive and zero value, and the second format is used for negative values. For example, [blue]0;[red]-0 will display negative numbers in red and other numbers in blue.

If you enter three formats separated by semicolons, the first format is for positive, the second format is for negative, and the third format is for zero. For example, [blue]0;[red]-0;[green]0 will display 0 cells in green text.

To show a plus sign before the positive numbers, use +0;-0;0.

If you type a second semicolon and leave out the final formatting code, Excel will suppress the display of zero values. For example, 0;-0; will show positive and negative numbers but hide zeros. Note that the final semicolon is a subtle but important difference from using 0;0. This figure shows the custom number format to hide zeros.

A custom number format to hide zero is Zero, Semi-Colon, Minus Zero, Semi-colon. It is the final Semi-Colon that sets up a blank zone for Value if Zero. — Figure 760. The zero in C2 is not displayed.

If you specify a fourth number format, it is used for text values.

To hide all values in a cell, you can use ;;; as the custom number format.

Additional Details: The custom number formats were written long before Microsoft started using conditional formatting. You can change the formatting based on meeting certain criteria. For example, the following code would display numbers above 10,000 in thousands and other numbers normally:

[<10000]#,##0;[>=10000]#,##0,K,.

In many cases in which you might use concatenation to join text and a number, you could use a custom number format instead. Here, cell B8 contains a SUM function, yet the result is displayed with a payment message.

A cool trick: a SUM function in C8 returns a calculated value of -124.56. However, the cell displays "Credit Balance of $124.56. Do not Pay." — Figure 761. This SUM function produces a message.

Further, the message changes, depending on whether the balance is positive, negative, or zero. Below, the three cells show the message for each state. You control the messages by using three zones in the custom number format. Note that in the negative zone, there is no minus sign in the number format, so Excel displays the number as positive. In the zero zone, there are no numeric characters at all, so Excel displays the No Balance Due message.

The entire custom number format uses three separate phrases for positive, negative, and zero:
"Please Remit "$#,##0.00;
"Credit Balance of "$#,##0.00;
"No Balance Due" — Figure 762. New message when the answer is positive, negative, or zero.

Additional Details: There is a subtle difference between the 0 and # when used after the decimal point in a custom number format. A # indicates that Excel can display the digit if there is sufficient precision in the value. A 0 indicates that Excel must display the digit. The 0.000 format would cause 123.4 to display as 123.400 even though the last two digits are zero. The 0.0## format ensures that there is always one decimal place, but the second and third decimal places are used only if necessary.

Additional Details: To fill the white space before a number, precede the number format with two asterisks. Similar to the security feature of old check printers, asterisks will appear before the number.

A custom number format like the old mechanical check printers. The empty space to the left of the number is filled with asterisks. There are automatically more asterisks to the left of a short number like 1 than to the left of a long number like 10235. — Figure 763. Custom format of **0.

Additional Details: You can use zeros before the decimal point to force Excel to display leading zeros. The custom format 00000 will ensure that the zip code for Cambridge, Massachusetts, prints as 02142 instead of 2142. If you need a part number to appear as 4 digits, you can use the custom format 0000 to force leading zeros to appear.

This article is an excerpt from Power Excel With MrExcel

Title photo by Bernard Hermant on Unsplash