MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Excel 2019: Filter by Selection in Excel


February 13, 2019 - by Bill Jelen

Excel Filter by Selection. Photo Credit: Fancycrave at Unsplash.com

The filter dropdowns have been in Excel for decades, but there are two faster ways to filter. Normally, you select a cell in your data, choose Data, Filter, open the dropdown menu on a column heading, uncheck Select All, and scroll through a long list of values, trying to find the desired item.

The filter drop-down menu offers a Search box to quickly find an item.

One faster way is to click in the Search box and type enough characters to uniquely identify your selection. Once the only visible items are (Select All Search Results), Add Current Selection to Filter, and the one desired customer, press Enter.

But the fastest way to Filter came from Microsoft Access. Microsoft Access invented a concept called Filter by Selection. It is simple: find a cell that contains the value you want and click Filter by Selection. The filter dropdowns are turned on, and the data is filtered to the selected value. Nothing could be simpler.

Starting in Excel 2007, you can right-click the desired value in the worksheet grid, choose Filter, and then choose By Selected Cells Value.

Guess what? The Filter by Selection trick is also built into Excel, but it is hidden and mislabeled.

Here is how you can add this feature to your Quick Access Toolbar: Right-click anywhere on the Ribbon and choose Customize Quick Access Toolbar.


Right-click the Ribbon and the second choice is Customize Quick Access Toolbar. Other choices shown in the screenshot but not discussed are Show Quick Access Toolbar Below the Ribbon, Customize the Ribbon, and Customize the Ribbon.

There are two large listboxes in the dialog. Above the left listbox, open the dropdown and change from Popular Commands to Commands Not In The Ribbon.

In the left listbox, scroll to the command AutoFilter and choose it. That’s right: The icon that does Filter by Selection is mislabeled AutoFilter.

In the center of the dialog, click the Add>> button. The AutoFilter icon moves to the right listbox, as shown below. Click OK to close the dialog.

Using the Commands Not In The Ribbon category, find AutoFilter and click Add>> to add it to the Quick Access Toolbar.

Here is how to use the command: Say that you want to see all West region sales of widgets. First, choose any cell in column B that contains West. Click the AutoFilter icon in the Quick Access Toolbar.

This shows an unfiltered data set. The cell pointer is on the word West in the Region column. The mouse cursor is about to click the AutoFilter icon in the Quick Access Toolbar.

Excel turns on the filter dropdowns and automatically chooses only West from column B.

Next, choose any cell in column E that contains Widget. Click the AutoFilter icon again.

The Filters have been activated. The Region column is only showing West. Next, in the Product column, a cell containing Widget is selected and the mouse is about to click AutoFilter again.

You could continue this process. For example, you could choose a Utilities cell in the Sector column and click AutoFilter.

Caution

It would be great if you could multi-select cells before clicking the AutoFilter icon, but this does not work. If you need to see sales of widgets and gadgets, you could use Filter by Selection to get widgets, but then you have to use the Filter dropdown to add gadgets. Also: if you are in a Ctrl+T table, you can not use the AutoFilter icon in the Quick Access Toolbar. You can still right-click a cell, choose Filter, By Selected Cells Value.

How can it be that this feature has been in Excel since Excel 2003, but Microsoft does not document it? It was never really an official feature. The story is that one of the developers added the feature for internal use. Back in Excel 2003, there was already an AutoFilter icon on the Standard toolbar, so no one would bother to add the apparently redundant AutoFilter icon.

This feature was added to Excel 2007’s right-click menu - but three clicks deep: Right-click a value, choose Filter, then choose Filter by Selected Cell’s Value.

Thank you to Excel MVP Roger Govier for pointing out that you can still use Filter by Selected Cells Value in a Ctrl+T table.

Title Photo: Fancycrave at Unsplash.com


Bill Jelen is the author / co-author of
MrExcel LX – The Holy Grail of Excel Tips

A book for people who use Excel 40+ hours per week. Illustrated in full color.