Excel: Filter by Selection

Problem: Microsoft Access offers an icon for Filter by Selection. Why isn't this in Excel?

Strategy: Filter by Selection IS in Excel. It is hidden. It is mislabeled. It has been in Excel (hidden and mislabeled) for fifteen years.

To add Filter by Selection to the ribbon, follow these steps:

1. Right-click the Quick Access Toolbar and choose Customize Quick Access Toolbar.
2. Initially, you only see popular commands. Open the left dropdown and change from Popular Commands to All Commands.
3. Scroll down to find AutoFilter. Click on AutoFilter. Click the Add>> button in the center to add this icon to the Quick Access Toolbar. Click OK. You now have Filter by Selection on the QAT.

1. AutoFilter is really Filter by Selection.

   You can now filter very quickly. Say that you want all of the Wal-Mart, East, ABC records from a data set. It will be six clicks.

   1. Select a cell that says Wal-Mart
   2. Click Filter by Selection
   3. Select a cell that says East.
   4. Click Filter by Selection.
   5. Select a cell that says ABC.
   6. Click Filter by Selection.

   

2. Choose the value that you want, click AutoFilter.

   The result is filters on three columns, without ever clicking the Filter button and without ever opening a Filter dropdown.

   

3. Filter by Selection will change your life.

   Additional Details: How can it be that Microsoft never tells anyone about this? Why did they continue to mislabel the icon as AutoFilter when Excel 2007 changed the term to Filter (and, in fact, they added a Filter icon in Excel 2007). This is one of the great mysteries. I've asked the Excel project managers about this. Someone added the code to do Filter by Selection but it was sort of an afterthought, not something they felt worthy of publicizing. If you believe some accounts, they collectively forgot the code was there. It was only during a code review for a lawsuit defense that someone happened upon it again. Filter by Selection is the greatest feature that no one knows about.