Advanced Filter in Excel

If you have been using Filter for a while, it is time now to upgrade to Advanced filter, which I think is more easy to use and offers a great utility than the usual filter in Excel..

Let’s take a dive into this

Consider this data..

What if we had to filter by the records by the following criteria.

• Sales Rep = Veronica
• Customer = MNTL

Sure we can do this we a filter but for learning sake let’s try advanced filter for this

 

Before we proceed, 2 Quick shortcuts about Filter (not advanced filter)

1. You can apply & remove filter by the shortcut Ctrl + Shift + L
2. and press the Alt key and down arrow together to get the drop down list from the filter

Related [Download the 25 recommended Excel Shortcuts]

 

Applying Advanced Filter on the data

The advanced filter is located in the DATA tab by the name ‘Advanced’ (Shortcut key – Alt A Q)

 

Before you click on Advanced Filter 

1. Make a separate place for writing the criteria on the sheet, let’s say on the top of our data.
2. Include the headers too while writing the filter criteria, with the help of headers advanced filter will identify the which criteria will apply to which column of the data.

 

Click the picture to enlarge

 

Applying the Advanced Filter

1. Go to the DATA tab and click on Advanced
2. Advanced Filter dialogue box pops up for 4 inputs
   • List Range --> This is your data on which you want to apply Advanced Filter. Select the entire data including the headers
   • Criteria Range --> These are conditions by which we want to filter the data. Select the criteria range (including the headers)
   • Click the Radio Button --> Copy to another location and click anywhere else on the sheet, where you want the filtered data to be copied
   • Unique Records --> If you are looking for only unique records then check the unique records button
3. And you are done! Yes it is that simple 😀

 

What if I want to extract the data on some other sheet? You can also paste the data on a new sheet (or some other sheet) simply by referencing it (in the copy to location) in the advanced filter dialogue box

 

Exploring Advanced Filter Further..

The steps that you have just learnt are all that you need to learn advanced filter. Let’s take a look at a few ways in which you can filter the data.

Note that the way you’ll apply advanced filter will be the same only the filter criteria will change

 

1. Filtered records for Veronica and MNTL and records for White Associates with Sales greater than >10000

 

2. Filtered records from 2-Jan-2005 till 31-Dec-2006 for Varsha

 

3. Filtered records between 2-Jan-2005 and 31-Dec-2006 for Customer MNTL and filtered records for Boston Consultants for Sales >10000 in the Region East

 

Partial Filter Result in Advanced Filter

Your data can have many columns but not always when you apply a filter, you want to work will all the columns. In that case you can also retrieve partial filter results.

For instance I want to apply the following filter

• Sales Rep = Swati
• Region East = East

And the filtered records should only come for Date and Sales

1. For that just write the headers Date and Sales in the Copy to Location (on the sheet)
2. Select those headers only in the advanced filter (copy to location) option
3. The records will only populate the Date and Sales for Swati and East

 

Let’s take a look into a some Pros & Cons

• Flexibility – It is really flexible in terms of defining criteria and advanced filter really becomes handy when the criteria becomes complex and lengthy. It also gives flexibility to extract a subset of the entire data (depending on the criteria)
• Hassle Free – Unlike the regular filter where you have to filter, copy and then manually take the data to a new location, advanced filter automatically pastes to a new location
• Redundancy of Operations – The only thing which is a bit annoying is that you have to do the advanced filter operation every-time you want to apply a filter, its is not automatic! But this can be fixed with a short macro! Coming Up Next 😎

 

Do you use Advanced Filter ?

I don’t use this technique much but prefer it over the regular filter and love it for its flexibility. How often and in what ways do you use advanced filter?

 

Further Advanced Readings (Highly Recommended)

1. Automate Advanced Filter with a Macro
2. Work with Slicers rather than using Filters
3. Make a Dashboard in 15 mins using Slicers – Case Study