Refine your filter options with the Advanced Filter in Excel

For most filtering of data Excel AutoFilter is perfectly adequate. Here we’ll look at some benefits for moving beyond the AutoFilter by using the advanced filter in Excel.

A Recap of AutoFilter Benefits

For ease of use, it’s hard to beat the Excel AutoFilter feature. Just click the Filter command on the Excel Ribbon’s Data tab, and the filter is ready to go.

You can click the drop down arrows in the column headings, and use the check boxes to select the items you want in the filter. Or, use the Text, Date or Number Filters commands, for variations on the filters.

Another quick way to filter is to right-click on a cell in the list, then click Filter, and click Filter by Selected Cell’s Value.

Benefits of using Advanced Filter in Excel

The Advanced Filter in Excel isn’t quite as easy to use but it does have some benefits that make it worth the effort.

With the Advanced Filter, you can apply multiple filter criteria to the whole database whereas with the other types of filters, you have to filter in a step-wise way, which means that when you have applied one type of filter to the data, there is less data available to run a second and third filter.

After you click the Advanced command on the Excel Ribbon’s Data tab, the Advanced Filter dialogue box opens. Fill in the details, and then click the OK button to filter the data.

Filter Data to Another Sheet

Another reason to use an Advanced Filter, especially when working in Excel, is that it’s a quick and efficient way to copy specific data to a different worksheet.

In the screen shot above, the option is selected to copy the filtered data to another location. You can use that option to quickly create a report for each department, or salesperson, and email the results.

If you select “Filter the list, in-place”, the results will be shown in the database.

Tip: If you wish to remove the filters, you need to click the Clear button in the Data ribbon because the dropdown filter arrows disappear when you apply advanced filters.

Create a List of Unique Items

Excel 2007 introduced the Remove Duplicates feature, but you can still use an Advanced Filter to create lists of unique items. The Remove Duplicates feature strips the duplicates out of the list that is selected, so you have to remember to work on a copy of the list, if you want to keep the original list intact.

With an Advanced Filter, no items are removed from the list, so you don’t risk losing any of your original data.

Complex Filters

With an Advanced Filter, you can create a criteria range on the worksheet, and see at a glance what the filter settings are. You can also create complex filters with AND/OR settings that go beyond what an AutoFilter can do.

For example, with an AutoFilter, you can select two specific customers, and two products for those selected customers.

With an Advanced Filter, you can create OR conditions between columns, such as Customer A OR Product B — you can’t do that in an AutoFilter!

How to create a criteria table for the Advanced Filter

1. Paste a copy of your data table’s header row into an empty section of your spreadsheet or into another blank sheet.

2. Then type in your search criteria below the appropriate headers. If you type criteria next to each other, as in the example below, it performs an AND filter, meaning it will show results meeting ALL requirements.

The criteria above will show people whose surnames start with C AND who work in Development. It will also show people whose surnames start with F AND who work in Production.

If you type criteria on different rows, as in the example below, the criteria will perform an OR filter, meaning it will show results which meet ANY requirement.

Wildcard searches

? (Question mark): Any single character.
For example, “Bl?ck” finds either “Black” or “Block”

* (asterisk): Any number of characters.
For example North* finds “North”, “Northeast”,
and “Northwest”.

*west finds “West”, “Northwest”, Southwest”, etc.

*a* finds the “a” anywhere in the text

Additional resources

The difference between Autofilter and Sort in Excel

Using filters in Excel

Using VBA to filter data

Filter by using advanced criteria

What is the difference between Autofilter and Sort in Excel? Why use one and not the other?

I found out the difference when I was trying to sort through an enormous worksheet and a colleague asked me why I wasn’t using the Autofilter. I didn’t really want to admit that I didn’t know what it was…so I resorted to shrugging my shoulders and hoping that he would show me how it worked…which he did. And so I share this little tip as it has saved me oodles of time.

When I use Sort to sort data in Excel, it sorts the entire table – which makes sense but might not be the best way forward. Sort is great for arranging a client list alphabetically for example, or sorting from lowest value to the highest value – but it may not be the best method as data can still be buried among the other rows and columns of detail.

Autofilter gives me the option to view the bits of data I actually want, and filter out the ones I don’t. So I can spot the trends, or analyse costs, or spot duplicate entries etc. Now, I really like this. It means I can easily find the data I need (surrounded by white space which helps me read it all more clearly).

To filter data, I select the field name for the data I want to filter. Go to the Home tab on the Ribbon and select the Sort and Filter button, and select Filter. Excel will prompt the next stage by asking what you want to use to filter your data, and tick the ones you want or add a new filter (such as a text filter). Excel then filters the data for you. Easy peasy.

Sort-and-filter-microsoft-excel-training-courses — Sort and Filter, hidden on the Home tab…but so useful

This can be really helpful if you want to find records for a department’s expenditure (and filter out the information on other departments). It’s really quick, so you can answer a colleague’s question on a key piece of data and then return to your normal worksheet view. When you want to return to the view of all your records, click on the Sort and Filter button again and then choose Clear.

It may appear a simple difference, but so worthwhile to know, because you can use Sort or Autofilter to meet your needs. It may not be a question on QI, but it is undeniably useful. They are just another little set of tools that you can use in Excel for time-saving at work. Check out what more you can learn to boost your skills with Microsoft Excel training courses https://www.stl-training.co.uk/microsoft/excel-training-london.php

Tag: Autofilter and Sort in Excel

Using the Advanced Filter in Excel