AutoFilter By Selection In Excel

In Excel 2003, you can add a couple of buttons to the toolbar to make it easy to filter a table. For example, in the table below, the East region is selected. With one click of a button, and no programming, you can add an AutoFilter and filter the table to show only the East region orders. Thanks to Roger Govier for sharing this tip.

[Update: For Excel 2007 instructions, see Easy Filtering in Excel 2007]

AutoFilterBtns02

Add Buttons to the Toolbar

You can add two buttons to the toolbar, to make filtering easy. One button will filter the table for the selected item, and the other button will show all the records.

To add the buttons:

  1. On the menu bar, click Tools, then click Customize.
  2. In the Customize dialog box, click the Commands tab.
  3. Click the Data category, then drag the AutoFilter command to an existing toolbar.
  4. Drag the Show All command to an existing toolbar.
  5. Close the Customize dialog box.

AutoFilterBtns01

Apply the AutoFilter

Now you can use the new buttons to filter the table, or to show all the records. The table doesn’t need to have an AutoFilter currently applied.

  1. In a table in Excel, select a cell that contains the criterion you’d like to use. For example, to filter for the East region records, select an East cell in the Region column.
  2. On the toolbar, click the AutoFilter button

The table is filtered, and shows only the East region records.

Remove the Filter

To remove the filter, and show all the records again:

  1. Select any cell on the worksheet
  2. On the toolbar, click the Show All button.

The filter is removed, but the AutoFilter feature is still turned on, and all the records are visible.

For more information on Excel AutoFilters, visit the pages on Excel AutoFilter Basics and Excel AutoFilter Tips on my website.

Watch the Video

To see the steps, you can watch the short video below.

_________________________

You may also like...

6 Responses

  1. Jon Peltier says:

    That’s a nice time-saving feature. Too bad it doesn’t work for lists.

  2. Peggy Duncan says:

    This is so cool. As long as I’ve been autofiltering, I never knew this! I’m presenting Excel tips at the American Bar Association’s TechShow in a couple of weeks and I will demo this. Thanks Debra and Roger!

  3. To turn the Autofilter off a simple macro will do the job

    Sub filter_off()
    ActiveSheet.AutoFilterMode = False
    End Sub

    You can assign this macro to a button and put it in a toolbar.
    I posted a simple technique to do it in my blog (in Spanish)

  4. AlexJ says:

    Why would this not work for me? Turns out that I had copied the identical looking AutoFilter button from the Data>Filter>AutoFilter menu selection. The filter on/off works, but filtering on the selection does not.

    A little further testing shows that the Tools Menu item is button ID 899, while the item you demo from the customization list is ID 458.

    Is there a way to account for the difference?

  5. Contextures Blog » AutoFilter by Selection in Excel 2007 says:

    […] couple of weeks ago I described how you could select a cell in a table, and automatically filter the list based on that cell’s value. The same feature is available in Excel 2007, using a different […]

  1. May 3, 2013

    […] If you can select a cell in your table that contains the name to want to filter by, maybe this will help you: AutoFilter By Selection In Excel | Contextures Blog […]

Leave a Reply

Your email address will not be published. Required fields are marked *