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
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.
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.
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.
Then, type the option or options that you want to filter. There is a limit of two options in the Custom AutoFilter.
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.
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.
__________________