Software

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).

About

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.

8 comments
BarbaraBlakely
BarbaraBlakely

I am having trouble finding the "Tools" menu to customize. Is this not for Excel 2007?

lyambor
lyambor

Once folks learn to use Pivot Tables - and they are quite easy to produce these filtering results you never will bother with all these steps and having to customize toolbars etc Pivot Tables Rule!

Glenn_Jonas
Glenn_Jonas

BUT, what happened to the link for download of a .pdf version? I sure miss having the .pdf ready to go.

fieldrd
fieldrd

Keep 'em coming...tips like this help everyone in the user-level community. We're not all IT mgrs looking for new products. Again, thanks.

DANIELLEMM0782
DANIELLEMM0782

My new position requires me to create queries and reports in ACCESS and EXCEL, and I am fairly new to it. This shortcut is really helpful! It will save me alot of time. Thanks!

jamiepowell-smull
jamiepowell-smull

I am on win7/office 2010 and was not able to use the shortcut you describe only the right-click >Filter>Cell Value works. Was the option listed above removed in Office 10? Thanks.

Mark W. Kaelin
Mark W. Kaelin

Traditionally, we have not produced PDF versions of the Office tips. We are working to correct that and hope to deploy it soon.

ssharkins
ssharkins

I think I mentioned that you can add the filtering buttons to the QAT in Office 2007 and 2010. In 2010, you could actually add the buttons to a custom ribbon if you wanted.

Editor's Picks