Excel Advanced Filter - Though the name suggest Advanced, but it is not that complex. In the contrary, it is simple and once you spend few minutes, you can learn most of it
Well, then let's begin.
Before we talk about the criteria or filter logic, let us spend a 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, alway 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 are the filtering conditions to filter the data based on each column. Criteria is written in a different place than the data itself. The criterias can in the same sheet or another sheet. It can even be on a different workbook (different excel file). We decide whether we want a OR condition or AND 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
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
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
Criteria Operators are used for Numeric, currency or date type of data
|>=||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
- 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