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 rows of data which boil down to about 8,200 rows in a PivotTable. The problem is that only about 230 of those rows 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 delete zero-balance rows before creating the PivotTable. If that is not possible, you could sort your data so that all the zero values are at the end of the data. Then, create your PivotTable so it only reflects the non-zero rows.

You can also use a filter 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 just the filtered data.

Another option is to use a filter 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 filter. Excel is smart enough to know that the filter should not apply to the blank column, but instead does its work on the rows that make up the PivotTable.