Filter for multiple values or apply complex criteria by using an Advanced Filter

Instructions: You can type values or formulas into cells above but to access Excel functionality, or if it is easier to work directly in Excel, open the file by clicking on the Excel Icon at the bottom.

If the file does not display correctly try right clicking in the area above and selecting ‘Refresh’. If you are still unable to view the file properly, or it contains unsupported features, click here to download a copy.

In Excel 2007 you can filter a range of data by clicking on the Filter (funnel) Icon in the Data tab of the Ribbon which will then put filter drop down arrows next to all the headings. Clicking on these arrows gives you some rich filter and sorting functionality – for example you can sort in ascending/descending order, filter by colour (which is very useful for when cells/rows have been colour coded either manually or using conditional formatting), and you can use the ‘Text Filters’ functionality to filter for all values which meet criteria that you indicate (such as equal to/containing/greater than/less than) for up to 2 conditions/values.

This is shown in the screen shot below:

This is all good and well but what about when you need to filter for more than 2 values? Say you have a list of 1000 Account codes and need to filter for a sub-set of about 100 of these that you have in another list? The manual method in Excel 2007 would be to use the tick-box selector window in the filter options to manually check/un-check as required but this is time-consuming and prone to error. Alternatively you could use a Lookup formula to Compare Lists. But a more efficient way to do this is to use Excel’s Advanced Filter functionality which is what we’ll look at in this topic.

To use this we firstly need to set up our custom filter using the list which contains the values we want to filter for (perhaps contained on a separate worksheet) . Then secondly we need to use the Advanced Filter functionality on the range we want to filter, pointing it at the custom filter we just created. It is important that the Data Range contains data in consecutive rows (no blank rows in-between) with headings in the first row of the dataset.

In the Topic Example Workbook above, we have the dataset which we want to filter on the first worksheet and on the second sheet in Column A we have the list of items (Item numbers) which we want to filter for.

In Column B of the second sheet we will create our custom filter for the Item no. The heading for this column (in cell B1) needs to be exactly the same as that which we have in our dataset – in this case ‘Item no’. If we wanted to filter our dataset for more than one column simultaneously then we can have multiple columns in this Custom Filter, each with the headings matching up to the dataset.

Where we have the filter in Column B of the second worksheet, then each row in Column B then represents an ‘Or’ condition. So for example if we have ‘=Item 2’ in cell B2 and ‘=Item 4’ in cell B3 and we use the range B1:B3 as our filter range, then the criteria we will be filtering for will be ‘Item 2 or Item 4’

In this case, we want our filter to be in cells B2 to B14 which will populate by referring to the corresponding item numbers in column A. We do this by linking to the cell in Column A but preceding our link with an operator – in this case an = operator to indicate that we want to filter for values in the dataset equal to the value in Column A of our Custom Filter. (We could also use operators such as > for greater than, < for less than). We need to include our operator in speech marks so it becomes “=” and then combine it with the cell reference by using an Ampersand character (&). So the formula we need in cell B2 is ="="&A2. The first equals sign is that which we use to begin any formula in Excel, the second (in speech marks) is the literal text string operator which we want to apply. As we drag this formula down the values in Column B now read ‘=Item 2’, ‘=Item 4’ and so on. These values (including the heading) are now what we will use as the custom filter.

Back in the ‘Data to Filter’ tab, we now want to apply this custom filter. To do this we click on the ‘Advanced’ button in the ‘Data’ tab, as per the screen shot below.

This brings up the Advanced Filter dialog box as per below:

Here apply the following settings:

• Keep the ‘Filter the list, in-place’ Action selected
• Our List range has automatically been selected as the data range we were in (if this is incorrect you can delete the cell range text in ‘List Range’ and select the right one yourself-ensure the headings are included).
• Criteria range: This is what we set up above – Cells B1 to B14 on the ‘Custom Filter’ tab,
• Leave ‘Unique Records Only’ unchecked.

Click OK and our dataset should now be filtered for the values we have in the ‘Custom Filter’ tab.

In the Topic Example Workbook try creating the custom filter as outlined above

A demonstration is shown in the video clip below. Resist the temptation to view the answers file below until you have tried this!

Once you have completed the ‘Try it Out’ exercise, the completed Excel file should look like this:

If the file does not display correctly try right clicking in the area above and selecting ‘Refresh’. If you are still unable to view the file properly, or it contains unsupported features, click here to download a copy.

How do I filter on multiple columns at once using different sets of criteria?

• In the Custom Filter, each row represents an ‘OR’ condition and each column represents an ‘AND’ condition (with the column heading being the same as that which the filter is to be applied on).

How do I copy and paste values in a filtered dataset?

• Either highlight the cells, copy them and then Paste Special Values, or only highlight the visible cells by using Ctrl + G (Go To) -> Special -> Visible Cells (or adding the ‘ highlight visible cells’ feature to the Quick Access Toolbar)
• If you have highlighted all the visible cells, to populate a value or formula into all of those cells only then type the value/formula and press Ctrl + Enter (rather than just Enter)

How do I sum only the visible cells in a filtered dataset?

• Use the SUBTOTAL function instead of SUM