Quick Tip: Easy filtering of data in Excel

Filters for data in Microsoft Excel are just a click away with this simple-to-implement filtering technique. Susan Harkins shows you how it works.

There are many ways to filter Excel data. The sheet below shows an advanced filter in place. Rows 1 through 4 comprise the criteria range. The data range begins with the headings in row 5. The current criteria, ="Beverages", would return only those rows whose Category value equals the string Beverages. It's a simple implementation that can handle very complex criteria expressions.

The above arrangement makes it possible to match records using expressions and multiple criteria. When the criteria is natural data, as in Beverages, you don't need such a complex solution. You can use Excel's AutoFilter By Selection feature instead. To enable this feature, you choose Filter from the Data menu and choose AutoFilter. Excel adds a dropdown to each of the heading cells. You use these dropdown lists to quickly filter the list.

But I can show you an even quicker way to filter records by existing data: add the AutoFilter and Show All buttons to a toolbar. Then, you simply select a value and click a button—you don't need the dropdown filters at all. To add these buttons to the toolbar, do the following:

  1. From the Tools menu, choose Customize.
  2. Click the Commands tab.
  3. From the Categories list, select Data.
  4. From the Commands list (on the right), drag AutoFilter to a toolbar and drop it.
  5. Also drag Show All (you'll need it to display hidden records).
  6. Click Close.

Now, to see only the Beverages records, select a cell in column G that contains the string Beverages. Then, click the AutoFilter button. Click Show All to remove the filter and show all the records.

This feature is easily accessible in Excel 2007 and 2010. Simply right-click the cell that contains the value you want to filter by. Choose filter from the resulting context menu and then select Filter By Selected Cell's Value. Or, you can add AutoFilter and Show All to the Quick Access Toolbar (QAT).


Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.

Editor's Picks