It’s not uncommon to have empty values in a pivot table.
When you do, you get gaps in your pivot report like the ones you see here.
The problem with the empty values is that the gaps can make it difficult to keep track of the row and column you’re looking at; especially when showing lots of data.
For this reason, Excel lets you replace empty cells with some character.
Simply right-click your pivot table and select the PivotTable Options.
You’ll see an option to use a specific character to represent empty values.
Most of us use zero.
This is marginally better, but now we’re inundating our audience with lots of nonsense zeros.
To alleviate this you can use some custom number formatting so that any data value showing zero is subdued.
To do this follow these steps:
1. Right-click on any number in the Data area of your pivot and select Value Field Settings.
2. Select the Number Formatting button
3. In the Format Cells dialog box, click Custom
4. In the Type input box, enter #,##0;#,##0;[Color15]#,##0
5. Press the OK button to confirm the change.
If all went well, you’ll still see the zeros in your pivot table, but they’ll be colored light grey. This lets you fill the empty values while at the same time letting your audience see the real data without have to fight through a bunch of zero values.
So how does this work? Well, every number format has essentially 4 parts (separated by semicolon).
format for positive numbers ; format for negative numbers ; format for zero ; format for text
In this trick, you’re telling Excel to format the third part (the part for zeros) so that it has a specific color using the [ColorN] syntax (where ‘N’ represents a number from 1 to 56).
You can actually call up any one of the 56 colors defined in the standard color palette by number. In this example, I use [Color15] to represent light grey.
Your next question should be, “how do you know which number represents which color”? Well, you can run this small macro to get a list of colors and their associated numbers
Dim i As Integer
For i = 1 To 56
Cells(i, 1).Interior.ColorIndex = i
Cells(i, 2).Value = i
Running the macro will give you a list similar to the list you see here (although your colors may be different based on your current color palette).
Now you don’t have to feel so bad about all those zeros in your pivot table reports.