In our previous post, you might have gone through filtering in excel. The Excel filter function has provided you with various options for different data types. These filter options are for text, number, and dates. The Excel filter is not for all, you need to use an advanced filter tool to configure the criteria based on your need. For Excel compare two columns, you need to use some complex criteria that are Excel advanced filter to find matched values and differences.
How to use Excel Advanced Filter?
The Advanced filter in excel is not the same as an auto filter. Here you can check how to use an advanced filter in excel.
Organize your database
To get the result, you need to arrange your database. Make sure there will be no space or blank rows available within the data set.
Set-up criteria range
Mention your conditions and criteria on your worksheet that can reside anywhere in the worksheet. You can reside these criteria on your sheet with headings on which you can apply the filter. If you mention the criteria in the same row then you need to apply AND whereas for different rows, you need to apply OR logical conditions.
Suggested Read: Excel Games
Use Advanced Filter
- Choose the cell on which you want to apply an advanced filter.
- Go to the Data tab.
- In Sort and filter group, you will find an advanced filter icon.
Advanced Filter Dialog Box
- In action, you need to choose either Filter the list in place or Copy the results to another location. If you want to hide the rows that are not matching the criteria then choose Filter the list option whereas select the upper left cell range.
- In List range, you need to mention the cell reference and the range of the cell that you want to filter.
- In the criteria range, you need to mention the criteria.
Excel Advanced Filter Multiple Criteria
- For more complex conditions, you need to create more excel functions like,
- The formula which you are using must evaluate either TRUE or FALSE.
- For the criteria range, it must have 2 minimum cells like formula and heading cell.
- If you want to evaluate the formula for each row of list range you need to use relative reference whereas, for a specific cell, you need to use an absolute reference.
- If your criteria have one formula then you must include 2 cells in that criteria range that is formula cell and heading cell.
Use of Wildcard Characters in Advanced Filter Criteria
For doing partial match to filter text records, you need to use wildcard characters in Excel Advanced filter formula.
- To match a single character, you need to use questions mark (?).
- For the sequence of characters, you need to use the asterisk sign (*).
- To filter cells that contain a question mark, asterisk, you need to use the tilde (~).
Excel Advanced Filter Copy to another Sheet
To perform excel advanced filter copy for another worksheet containing data, you need to choose “Copy to another location” option. For example, you have your table in Sheet 1 and now you want the filtered data in sheet 2 then you need to follow these below-mentioned process.
- In sheet 1, you need to set up a criteria range.
- Now select an empty cell in sheet 2.
- Go to the Data tab and look for the advanced filter.
- In an advanced filter dialog box, you will get three options
- For action, select copy to another location whereas for list range box, switch to sheet 1 and choose the table that you want to filter. For the criteria range, switch to sheet1, and choose criteria range as discussed above. In copy to box, you need to select the destination range of upper left cell of sheet 2.
- Click ok.