A Better Way to Show Empty Values in a Pivot Table

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).

#,##0;#,##0;[Color15]#,##0

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

Sub ColorList()
Dim i As Integer
For i = 1 To 56
Cells(i, 1).Interior.ColorIndex = i
Cells(i, 2).Value = i
Next i
End Sub

.

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.

18 thoughts on “A Better Way to Show Empty Values in a Pivot Table

  1. Matt

    I agree w/ the 1st comment… Instead of entering “0” in the “For empty cells show:” box, enter “-” and it cleans it up a good bit without adding colors or graying out the zeros.

    Either way, I didn’t know about this option, and I’ve just used it on a few workbooks I use all the time. Thanks!

  2. Jon Acampora

    Great technique Mike! I also use the “-“, but this technique would be especially useful for fields in the pivot that are displaying percentages. To me, the “-” method doesn’t look as good in columns with percentages.

    Thanks for sharing!

  3. Jon Peltier

    Why do you center align your data? Imposing such alignment hides the difference between numerical data (right aligned by default) and numbers stored as text (left aligned by default). I guess in the data range of the pivot table, text would be represented as zero, but still.

  4. Bob Phillips

    I agree with Jon (as usual). If, like myself, you don’t like your numbers butting up against a cell edge and that is why you centre it, you can always right-align and add a gap with a custom number format of #,##0_w.

  5. Bob Phillips

    If tracking the row and column is your concern, you could rowstripe with pivot table styles or you could just highlight them with event code, such as

    Option Explicit

    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim pt As PivotTable
    Dim rng As Range

    Set pt = getPivot(Target)
    If Not pt Is Nothing Then

    pt.TableRange1.FormatConditions.Delete

    With Union(pt.DataBodyRange, pt.RowRange, pt.ColumnRange)

    .FormatConditions.Add Type:=xlExpression, Formula1:=”=OR(ROW()=CELL(“”row””),COLUMN()=CELL(“”col””))”
    .FormatConditions(1).Interior.ColorIndex = 35
    End With

    Application.ScreenUpdating = True
    End If
    End Sub

    Public Function getPivot(Optional ByRef cell As Range) As Object
    If cell Is Nothing Then Set cell = ActiveCell
    With cell

    On Error Resume Next
    Set getPivot = .PivotTable
    On Error GoTo 0
    End With
    End Function

  6. Rahim Zulfiqar Ali

    Hi ! Thanks for sharing this awesome tip

    1 Question: I have few cells that contains #N/A and I want to use Custom Formatting to show them in White Font so how what I have to write ?

    I know I can use IFERROR Function but I want to use Custom Formatting.

    Looking forward for response.

    Regards,
    Rahim Zulfiqar Ali

  7. datapig Post author

    Rahim:
    Right click on your pivot table and select PivotTable Options.

    you’ll see a setting called “For error values show:”
    Place a check next to that setting and enter “error”

    In the custom number formatting enter
    #,##0;#,##0;[Color15]#,##0;

    This will hide your errors.

  8. Dawn Hobs

    As an accountant, can’t agree more about right-aligned numerical values. This is all about the flow of the eye over data — larger amounts stand out instantly if right-aligned as do smaller ones.

    Centering cell entries – text or numerical values – is often quite tiring for eyes when looking at a large quantum of data.

    This also goes to (usually non-accountants’) mania for inserting dollar signs “everywhere”. Very hard to scan, tiring, and valueless.

  9. Doug Glancy

    It’s important to distinguish between empty spaces that show no activity, like zero sales, and those that are null, like a Built-In Warmer yanked in Quarter 3 for over-warming. If they’re null they should be left blank. If they’re zero, better they were fixed in the source data. But I do like the grayish zeros.

  10. Pete

    Hi,

    First I want to say this is an excellent little tip. I use Excel daily at work and graying out the zeros comes in very handy. I’ve even built a a toolbar macro that immediately applies (a variation of) this formatting to selected cells.

    One pretty big problem though that i didn’t notice until I’d used the formatting in dozens of spreadsheets…

    …negatives appear as positive. Eeeeek!

    The formatting should be:

    #,##0;-#,##0;[Color15]#,##0

    Pete

  11. Andrea

    Hi all,

    This is exactly what I would need, but it doesn’t work for me! I ticked the box ‘for empty cells show’, and would like to display ‘-‘, but it just doesn’t work! What could I have done wrong? Is there any other criteria for this to work?

    Thanks,
    Andrea

  12. KIRANMAI

    HOW TO GET VALUE ZERO IN PIVOT WHEN THER IS NO DATA.
    I AM GETTING GRAND TOTAL AS 0.
    BUT I WANT ALL ROWS AND COLUMNS SHOULD DISPLAY ZEROS WHEN THERE NO DATA FOR THE PIVOT.

  13. Pingback: How to Replace Blank Cells with Zeros in Excel Pivot Tables 

  14. Pingback: How to Replace Blank Cells with Zeros in Excel Pivot Tables 

Leave a Reply

Your email address will not be published. Required fields are marked *