Use Autofilter With A Pivot Table
April 12, 2021 - by Bill Jelen
Challenge: You’ve created a pivot table to summarize sales by customer. You now want to filter those results to show only the customers with sales between $20,000 and $30,000. The AutoFilter command is grayed out for pivot tables.
Solution: You can fool Excel into turning on the AutoFilter dropdowns by starting your selection one cell to the right of the pivot table headings. In Figure 67, select cell E4. Hold down the Shift key and press the left arrow key four times to select E4:A4.
In Excel 2003, you can select Data, Filter, AutoFilter to turn on the AutoFilter dropdowns. In Excel 2007, choose the Filter command from the Data tab.
Figure 68 shows how you apply a custom filter to limit the customers to those with sales between $20,000 and $30,000.
Summary: You can trick Excel into allowing filters in a pivot table by starting the selection outside the pivot table.
Source: MrExcel's Learn Excel #793 - Pivot Filter Hack on the MrExcel Message Board.
Title Photo: Ussama Azam on Unsplash
This article is an excerpt from Excel Gurus Gone Wild.