How To Create And Use Advanced Filter Excel (with example)
Advanced Excel Filters
Excel has many tools that help you do complex work with only a few clicks. Filtering is one of them, which has different options for basic and advanced criteria. As the name indicates, the Filter option helps you find data on your Excel sheet that meets your conditions. This post explains how to use Advanced Filter Excel in different ways.
How does Excel Advanced Filter work?
You may have a large data table and want to find something on the list based on two or more complex criteria. Advanced Filter Excel allows you to filter the list based on any condition. Suppose, for instance, you require information regarding the specific product that recorded a sales volume of 10 units on a particular date. Here, you have two criteria: the items sold and the date. You can add more options to the criteria, such as colors. Therefore, the filtering shows you a list of products with the specified color sold in 10 units on a specific date.
You should note that the Advanced Filter acts like the AND function. It means it is required for all the conditions to be True. If one of them is not true, no result will be shown.
Let’s see what you can do with this advanced tool in Excel.
Assume we have a list of our sold products and want to filter the list based on the names starting with an “S” Although we still have one criterion, this condition is not applicable to Autofilter. However, we can filter the records based on this condition using Advanced Filter. Here is what we can do:
Go to the Data tab.
Find the Sort & Filter section.
Click on Advanced.
A dialogue box will open in which you should enter the criteria you want. First, select the range of the list you want to filter. In our example, we can only select the column that contains product names.
Then, select the criteria range. We can only choose the Product Name column because there is just one criterion to use here.
Since we want to filter the product names that start with “S,” we enter “S*.” The asterisk after “S” indicates that we don’t care about the rest of the word. Only the first character is essential. You can do the same with the words that end with an “S.” In this case, the star will be placed before “S” to show that the last character is important.
Click OK to see the result. You should see a list of products whose names start with “S.”
As you can see, we have an identical name (Skirt). If you don’t want to see duplicate data on your result, mark “Unique records only” on the Advanced Filter dialogue box. Then, you will see the following result.
Advanced Filter Excel With Multiple Criteria
What else can you do with Advanced Filter Excel? One critical thing that makes a huge difference between the simple Filter in Excel and Advanced Filter is the ability to filter the data based on multiple criteria. Suppose we want to see the list of items with Blue color sold on December 23rd. In this case, we should select the whole table for filtering. After that, we must select the criteria range according to the following picture.
Click OKto see the following result.
When you write the criteria on the same row, the Excel filtering option will consider both conditions to be true. It means it searches only for products with Blue color sold on the 23rd. It will not show any other blue products sold on other dates. To make the filtering option behave like an OR function, you must place the second criterion in a separate row, as shown below:
Figure 6- Advanced Filter can act like an OR function.
What is the difference? If you write the criteria in different rows, you will have a list of blue products sold on the 23rd. Therefore, it is possible to see other colors or dates on the list as well.
Advanced Filtering With More Complex Criteria Range
You can add more complex criteria to your criteria range. In the previous examples, we have always filtered the records based on the data equal to our criteria. In this step, we want to filter the data based on our criteria by specifying whether we want data greater than or less than the specific value. Let’s see how we can filter the records based on Blue products sold more than ten units. We only need to set the criteria to >10. The same can happen to the dates or other values.
Copy The Result To Another Location
If you don’t want to change the main table, you can select “Copy to another location” while using Advanced Filter Excel. In this case, you should choose a cell where you want to see the result. Therefore, the table will not change and you can see the result elsewhere. . You should note that you can only copy the result to the active sheet.
Why Does Advanced Filter Excel Not Work?
You may face one of the following problems:
Advanced Filter is inactive, and you can’t work with it.
You select a criteria range, but the result is incorrect, and it only creates a list based on one of the conditions.
You should note that Advanced Filter is available on all versions of Excel, from 2003 to Excel 365. Therefore, you will not face either of the problems because of your Excel version. Here is one you should consider to ensure you can work with Advanced Filter correctly:
Ensure that the column headers in the data section match those in the criteria section. If your column headings are spelled differently (even with one more space) than the criteria headings, the advanced filter doesn’t work.
Make sure you don’t have any cells merged. In this case, Advanced Filter will not work.
The Advanced Filter will not work on shared workbooks. This option will turn gray in this case.
Excel is more than just creating tables or calculating numbers. You can work with large databases on this application. Advanced Filter is one of the helpful options in Excel that can help you find data based on complex criteria. You will not have to check each row one by one. This filtering option gives you the result with a few clicks. If you become an expert in Excel, you will find better jobs too. There are also some Excel skills that employers are looking for when hiring new employees. Everything gets easier in Excel.
How to clear Excel’s advanced filter?
If you want to clear the filtering and reset the table to its main form, click on Clear in the Sort & Filter section under the Data tab. You should also note that this option is only available when you have filtered the records on the main table. If you copy the result into another cell, this option will not be active.
What is filter vs. advanced filter in Excel?
Generally, two filtering options are available in Excel: The Filter option (Autofilter) and the Advanced Filter. The Filter option can only sort the list based on one value you select from the drop-down list. In the result, you will see the records containing that criterion. But, when you work with Advanced Filter, you can create a criteria range. It means you can filter the list based on two or more criteria. As the name indicates, it is more advanced than the simple Filter.
What is the criteria range in the advanced filter?
To use an advanced filter in Excel, you need to set up your criteria range first. You must specify the conditions under which your database will be filtered. The criteria range is that condition.
How do I use Advanced Filter for only specific columns in Excel?
If the columns are placed next to each other, you can only pick those columns while setting the advanced filter. But, if they are apart, it is better to select the whole table.
How to create an advanced filter in Excel?
You can select the entire data set and open the Advanced Filter on the Data tab. In the Advanced Filter dialog box, you can specify the criteria for which you want to filter the data.
How to add an advanced filter in Excel?
Advanced Filter is placed under the Data tab in the Sort & Filter section. Click on it, select the range of databases you want to filter, then pick the criteria range and click OK.
Our experts will be glad to help you, If this article didn't answer your questions.
We believe this content can enhance our services. Yet, it's awaiting comprehensive review. Your suggestions for improvement are invaluable. Kindly report any issue or suggestion using the "Report an issue" button below. We value your input.
Contact us today at – and speak with our specialist.
BSUPERIOR SYSTEM LTD. provides custom software and technology solutions to small and medium sized businesses. We have been helping businesses to operate more efficiently, reduce money and time spend on redundant repetitive daily tasks.