Filter by Selection in Excel

March 01, 2018 - by Bill Jelen

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.

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.

1. Right-click on the Ribbon and choose Customize Quick Access Toolbar.
2. There are two drop-downs at the top of the dialog. Open the left drop-down and choose Commands Not in the Ribbon
3. Look for AutoFilter in the first page of choices on the left. Click AutoFilter. Click the Add>> button in the middle. Click OK.

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.

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.

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.

Title Photo: Ricardo Gomez Angel / Unsplash