How to use advanced filter in excel

Advanced Filter in excel is applied onto some data in excel sheet. Criteria or filtering conditions are applied on the data. We need to understand both data and criteria before we apply filter.

Data for Advanced Filter in Excel:

Before we talk about the criteria or filter logic, let us spend few minute on data sheet. The data must have a name for each column on the top-most row. This also known as Header. The Header or name must be unique. These names or Headers are used for choosing the criteria. The name represents the type of data. This can be for example Name, DOB, Amount etc.

Remove any blank lines within the data. For all purposes, always avoid blank lines in excel. Sometimes, blank lines can be visually pleasant but causes of all kind of miscalculations in excel.

Here is an example of organized data

Criteria for Advanced Filter in Excel:

Criteria are the filtering conditions to filter the data based on each column. Criteria is written in a different place than the data itself. The criteria can be in the same sheet or in another sheet. It can even be on a different workbook (different excel file). Criteria can be either OR Condition or AND condition. let us understand the conditions and when to apply

OR Condition

In Simple term, if you want to show multiple specific values from same column, these are OR condition. For example, you want to choose Jan, Feb and Mar from Month column. Since these are different values from the same column, these are OR conditions

AND Condition

These are the conditions, which will be applied on the data filtered by the OR conditions. For example, Sales more than $50 in those three months - It would be written as

Month Jan and Sales more $50
Month Feb and Sales more $50
Month Mar and Sales more $50

Please refer to the picture below. These defines how to define the OR and AND conditions

If the Criterias are on the same row they joined by AND. So, when all of the criteria are matched, data will show up in filtered data
If the Criterias are on different rows, they are joined by OR. Meaning, Data that match each row criteria will show up (Data matching Row1 + Row2 + Row3 +.. all will show up.

Criteria starts with the column name. Then the contents that needs to be filtered. And Criteria are written side by side. OR conditions are written vertically one after one. Will show some example to explain it

Excel Advanced Filter Criteria Examples:

If we want to filter only based on Sales figures between $50 and $100, we will use tow Sales column with the following

Instead, if we want to see all data for Month Jan, Feb and Mar, we would use the following

Next, we want Sales in the Month Jan, Feb and Mar where the sales are between $50 and $100

Please notice that the sales lines are added to each of the month. That is how it works. This also gives flexibility that we can choose different criteria for January and different for the rest of the months

Let's further customize it. Let us look for Dry Fruit only for February. For rest of the months, it can be of any Fruit types. So in the the criteria, I do not put anything against Fruit Type in January and March. This will effectively create a logic to show up the rows of January and March, if the Sales value is between $50 and $100, irrespective of fruit types. But for February, the row shows up, only if the sales are between $50 and $100 and the Fruit Type is Dry

In the following example, All data for year 2018 + all data for Rob, irrespective of year will show up

In the following example, all Sales data of Rob for the year 2018 will show up

Excel Advance Filter Criteria Operators:

Criteria Operators are used for Numeric, currency or date type of data

Criteria Ooperator	Meaning
=	Equal to
>	Greater than
<	Less Than
>=	Greater than or equal
<=	Less than or equal
<>	Not equal to

You can also use some operator for texts. "*" is the most common of them

*day in criteria will match day, Sunday, Monday .. etc.

Sun* will match Sun as well as Sunday

How to apply Advanced Filter in Excel

Go to Data Tab and
Click on Advanced in the Sort & Filter Group
In the Advanced Filter pop-up window, click on the List range
Select the data range that you want to apply filter on
In the Advanced Filter pop-up window, click on Criteria range and select the range where you have mentioned the criteria

The steps are shown on the following picture:

After applying the above filter following is the result

Hope, this was helpful. Please stay tuned for other topics on Excel.

If you are unsure and not able to decide on what to use - Excel Auto Filter or Excel Advanced filter, then the following link will help you Excel Advanced Filter or AutoFilter