Spreadsheets: Suppressing Cells in a Pivot Table

Editor’s Note: To read more about spreadsheets, share a tip with other readers, or suggest a topic for Bill Jelen to cover in an upcoming column, click here to visit CFO.com’s Spreadsheet Tips Page.

Victoria C. wins a copy of Pivot Table Data Crunching for her question, “In pivot tables, how do I “suppress” or eliminate those cells in which across all columns, in a particular line, the summation is zero? I don’t want that line to appear in the pivot table for presentation purposes, as it is not relevant and need to reduce the size of the table.”

Let’s take a look at a large table, like the one that I’ve prepared in Figure 1 (below) that spans all twelve months. In the example, the pivot table shows several cost centers with a zero in the grand total column. The goal in this exercise is to hide those rows. You would think that the filters available in a pivot table would allow this, but the built-in pivot table filters simply do not do the job.

Fig. 1

MrExcel July 6 Fig1

Your options are to either move to the PowerPivot utility in Excel 2010, or to use a secret hack that allows you to combine the AutoFilter with a pivot table.

As shown in Figure 2 (below), select the cell immediately to the right of the last heading cell. On the Data tab of the ribbon, select the large Filter icon. You now have the AutoFilter dropdowns on each heading in the pivot table.

Open the dropdown for the Grand Total column. Uncheck the zero entry.

Fig. 2

Mr Excel July 6 Fig 2

The result is a pivot table suppressing the rows that total to zero, as show below in Figure 3.

Fig. 3

MrExcel July 6 Fig3

This method hides rows that had nonzero values in the columns that also totaled to zero. For example, if there is a positive 500 in March and a negative 500 in April, the row would total to zero and be suppressed. If this is a possibility, add a new column to the right of the pivot table. Use a formula such as =AND(MIN(B5:N5)=0,MAX(B5:N5)=0). Use the AutoFilter dropdown on this new column to suppress any rows where that formula is True.

Caution: Microsoft believes that autofilters and pivot tables do not work together. Anytime you choose a cell inside of a pivot table, the Filter icon is grayed out. (Figure 4).

Fig. 4

Mr Excel July 6 Fig 4

The trick in this week’s article is using a loophole. For some unknown reason, you can filter an adjacent data set by selecting the cell to the right of the last heading. Microsoft “forgets” to gray out the Filter icon when you are in this cell. The downside: If you refresh the pivot table to include updated data, you will have to remember to re-select the AutoFilter dropdown to re-apply the filter.

Bill Jelen is the author of 33 books about Microsoft Excel. For a chance to win a copy of one of his books, post a question at the Community Center to the right. If Bill selects your question as the topic of a future column, we’ll send you a book as a thank-you.
Bill’s next MrExcel Webcast, on consolidating data for financial reporting, is Thursday, July 14, at 2:00 Eastern Time.
<a href=”http://www.cfo.com/webcasts/index.cfm/l_eventdetail?webcast=14584275″ target=”” class=””>