Filter by Selection like in Microsoft Access
September 08, 2022 - by Bill Jelen

Problem: Microsoft Access offers an icon for Filter by Selection. Why isn’t this in Excel?
Strategy: Filter by Selection IS in Excel. It is hidden. It is mislabeled. It has been in Excel (hidden and mislabeled) for fifteen years.
To add Filter by Selection to the ribbon, follow these steps:
1. Right-click the Quick Access Toolbar and choose Customize Quick Access Toolbar.
-
2. Initially, you only see popular commands. Open the left dropdown and change from Popular Commands to All Commands.
3. Scroll down to find AutoFilter. Click on AutoFilter. Click the Add>> button in the center to add this icon to the Quick Access Toolbar. Click OK. You now have Filter by Selection on the QAT.

You can now filter very quickly. Say that you want all of the Wal-Mart, East, ABC records from a data set. It will be six clicks.
1. Select a cell that says Wal-Mart
2. Click Filter by Selection
3. Select a cell that says East.
4. Click Filter by Selection.
5. Select a cell that says ABC.
6. Click Filter by Selection.

The result is filters on three columns, without ever clicking the Filter button and without ever opening a Filter dropdown.

You can combine the Filter by Selection technique with any criteria value. In the following image, an entry of >300000 appears in the first row below the sales column. Select that cell and click the AutoFilter icon. Excel will filter to only the rows above $300K.

The result: only rows above $300K.

This article is an excerpt from Power Excel With MrExcel
Title photo by NordWood Themes on Unsplash