MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Use Autofilter With A Pivot Table


April 12, 2021 - by Bill Jelen

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.



Bill Jelen is the author / co-author of:

MrExcel 2021 – Unmasking Excel

This is a 5th edition of MrExcel XL. Updates for 2021 include: LAMBDA, LET, Power Query Fuzzy Match, Sort & Filter in Sheet View, Cut-out people, Save object as image, STOCKHISTORY, Wolfram Alpha Data Types, Custom Data Types from Power Query, Weather data types, bilingual spreadsheets, Performance improvements, Unhide multiple worksheets, Action pen, Collapsible task panes, LET function to re-use calculations, store formulas using LAMBDA, Recursive LAMBDA, Branching LAMBDA, Lambda to return a picture, Excel function quick reference.