Excel Advance Filters
If you work with Excel, filters are one of the often used features that quickly helps you to segregate data sets based on a given selection, string, number or other criteria. But do you know Microsoft Excel offers advanced filter options that can completely change your Excel life? There are many scenarios when a standard AutoFilter can't accomplish what you want; the Advance filter works like a miracle. Sounds wonderful, right!
Therefore, this tutorial will discover some cool stuff you can do using the Excel advanced filter. Below given are the list of topics:
Let's get it started!
What is Excel Advanced Filter?
As the name suggests, "Excel Advanced Filter contains more extensive and developed features than the regular filter version. It is commonly used when users need to implement more intricate criteria to filter their data."
Advanced Filter is helpful when you want to find data that meets two or more complex criteria?for example, extracting data by matching and seeing the differences between different rows, filtering columns that match values present in another list, and catching exact matches, including case-sensitive and case insensitive, and many more.
Excel Advanced Filter was introduced with Excel 2003 version and since then has been available in all later versions, including Excel 2007, Excel 2010, Excel 2013, Excel 2106, Excel 2019 and Excel 365.
Regular Filter vs Advanced Filter
Excel already contains an Auto Filter properly that works pretty well and solves most of your problems. Many of you might wonder what was necessary to introduce Excel Advanced filter. Below given are the differences between the Auto Filter and Advanced filter:
How to create an advanced filter in Excel
Excel Advanced Filter is complex as it involves many advanced steps, but it definitely gives you more refined and customised results. Everything you want to create an advanced filter, make sure to perform the below-given steps in your worksheet:
1. Organize the selected data
Refer to the below image to get an idea of how our table looks like:
2. Create the criteria range
For Advance Filter, always create a separate range on the top of the worksheet. Though it is not mandatory, you can place the criteria range anywhere on the sheet. But it becomes more convenient and accessible if you put it at the top of the sheet and separate it from the primary data with one or more blank rows.
For example, to filter records for the North province whose total value is greater than 20000, we will set up the below-given criteria range:
Total: > 20000
3. Apply Excel Advanced Filter
Since we have created our criteria range, applying an advanced filter is next. Following are the steps:
4. Configure the Filter parameters
In the Advance Filter dialog box, we will put the following values in the parameters:
Finally, click OK, and you will get the following filter output where it will only show the entries whose region is north and total sales is greater than 20000:
Filter and Copy in Another column
In the above section, we learned how to apply the advanced filter tool in the worksheet containing our actual data. Now, what if we want to copy the filter data to another worksheet? You might think the easiest way is to choose the given "Copy to another location" option and select the range in another worksheet where we want to paste our data. But the irony of this method is it ends with an error stating: "You can only copy filtered data to the active sheet".
Excel enables an option to quickly copy the data to a different location. This way, you can keep both the data, the original one and the filtered data as well. Let's suppose, your data is kept in sheet1 and you want to copy the filtered data to sheet3 Following are the steps to filter and copy data in a different column:
Excel Advanced Filter criteria range
So far, we have created an advanced filter, and we came to know it is not rocket science. We can easily create a filter per our requirement in just a few steps. But knowing a little bit more can increase your filter options.
In the Advanced Filter criteria, you can compare different numeric values using the following comparison operators.
So far, we have already used the following criteria range where we have been using the >=10000 criteria to filter records that are greater than or equal to 10000.
Now, let's suppose you want to filter data of the North region for the month of January, where the total sales are greater than 3000. For this, we will create a criteria range using the following operators:
Order date: >=1-07-2022
Order date: <=31-07-2022
Total Sales: >3000
Excel will throw the following output:
Advanced filter for text
In the above section, we covered operators that helped apply criteria on numbers and dates. Apart from these, you can also use logical operators to compare text values. The following table contains all the rules:
Example 1. To filter text with exact match
Many times we only want to show those cells that contain values exactly matching with specific text or character, including uppercase, lowercase, equal sign or comma.
For example, if we only want to filter Apple products from the Excel list, we will use the below-given criteria: ="=Apple ". Once you press enter Microsoft will automatically display the criteria as =Apple in the criteria range, but you can have a look at the full formula in the formula bar:
As you will notice in the below image, the criteria ="=Apple" shows only the Apple records with Total sales greter than 3000, ignoring Green Apple and Pineapple. The criteria will be as follows:
Total Sales: >3000
Example 2. To Filter data that starts with a specific text
To filter all cells whose text starts with a specified value. We need to type that text in the criteria range without mentioning the equal symbol (=) or double quotes ("").
For instance, to filter all product that contain the text "green" with total sales greater than 10000, we will use the below given criteria:
Excel Advanced Filter with wildcards
So far, in this tutorial, we have learned how to filter an exact match. What if you want to filter the text records only with a partial match? Yes, it is also possible to use wildcard characters. Below given are the most common wildcard characters used in Excel
Apart from the above entries, you can also refer to the table below to understand a few more criteria range examples with wildcards.--
To fetch the produscts that contain the word apple we will use the following criteria:
Total Sales: >10000
Using Advanced Filter with AND vs. OR logic
Excel Advanced filter can work well with AND as well as OR logic to set up a more advanced criteria range and filter out more complex data:
Let's understand the above concepts using the following examples:
Excel Advanced Filter with AND logic
To create a filter with (total sales greater than equal to 10000) >=10000 AND Sales in Mar >=1000. Now you might be wondering whether we can apply both criteria together. Yes, by using AND logic we can use apply both the criteria together in an Excel worksheet.
Excel Advanced Filter with OR logic
To create a filter where the Total sales >=3000 OR Sales in Mar >=600, we will put each criteria on a separate row:
Excel Advanced Filter with both AND as well as OR logic together
To create a filter where the product is Apple with total sales greater than or equal to 30000 OR Average greater than or equal to 1000, we will apply the following the criteria range: