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.

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.

MultiSelectFilter05

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.

MultiSelectFilter06

Watch the Video

To see the AutoFilter Search box in action, please watch this short Excel tutorial video.

__________________

Related Posts Plugin for WordPress, Blogger...

Share and Enjoy

  • Facebook
  • Twitter
  • LinkedIn
  • Google Plus
  • Pinterest

Leave a Reply

  

  

  

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>