Filter by Selection in Excel
March 01, 2018 - by Bill Jelen
![Filter by Selection in Excel Filter by Selection in Excel](/img/excel-tips/2018/03/filter-by-selection-in-excel.jpg)
Microsoft Access offers the awesome Filter by Selection icon. Choose a field that contains "Widget", click Filter by Selection, and you see all Widgets. You can further filter by choosing a field that contains Brown and filtering again. Excel secretly offers the same functionality.
First, ever since Excel 2007, you could access something like Filter by Selection by using right-click, Filter, Filter by Selected Cell's Value. But this entry does not allow you to right-click and Add to Quick Access Toolbar, so you don't have one-click access to the command.
![Buried in the right-click menu](/img/content/2018/03/filter-by-selection-in-excel-01.jpg)
But there is a much faster way that has been in Excel since at least Excel 2003. Right-click on the Ribbon and choose Customize Quick Access Toolbar.
- Right-click on the Ribbon and choose Customize Quick Access Toolbar.
- There are two drop-downs at the top of the dialog. Open the left drop-down and choose Commands Not in the Ribbon
- Look for AutoFilter in the first page of choices on the left. Click AutoFilter. Click the Add>> button in the middle. Click OK.
![Add AutoFilter to your QAT](/img/content/2018/03/filter-by-selection-in-excel-02.jpg)
Notice in this figure that the filter drop-downs do not have to be enabled. Choose a cell that says Widget. Click the AutoFilter icon in the QAT.
![Select a cell that contains widget](/img/content/2018/03/filter-by-selection-in-excel-03.jpg)
Bingo - you will see only the Widget rows. To further filter to only Chicago widget sales, choose any cell that contains Chicago and AutoFilter again.
![Faster than opening the filter drop-downs](/img/content/2018/03/filter-by-selection-in-excel-04.jpg)
Filter by Selection (aka AutoFilter) works when you are choosing one value from a single column. It will not work if you needed to see all Saturday or Sunday sales. That still requires you to open the Filter drop-down.
This feature also will not work if your data is formatted as a Table with Ctrl + T.
Thoracic Thursday - my favorite heart-pounding features in Excel.
Excel Thought Of the Day
I've asked my Excel Master friends for their advice about Excel. Today's thought to ponder:
"Don't use separate sheets as a filtering system (months, regions, etc.)"
Title Photo: Ricardo Gomez Angel / Unsplash