With data validation and some programming, you can select multiple items from a drop down list, and show the selections in a single cell.
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.
Filter in Excel 2003 and Earlier
For earlier versions of Excel, use the Custom option, at the top of the AutoFilter drop down list, to open the Custom AutoFilter dialog box.
Watch the Video
To see the AutoFilter Search box in action, please watch this short Excel tutorial video.