Software

Add autofilter to Excel and filter data by selection

Cut filtering tasks by more than half by adding the AutoFilter option to the ribbon or toolbar.

Filtering data by selecting a value seems like a nice feature. You select a region, click a button, and Excel displays only those sales records for the selected region. Nice! Excel does support selection filtering, but you'll have to go looking for it because it's not available by default on the ribbon or toolbar. Fortunately, once you've added the option, selection filtering is just two clicks away.

First, let's take a look at the available filtering process. Specifically, let's suppose you want to limit sports apparel sales records. You might do so, as follows:

  1. Click anywhere inside the data range.
  2. Click the Data tab. In Excel 2003, choose Filter from the Data menu, select AutoFilter, and skip to step 4.
  3. Click Filter in the Sort & Filter group. Doing so displays the filtering arrows in the header cells.
  4. Click the Item column's filtering arrow and uncheck Select All (the default).
  5. Check T-shirt, and click OK. (Excel 2003's filter is a bit easier to use - just select the value).

To clear the filter, click Clear in the Sort & Filtering group. In Excel 2003, choose Filter from the Data menu and then Show All. It's a tedious process if you do it often.

Now, let's reduce the previous process to just two clicks by enabling the filter by selection option (AutoFilter), as follows (in Excel 2010):

  1. Click the File tab and then choose Options (under Help).
  2. In the left pane, choose Customize ribbon.
  3. Choose All Commands from the Choose Commands From dropdown.
  4. Select AutoFilter in the resulting list.
  5. To the right, click the Data tab and then click New Group.
  6. Click Move Up to position the new group right after the Sort & Filter group.
  7. With the new group selected, click Add.
  8. Click OK and Excel adds a custom group with the AutoFilter option.

In Excel 2007, add this option to the Quick Access  Toolbar (QAT), as follows:

  1. From the QAT dropdown, choose More Commands.
  2. Repeat steps 3 through 8 above.

If you're still using Excel 2003, customize the toolbar as follows:

  1. Choose Customize from the Tools menu.
  2. From the Categories list, select Data.
  3. In the Commands list, find AutoFilter and drag it to the toolbar.
  4. Repeat step 3 for Show All.
  5. Click Close.

Now, you're ready to use the new filtering option, as follows:

  1. First, select a cell that contains the value you want to sort by. For instance, select Medium in column C.
  2. Next, click AutoFilter (the new option in the custom group you just added). In Excel 2007, you'll find it on the QAT. In Excel 2003, you'll be using the button you just added to the toolbar.

That's it. In just two clicks, you filtered your data! You can use the AutoFilter option with any column or any value in your data set. You can further hone your records by moving to a second column, choosing a value, and clicking AutoFilter. As you can see below, the filtered recordset now shows Medium T-Shirt records, not just all Medium records.

Demonstration Excel files are provided to help you explore this technique.

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.

16 comments
KeyCuts
KeyCuts

Another great thing about the QAT (in Excel 2007 and above) is that you can access it via keyboard shortcuts. All you have to do is hit the ALT key and numbers should pop up on the QAT that indicate which number key to press to use that Excel operation.

delsimcox
delsimcox

Thanks for sharing this Susan - I've used Excel many years and had always been frustrated with the number of mouse clicks required to use AutoFilter. This takes away all of the pain. I love it!

jbenton
jbenton

once again 2003 does it much quicker and simpler One word of caution on using the Show All/Clear All Filters option: if you have a very wide and long spreadsheet, it can take ages as it resets every column even if not filtered. In such cases it's quicker to toggle the autofilter off and on again.

Stuart Forbes
Stuart Forbes

you might want to add the "clear all filters" to the QAT as well. It quickly clears all filters used in a spreadsheet which can prevent other mistakes.

ssharkins
ssharkins

I'm glad you found this useful. I'm not sure why Excel hides it -- it should be much easier to use.

jbenton
jbenton

If you edit and display the description to include an accelerator (ie use a & before the letter you want to designate - I use c for "Cell Contents") then you don't even have to leave the keyboard; just press alt-c (or whatever you've chosen) and the current area is filtered by the contents of the active cell Even if there's no autofilter in place, Excel will set one up and add the arrows at the head of each column in the range

ssharkins
ssharkins

The Table feature limits a number of built-in features, so I'm not surprised it doesn't work with a Table. I should've checked it myself, but just didn't think to. Thanks for letting us know.

dogknees
dogknees

I tried doing this in 2003, but it doesn't behave the same way. Can you tell me where it is in the menu? I can do a normal AutoFilter, but that doesn't immediately filter the list based on the value selected like 2010 does. Thanks

sparent
sparent

I only see Clear Filters and it doesn't work for me.

jbenton
jbenton

this won't work for the header row in a range of data - but it will set up the autofilter for you, just not apply it

jbenton
jbenton

as outlined so admirably in the article above

delsimcox
delsimcox

From the Excel Options - Quick Access Toolbar step, Select Choose commands from "Data Tab". Select the filter icon that has the red "x" beside it, and Add this icon to the Quick Access Toolbar. This is the "clear all filters" button that Stuart Forbes is referring to - and it works as expected.

jbenton
jbenton

so maybe I shouldn't comment on 2010, but it seems that there's always more instructions needed for 2010 to achieve anything (so only a small step back) What I did (many years ago now) is exactly what Susan says in the article (I might expand step 2 to read "From the Categories list on the Commands tab, select Data") It's very straightforward, just note that this "AutoFilter" is different to the one found on the Data, Filter submenu from the main menu (despite having the same name and icon) This one (the one found via the Customise dialogue) sets up a filter based on the contents of the current cell. The other (found via the Standard toolbar) just toggles the filtering ability for the current range - for which you then have to select an item from a dropdown to actually filter your data So, having dragged the correct icon from the customise dialogue to your toolbar, all you need to do is select a SINGLE CELL within your range (and not in the 1st row) and click the button. An autofilter will then be set up on the current cell's contiguous area (if not already present) and the area filtered, based on the current cell's contents hope that's clear now and I haven't missed your point entirely; please let us know

dogknees
dogknees

I was asking how "you" do it in 2003? Not 2010. I tried the Auto-filter option in Excel 2003, and it's doesn't do what is described in the article above. You're saying it's harder in Excel 2010 than Excel 2003, but it appears to be impossible in Excel 2003 without some VBA code. Or am I missing some option in Excel 2003?

sparent
sparent

I found it. It's called... Clear