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.

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

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

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.

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

______________________