Advanced Filtering Methods – how do you use it?

Advanced Filtering Methods – what are the ways in which you can use it?

Advanced Filtering Methods: Excel iconThis week’s hint and tip is on advanced filtering methods in Excel. Filtering is a very useful feature in Excel but ‘simple’ filtering can sometimes not do quite what you want. This is where advanced filtering comes in to place. Advanced filtering is covered in our Intermediate Excel and Advanced Excel training courses but we also decided to do a hint and tip on it. We are going to go through it now below.

 

What is Advanced Filtering?

Filtering is a feature in Excel that is used by many for changing how their data looks. It is very good for temporarily removing unwanted information so that you can analyse and compare information easily. Sometimes though standard filtering can’t show you what you want and this is where advanced filtering comes in.

Advanced filtering is particularly good when your filtering covers AND or OR criteria. To show you two ways in which you can use advanced filtering, we shall use the attached worksheet as an example. You can download the spreadsheet in the video by clicking here (clicking here will download a copy to your computer so you can try it out!).

 

Method 1 – show all fields

Steps:

  1. Create filter criteria as on the range on right hand side in cells T1:U2
  2. Data Tab, Advanced Filtering button

Advanced Filtering methods: screenshot of advanced filtering window

This shows a filtered list but for all fields

Advanced Filtering methods: screenshot of method 1 advanced filter results

 

Method 2 – show just 3 fields

In this example the subtle change is that we just want to see filtered data for 3 fields on the left hand side but we’ve already created headings on the left hand side in cells A1:C1.

Advanced Filtering methods: screenshot of advanced filtering window for method 2

Which gives the following results

Advanced Filtering methods: screenshot of results from method 2 of advanced filtering

 

The video below goes through two ways in which you can use advanced filtering that you can see in the spreadsheet above. We hope that you find the video useful and enjoy learning about it!

Take a look below at the video to find out more!

We hope you liked this hint and tip on advanced filtering methods, why not take a look at our previous one on a nested XLOOKUP function with MIN?

For more blogs and videos please visit our website here.