22 May 2014

Excel 2010 build multi-criteria search in Autofilter

Excel 2010's search in autofilter can be used to easily build a multi-criteria filter but I found that the steps for adding the first criteria is different from adding the subsequent criteria.

Assuming your started Autofilter for your worksheet, the steps to add the first criteria are:

  1. Click on the filter icon in the column.
  2. In Autofilter pane, enter your criteria.
  3. Autofilter pane displays the reduced list, including your criteria (assuming it is found).
  4. Untick "(Select All Search Results)". Excel removes the tick marks from the reduced list, including your criteria. If you don't untick this option, all the rows in your worksheet remain selected.
  5. Don't tick "Add current selection to filter". If you tick this option, all the rows in your worksheet remain selected.
  6. Tick your criteria.
  7. Press the OK button to activate the filter.
  8. Excel closes the Autofilter pane and displays only rows containing your first criteria.

To add more criteria to the filter, the steps are:

  1. Click on the filter icon in the column.
  2. In Autofilter pane, enter your next criteria.
  3. Autofilter pane displays the reduced list, including your next criteria (assuming it is found).
  4. Leave "(Select All Search Results)" ticked.
  5. Tick "Add current selection to filter" to keep the rows previously filtered.
  6. Leave your criteria ticked.
  7. Press the OK button to update the filter.
  8. Excel closes the Autofilter pane and displays only rows containing all your criteria (including the first criteria).

What is confusing is that you can't see your previous criteria in the Autofilter pane; you have to infer it from the list of visible rows.