Custom Number Formatting in Excel Reports: Zeroes and Colors - dummies

Custom Number Formatting in Excel Reports: Zeroes and Colors

By Michael Alexander

In your Microsoft Excel reports, the best way to apply number formatting to cells is to use the Format Cells dialog box, which gives you have the ability to create your own custom number formatting.

Hide or suppress zeroes in Excel reports

Excel allows you to provide a format for zeroes. You do this by adding another semicolon to your custom number syntax. By default, any format syntax placed after the second semicolon is applied to any number that evaluates to zero.

For example, the following syntax applies a format that shows n/a for any cells that contain zeroes.


You can also use this to suppress zeroes entirely. If you add the second semicolon but don’t follow it with any syntax, cells containing zeroes will appear blank.


Again, custom number formatting only affects the cosmetic look of the cell. The actual data in the cell is not affected. This figure demonstrates this. The selected cell is formatted so that zeroes appear as n/a, but if you look at the formula bar, you can see the actual unformatted cell contents.


Apply custom format colors in Excel reports

Have you ever set the formatting on a cell so that negative numbers appear red? If you have, you essentially applied a custom format color. In addition to controlling the look of your numbers with custom number formatting, you can also control their color.

In this example, you format the percentages so that positive percentages appear blue with a + symbol, whereas negative percentages appear red with a – symbol. Enter this syntax in the Type input box.


Notice that all it takes to apply a color is to enter the color name wrapped in square brackets [ ].

Now, there are only certain colors — the eight Visual Basic colors — you can call out by name like this. These colors make up the first eight colors of the default Excel color pallet: