AutoFilter by Selection in Excel 2007

A 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 technique.

Using the same example as in the previous post, the East region is selected in the table below. With a couple of clicks, and no programming, you can add an AutoFilter and filter the table to show only the East region orders.

FilterSel01

Apply the AutoFilter

In previous versions of Excel, you had to add a toolbar button to use the filter by selection feature. In Excel 2007, the feature is available in a shortcut menu. The table doesn’t need to have an AutoFilter currently applied.

  1. In a table in Excel, right-click a cell that contains the criterion you’d like to use. For example, to filter for the East region records, right-click an East cell in the Region column.
  2. On the shortcut menu, click Filter, then click Filter by Selected Cell’s Value

FilterSel02

An AutoFilter is added to the table, if there wasn’t already one in place. 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. In the Region column heading, click the AutoFilter drop down arrow
  2. Click Clear Filter From “Region”.

FilterSel03

The filter is removed from the Region column, but the AutoFilter feature is still turned on.

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

Paste As Values on a Filtered Sheet

In related news, I recently discovered that the mouse shortcut to copy and paste as values doesn’t work anywhere on a filtered sheet, unless all the records are showing. Here you can see that it’s not available on the shortcut menu.

PasteValuesMouse03

You can use other methods to copy and paste as values, such as the Ribbon command, but not the shortcut. I wonder why.

PasteValuesMouse04

______________________

You may also like...

2 Responses

  1. Guy says:

    Great feature – but, what I want to know is how I make the Basic Autofilter in Excel 2007 the same as Excel 2003 – ie without the annoying checkboxes.
    I only ever want to show one filtered item as I have a chart linked to the filtered list, and having to deselect to then select is VERY annoying. The 2003 dropdown worked so much better.

  2. JIM says:

    I agree that someone at Microsoft has a “checkbox” fetish. I find it to be very annoying and have thus uninstalled Excel 2007 and reinstalled Excel 2003. It works just fine.

Leave a Reply

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