Advanced Excel Filter - Unique/Criteria/Wildcards

Advanced Excel Filter - Unique/Criteria/Wildcards

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.

Screenshot (119).png 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.

RangeDiscription
List_rangeYou must specify the range of cells you want to filter. Headers at the top of each column should be included in this range.
Criteria_rangeThe 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:

  1. Select the entire source data (data set). Screenshot (121).png
  2. Data tab –> Sort & Filter –> Advanced.

    This will open the Advanced Filter dialog box. (You can also use the keyboard shortcut – Alt + A + Q).

  3. In the advanced dialog box Screenshot (122).png
    • 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 Screenshot (123).png

Using Criteria

Excel Advanced Filter may be used to filter records based on a set of criteria.

  1. Specify the criteria for which you want to filter the data.
  2. Select the entire source data (data set). Screenshot (124).png
  3. Data tab –> Sort & Filter –> Advanced.

    This will open the Advanced Filter dialog box. (You can also use the keyboard shortcut – Alt + A + Q).

  4. In the advanced dialog box Screenshot (125).png
    • 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

Screenshot (126).png Excel's Advanced filter allows you to build a wide range of criterion combinations.

  • AND Criteria

Screenshot (127).png You must provide AND criteria below the header if you wish to utilise them.

  • OR Criteria

Screenshot (128).png 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.

wildcarddescription
* (asterisk)It represents any number of characters
?It represents one single character
~It is used to identify wildcard in the text

For *

  1. Select the cells you wish to filter. Screenshot (129).png
  2. Select Data –> Sort and Filter –> Filter (Control + Shift + L on the keyboard).
  3. In the header cell, click the filter icon.
  4. In the field (below the Text Filter option),type *a Screenshot (130).png
  5. Ok Screenshot (133).png

For ?

  1. Select the cells you wish to filter. Screenshot (129).png
  2. Select Data –> Sort and Filter –> Filter (Control + Shift + L on the keyboard).
  3. In the header cell, click the filter icon.
  4. In the field (below the Text Filter option),type ????? Screenshot (132).png
  5. Ok Screenshot (134).png

For ~

  1. Select the cells you wish to filter. Screenshot (129).png
  2. Select Data –> Sort and Filter –> Filter (Control + Shift + L on the keyboard).
  3. In the header cell, click the filter icon.
  4. In the field (below the Text Filter option),type Screenshot (131).png
  5. Ok Screenshot (135).png

NOTE :

  1. Remember, the headers in the criteria should be exactly the same as that in the data set.
  2. Advanced filtering cannot be undone when copied to other locations.
  3. 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 !