AutoFilter For Multiple Selections

AutoFilter For Multiple Selections

With data validation and some programming, you can select multiple items from a drop down list, and show the selections in a single cell.

Multiple Selections from a Drop Down List

MultiSelectFilter01

That technique is helpful in some situations, but it can make filtering difficult. With an AutoFilter turned on, only the combined results show in the filter options, not the individual items.

MultiSelectFilter02

Filter for a Single Item

This week, Ben emailed to ask how to filter that combined list for a single item. For example, how could you see all the rows where “Two” was selected?

If you’re using Excel 2010, you can type in the filter Search box, just above the list of filter options. As you type, the options are automatically filtered to show only the items that contain the text that you are typing.

In the screen shot below, I have typed “two”, and only the matching options remain in the list. Click OK, and only the selected items are visible in the filtered list.

MultiSelectFilter03

Filter in Excel 2007

For Excel 2007 AutoFilters, where there is no Search box, you can use the Text Filters command.

Click the Contains command, to open the Custom AutoFilter box.

MultiSelectFilter04

Then, type the option or options that you want to filter. There is a limit of two options in the Custom AutoFilter.

MultiSelectFilter05

Filter in Excel 2003 and Earlier

For earlier versions of Excel, where neither of those features is available, here’s what you can do.

Click the Custom option, at the top of the AutoFilter drop down list, to open the Custom AutoFilter dialog box.

Then, enter one or two options for the AutoFilter, just as described above, for Excel 2007.

Filter in Excel 2003 and Earlier
Filter in Excel 2003 and Earlier

Watch the Video

To see the AutoFilter Search box in action, watch this short Excel tutorial video. It shows the steps for finding specific items within text strings in an Excel column.

__________________

Leave a Reply

Your email address will not be published.

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