Excel 2019: Filter by Selection in Excel
February 13, 2019 - by Bill Jelen
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.
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.
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.
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.
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.
You could continue this process. For example, you could choose a Utilities cell in the Sector column and click AutoFilter.
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.
Title Photo: Fancycrave at Unsplash.com