Excel 2010 :: Pivot table options :: Display tab

Or, much more easily, you can just:

1. Right click on any of the row labels (NOT data cells, the actual labels)
2. Select Field Settings from the context menu
3. Click on the Layout&Print tab
4. Select the show items with no data checkbox
5. Click OK

Refresh the table and, presto, your empty data shows up.

You can create the following VBA procedures; they will do the work for you.

DisplayEmptyMembers() displays the items with no data and HideEmptyMembers()

hides the items with no data in the pivot table "PivotTable1".

Sub HideEmptyMembers()

Dim pvtTable As PivotTable

Set pvtTable = ActiveSheet.PivotTables("PivotTable1")

pvtTable.DisplayEmptyColumn = False

pvtTable.DisplayEmptyRow = False

pvtTable.RefreshTable

End Sub

Sub DisplayEmptyMembers()

Dim pvtTable As PivotTable

Set pvtTable = ActiveSheet.PivotTables("PivotTable1")

pvtTable.DisplayEmptyColumn = True

pvtTable.DisplayEmptyRow = True

pvtTable.RefreshTable

End Sub

Yes, this worked for me as well - except where I am calculating averages - showing the columns without data changes the averages :(

Is there a way to tell a pivot table to ignore the zero entries in some cases?  I have a pivot table which calculates the Average Days To Close on Sales Leads. However, if there are zero leads that closed that month (in some obscure bucket of lead title) then the average is skewed greatly.

The classic PivotTable view doesn't exist?