Advanced Filtering Methods – what are the ways in which you can use it?
This 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 and advanced Excel 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
- Create filter criteria as on the range on right hand side in cells T1:U2
- Data Tab, Advanced Filtering button
This shows a filtered list but for all fields
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.
Which gives the following results
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.