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

Filter by Selection like in Microsoft Access

September 08, 2022 - by

Filter by Selection like in Microsoft Access

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.

Customize the Quick Access Toolbar. Choose All Commands from the Choose Commands From drop-down. Find the icon called AutoFilter.
Figure 677. AutoFilter is really Filter by Selection.

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.

With the new AutoFilter icon in the Quick Access Toolbar, and without any filters applied, choose WalMart from the customer column. Click the AutoFilter in the QAT.
Figure 678. Choose the value that you want, click AutoFilter.

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

After autofiltering to Walmart, choose East in the Region column and AutoFilter again. Then ABC in the product field and click AutoFilter. You've filtered three columns without opening a single drop-down menu.
Figure 679. Filter by Selection will change your life.

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.

In the blank cell below a column of sales figures, type >300000. Keep that cell selected when you click the AutoFilter icon.
Figure 680. Apply a filter on the fly

The result: only rows above $300K.

The data is filtered to show items above $300,000
Figure 681. A fast way to filter to a range.

This article is an excerpt from Power Excel With MrExcel

Title photo by NordWood Themes on Unsplash