How To Use Advanced Filters In Microsoft Excel

Date:2022-8-20 Author:Sandra

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.

How To Use Advanced Filters In Microsoft Excel

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.

How To Use Advanced Filters In Microsoft Excel
How To Use Advanced Filters In Microsoft Excel

Ok, we made it.

How To Use Advanced Filters In Microsoft Excel

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.

How To Use Advanced Filters In Microsoft Excel

Hit Yes.

How To Use Advanced Filters In Microsoft Excel

Ok, we’ve done it.

How To Use Advanced Filters In Microsoft Excel

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.

How To Use Advanced Filters In Microsoft Excel
How To Use Advanced Filters In Microsoft Excel

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.

How To Use Advanced Filters In Microsoft Excel

Now, we get the result.  We get all the info of customer Arianna and all the lines of sale numbers are over 500.

How To Use Advanced Filters In Microsoft Excel

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” :

How To Use Advanced Filters In Microsoft Excel
How To Use Advanced Filters In Microsoft Excel

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”

How To Use Advanced Filters In Microsoft Excel

Still do the same in the Advanced Filter box, and hit OK.

How To Use Advanced Filters In Microsoft Excel

Now, we get the info of only about “Phone”.

How To Use Advanced Filters In Microsoft Excel

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)

How To Use Advanced Filters In Microsoft Excel
How To Use Advanced Filters In Microsoft Excel

Then we start to use the advanced filter. Pay attention that there is no title table in cell F1.

How To Use Advanced Filters In Microsoft Excel

You can count the average number of sale to check the result.

How To Use Advanced Filters In Microsoft Excel

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.

How To Use Advanced Filters In Microsoft Excel
How To Use Advanced Filters In Microsoft Excel
How To Use Advanced Filters In Microsoft Excel

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.

Copyright Statement: Regarding all of the posts by this website, any copy or use shall get the written permission or authorization from Myofficetricks.

One response to “How To Use Advanced Filters In Microsoft Excel”

  1. I gotta favorite this site it seems very useful extremely helpful

Leave a Reply

Your email address will not be published. Required fields are marked *