When we talk about the filters in Excel, many people know that it can quickly find information or data that fits certain criteria in the clutter.
In addition to the filter, there is a feature called an advanced filter. Someone may not have used it, but what does this advanced filter do? In our today’s tutorial, we are going to learn how to use it. Let’s get started.
No.1 Filter The Data That Meets The Criteria
If we want to get the sale and product info of customer Jack, we first select any cell and go to the data tab, and choose Advanced filter.
There will be a box of Advanced Filter. Fill the List range, and Criteria range and Copy to one by one. Remember to check the Copy to another location first. Then, hit ok.
Ok, we made it.
No.2 Filter Part of the Data That Meets The Criteria
If you think that “Jack” appears too many times in the first result, we can also make it not show. But we need to copy the table header first.
Select any product and go to the Data tab to choose Advanced Filter, check the copy to another location, then fill the Criteria range and Copy to.
Hit Yes.
Ok, we’ve done it.
No.3 Filter Data That Meets Both Criteria
If we want to know how many Jams Jack buys, we need to filter out conditions that meet both of these conditions.
Select any Jams cell and go do the same as before.
No.4 Filters Data That Meet Only One Of Two Criteria
If we need to filter the data of customer Arianna or the sale number is over 500.
Select any cell and go to Advanced Tab to open the Advanced Filter box. And we also do the same as before, fill the Criteria range and Copy to the range.
Now, we get the result. We get all the info of customer Arianna and all the lines of sale numbers are over 500.
No.5 Advanced Filtering For Precise Matching
Advanced filtering uses fuzzy matching by default, and anything that contains a keyword is considered qualified. As shown in the figure below, to extract the record of the commodity category “Phone” :
But if we only want to get the info of Phone, not phone case or phone charger or phone cable. It’s quite easy. Just type = “=Phone” to replace the original “Phone”
Still do the same in the Advanced Filter box, and hit OK.
Now, we get the info of only about “Phone”.
No.6 Take The Formula As The Filter Condition
If we want to get the sale number is more than average. We need to first type =D2>AVERAGE(D2:D14) in cell F2 to check whether 354 is more than average or not. Remember to use relative reference here. (You can just press the F4 shortcut key)
Then we start to use the advanced filter. Pay attention that there is no title table in cell F1.
You can count the average number of sale to check the result.
No.7 Put The Filter Results Into A New Worksheet
If you just click the copy to range in another sheet, there will be a pop-up window showing You can only filter data to the active sheet. In fact, we only need to change the way of thinking, this problem will become easy to solve—— that’s just do the Advanced Filter in the new sheet directly.
OK, That’s all for today’s tutorial. Hope these seven tips about how to use advanced filter can be helpful to you, see you next time.