How To Create And Use Advanced Filter Excel

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. 

Need help with filtering the data on your Excel files? We are here to provide you with top-notch Excel services at your office.

 

Excel Advanced Filter Simple Example 

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 OK to 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. 

 

Bottom Line 

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.

 

FAQ