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:

1. What is Excel Advanced Filter?
2. Difference between AutoFilter and Advanced Filter
3. Create an advanced filter in Excel
   • Criteria range for advanced filter
   • Criteria for number and date values
   • Criteria for text values
   • Advanced Filter options with wildcard
   • Advanced Filter criteria and Formulas
4. Using AND and OR logic gates using Advanced Filter
5. Extract some specific columns
6. Copy the filtered data to another worksheet

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

1. Always add a heading to your row. Therefore, each of your column will be having it unique header as repeated headers creates confusion to Advanced Filter.
2. Advance filter won't work well with blank rows. Always make sure there are no blank rows within the selected data range.

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:

Region: North

Total: > 20000

3. Apply Excel Advanced Filter

Since we have created our criteria range, applying an advanced filter is next. Following are the steps:

• Select any cell from your data range.
• From the excel ribbon, go to the Data tab, select the Sort & Filter group, and click on Advanced from the dialogue window.

• The filter dialogue window will be displayed as soon as you click the above option. Now we have to set up and configure the filter parameters.

4. Configure the Filter parameters

In the Advance Filter dialog box, we will put the following values in the parameters:

1. Action: This parameter will specify whether we want to filter the selected data in their respective place or copy the output and place it in another location.
   • If you select the "Filter the list in place" option, it will hide the rows that doesn't not fit in your criteria.
   • Selecting "Copy the results to another location" option, will direct you to select the upper-left cell of the range where you can copy and paste the filtered rows. Always select blank cells in the destination range else all cells below the copied range will be cleared.
2. List range: It represents the range of cells to be filtered, and the column headings should be included.
   • If you have already selected the data range before clicking the Advanced option, Excel will automatically pick the range and fill the list for you.
   • If you are not satisfied with the listed range, click on the Collapse Dialog icon which will redirect you to the worksheet from where you can select the preferred range using the cursor.
   
3. Criteria range: In this filed, we will supply the range of cells where we have the specified the criteria in our worksheet.
   • In our case, we are filtering the list in place, click on the Collapse Dialog icon which will redirect you to the worksheet from where you can select the criteria range using the cursor.
   
4. Unique Records: If you only want unique data, click on the Unique records only checkbox placed on the left corner the Advanced Filter dialog window. It will filter only unique data entries.

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:

1. Again, we will set up a criteria range at the top rows on worksheet1.

   Criteria Range
   Region	Total Sales
   North	>=10000

2. Manually go to sheet3 and, using the cursor select any blank cell. Make sure it contains no text. Else it will be overwritten.
3. From the excel ribbon, go to the Data tab, select the Sort & Filter group, and click on Advanced from the dialogue window.
4. The filter dialogue window will be displayed; select the following options:
   • For the Action field, you will find two options. Choose the option Copy to another location.
   • The following field is List Range; click on the Collapse Dialog icon, which will redirect you to sheet2, switch to sheet one and select the range you want to filter using the cursor.
   • In the Criteria range box, select the criteria you want to apply.
   • The last step is to click on the Copy to box, and select the cell range in sheet2 where you wish to place your data.
   • If you want unique data, click on the Unique records only checkbox in the left corner of the Advanced Filter dialogue window.
   • Finally, click OK,

5. You will get the following filter output.

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.

Comparison operators

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:

Region: North

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:

Product: ="=Apple"

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:

Product: Green

Sub-total: >10000

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

• Question mark (?): This character matches the specified content with any single character.
• Asterisk (*): This wildcard character matches any sequence of characters.
• Tilde (~) followed by *, ?, or ~ : This character is used to filter text that contains a ? (question mark), * (asterisk), or tilde (~).

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:

Product:* Apple*

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:

• Criteria applied on the same row are linked using an AND operator.
• Criteria applied on different rows are linked using an OR operator.

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: