More complicated filtering may be done with the Excel Advanced Filter than with the Excel Autofilter.
The Advanced filter may be used to filter a data collection based on user-defined criteria and can be applied to many columns of data at the same time. Rather of using a drop-down menu, these criteria are set on the same spreadsheet as the range to be filtered.
A List_range and a Criteria_range must be specified in order to conduct an advanced Excel filter. Both of these ranges define cell ranges in your working spreadsheet.
Range | Discription |
List_range | You must specify the range of cells you want to filter. Headers at the top of each column should be included in this range. |
Criteria_range | The filtering criteria are supplied in a range of cells (usually located above or below the list range) |
Shortcut : ALT + A + Q
Extracting a Unique list
The steps of extracting a unique list are as follows:
- Select the entire source data (data set).
- Data tab –> Sort & Filter –> Advanced.
This will open the Advanced Filter dialog box. (You can also use the keyboard shortcut – Alt + A + Q).
- In the advanced dialog box
- Action: Select the ‘Copy to another location’ option.
- List Range: Make sure it corresponds to the dataset you're looking for unique records in. Also, ensure sure the data set's headers are included.
- Criteria Range: This should be left blank.
- Copy To: Choose the cell address where you want the list of unique records to be sent.
- Unique Records Only: check this option.
- Ok
Using Criteria
Excel Advanced Filter may be used to filter records based on a set of criteria.
- Specify the criteria for which you want to filter the data.
- Select the entire source data (data set).
- Data tab –> Sort & Filter –> Advanced.
This will open the Advanced Filter dialog box. (You can also use the keyboard shortcut – Alt + A + Q).
- In the advanced dialog box
- Action: Select the ‘Copy to another location’ option.
- List Range: Make sure it corresponds to the dataset you're looking for unique records in. Also, ensure sure the data set's headers are included.
- Criteria Range: Specify the criteria we established in the previous steps.
- Copy To: Choose the cell address where you want the list of unique records to be sent.
- Unique Records Only: check this option.
- Ok
Excel's Advanced filter allows you to build a wide range of criterion combinations.
- AND Criteria
You must provide AND criteria below the header if you wish to utilise them.
- OR Criteria
You must put the criterion in the same column if you wish to utilise OR criteria.
Criteria listed on the same row are linked by the "AND" operator and criteria listed on different rows are linked by the "OR" operator.
Using WILDCARD characters
When creating criteria in Excel Advanced Filter, you may also use wildcard characters.
wildcard | description |
* (asterisk) | It represents any number of characters |
? | It represents one single character |
~ | It is used to identify wildcard in the text |
For *
- Select the cells you wish to filter.
- Select Data –> Sort and Filter –> Filter (Control + Shift + L on the keyboard).
- In the header cell, click the filter icon.
- In the field (below the Text Filter option),type *a
- Ok
For ?
- Select the cells you wish to filter.
- Select Data –> Sort and Filter –> Filter (Control + Shift + L on the keyboard).
- In the header cell, click the filter icon.
- In the field (below the Text Filter option),type ?????
- Ok
For ~
- Select the cells you wish to filter.
- Select Data –> Sort and Filter –> Filter (Control + Shift + L on the keyboard).
- In the header cell, click the filter icon.
- In the field (below the Text Filter option),type
- Ok
NOTE :
- Remember, the headers in the criteria should be exactly the same as that in the data set.
- Advanced filtering cannot be undone when copied to other locations.
- The criteria range can be on the same sheet as the data, or on a different sheet.
When copying the filtered date from sheet 1 to sheet 2, keep the sheet 2 active.
Acknowledgement
This blog simply represents my own ideas and opinions (based on my limited knowledge) and should not be used as a replacement for legitimate sources. I'd welcome corrections in the comments if I ever make a mistake or if you disagree!
End note
These were the things that I was able to explore today, and I am looking forward to learn more. If you have any further information on the aforementioned concepts, please share it in the comments section below. Don't forget to connect me on Twitter and Github.
Have a great time !