Suppressing Zero Values in PivotTables

Many people use the PivotTable feature of Excel to help analyze their data. One such person is Chris, who explained how he has approximately 40,000 lines of data which boil down to about 8,200 lines in a PivotTable. The problem is that only about 230 of those lines have non-zero values in them. Chris was looking for a way to suppress the PivotTable rows that contain zero balances.

There are various ways that a solution can be approached. For instance, you could work with the original data and deleting zero balance rows before creating the PivotTable. If that is not possible, you can use AutoFilter on the data, before creating the PivotTable, that would show only rows that are not equal to zero. In other words, create a PivotTable using filtered data.

Another option is to use AutoFilter after the PivotTable is created. All you need to do is select the column to the immediate right of the PivotTable and then create the AutoFilter. Excel is smart enough to know that the AutoFilter should not apply to the blank column, but instead does its work on the rows that make up the PivotTable.