Besides the normal filter, there is an advanced filter in Excel. What does the advanced filter do? The advanced Filter allows you to filter your data by criteria combinations, which you write into another table. This allows you to do filtering by using more complex criteria, for example, any red VW Golf or silver Audi A3.
How to use the advanced filter in Excel
There are basically five steps for applying advanced filters in Excel (the number are corresponding with the picture above):
- Create a criteria table: It has the same columns as your data table, but instead of your data it contains only your criteria. If you leave cells blank (like cells D12 and D13 in the above example), they won’t be regarded as a criterion.
- Click on “Advanced” on the Data ribbon.
- Select the data you want to be filtered as the “List range”.
- Select your filter criteria range as the “Criteria range”.
- Confirm by clicking “OK”.
Now, only the green framed cells will be shown. The rows in-between are hidden, as they do not match the criteria.
