MrExcel.com - Your One Stop for Excel Tips & Solutions

Use Autofilter With A Pivot Table

April 12, 2021 - by

Use Autofilter With A Pivot Table

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.

Figure 67. Start your selection just to the right of the pivot table headings.
Figure 67. Start your selection just to the right of the pivot table headings.

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.

Figure 68. Use the filter dropdowns on the pivot table.
Figure 68. Use the filter dropdowns on the pivot table.

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.