Suppose you’re scrolling through some data and you come across an interesting value. You’d like to know if there are more values like the current one. It might be a name on a privilege log, the name of a creditor, a part number, etc. At this point, you would probably turn on the filter, and look for this value in the dropdown list. If there are many values for that field, finding the right one could be difficult.
Excel has a powerful but little-known shortcut that enables you to filter against the selected cell rather than having to look for it in the dropdown. We’ll add this option to the Quick Access Toolbar, or QAT. This is the set of shortcut keys in the very top left-hand corner of the Excel screen—most people have shortcut keys for save, undo, and print preview, but you can add whichever tools you want by customizing the QAT.
Just follow these steps:
1. Hover your mouse over the menu icons at the top of the Excel screen and click the right mouse button. Select the option called ‘Customize Quick Access Toolbar…’
2. Choose the menu of commands called All Commands. Then scroll down to an unassuming one called AutoFilter and select it. Hit the Add button to add this to your QAT. Now hit OK to complete.
Your shortcut menu now includes this new icon—’Autofilter’ doesn’t really do it justice, so I like to call it the Filter by Selection button:
Now, in the sales data below, you might want to see the data like that in Row 11–namely, for female salespeople from Massachusetts.
First you would select cell B11, which is populated with ‘Massachusetts’. Hit the Filter by Selection button, and Excel automatically sets the filter on Column B to equal ‘Massachusetts.’ Then click cell C11 and hit Filter by Selection again, and Excel will narrow the filter to only rows which also have Female in Column C. Notice that we didn’t even need to have the filter turned on when we started to use Filter by Selection.
If your Filter by Selection proves to be a dud, you can just hit Ctrl+Z (undo) to get back to the previous filter. Of course, you can still undo all the filter settings by hitting the Clear button on the Filter menu.
Finally, to get the most out of this new button, deploy the keyboard shortcut. When you hit the Alt key, Excel assigns numbers to each button on the QAT. On my menu Filter by Selection is the 5th button, so I can hit Alt+5 to activate it:
This is a powerful tool for analyzing any long list with many different entries. For example, a search term hit report may list thousands of email subject headers. When you come across one that you suspect may be duplicated, finding it in the filter dropdown list will be difficult. Likewise, in a massive privilege log when you come across an unfamiliar name and want to see all other emails from that person, the Filter by Selection button will be a lifesaver!
If you have any favorite filtering tricks, please post them in the comments below. As always, feel free to share, forward or link this tip!
Excel Esquire 