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

Excel Filter by Selection

September 02, 2014 - by

There is a great Filter by Selection icon in Access. Excel offers the same functionality, but it is hidden and mislabeled!

Excel Filter by Selection
Excel Filter by Selection

To add the command to your Quick Access Toolbar:

  1. Right click the QAT
  2. Choose Customize Quick Access Toolbar
  3. From the top left dropdown, change from Popular Commands to All Commands
  4. In the left listbox, select AutoFilter.
  5. In the center, click the Add >> button.
  6. 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.

Tip

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.