Online Test Banks
Score higher
See Online Test Banks
eLearning
Learning anything is easy
Browse Online Courses
Mobile Apps
Learning on the go
Explore Mobile Apps
Dummies Store
Shop for books and more
Start Shopping

Custom Number Formatting in Excel Reports: Zeroes and Colors

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.

#,##0_);(#,##0);"n/a"

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.

#,##0_);(#,##0);

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.

image0.jpg

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.

[Blue]+0%;[Red]-0%

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:

[Black]
[Blue]
[Cyan]
[Green]
[Magenta]
[Red]
[White]
[Yellow]
blog comments powered by Disqus
Advertisement

Inside Dummies.com

Dummies.com Sweepstakes

Win $500. Easy.