AutoFilter By Selection In Excel

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.
Customize dialog box
Customize dialog box

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, watch the short video below.

_________________________

6 thoughts on “AutoFilter By Selection In Excel”

  1. 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!

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

  3. 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?

  4. […] 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 […]

Leave a Reply to Anonymous Cancel reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.