Excel Filter by Selection
September 02, 2014 - by Bill Jelen
There is a great Filter by Selection icon in Access. Excel offers the same functionality, but it is hidden and mislabeled!
To add the command to your Quick Access Toolbar:
- Right click the QAT
- Choose Customize Quick Access Toolbar
- From the top left dropdown, change from Popular Commands to All Commands
- In the left listbox, select AutoFilter.
- In the center, click the Add >> button.
- Click OK
To use the command, select one cell in your data. For example, choose a cell in the customer column with Ford. Click the Funnel icon in the QAT. Excel will turn on the Filter dropdowns and limit the customer column to just Ford. No need to open the dropdown, uncheck (Select All), then find Ford.
To further filter by other columns, click a value (such as East in the Region column) and click the Funnel icon again.
This is one of the tips in Learn Excel 2007-2010 from MrExcel – 512 Excel Mysteries Solved.