Excel Advanced Filter [Multiple Columns & Criteria, Using Formula & with Wildcards]

 

I was performing keyword analysis for one of my new projects using SemRush.com, a competitive analysis tool.

Actually, I was checking for how many keywords a website was ranking in Google.

Domain authority, website age and other web metrics of that website were not that lucrative; so, my concept was if that site could easily rank in Google for some keywords, my new website would do so easily.

SemRush.com gives the option to download the performed analysis in an Excel worksheet. Below, you’re seeing the sample of the data that I have downloaded.

As I am planning an affiliate website to make, so I wanted to take only the buying keywords.

What are then buying keywords? Buying keywords are the keywords that people use to search in Google or any other search engines before taking any buying decision. If you are decided to buy a “Shaving Razor” from online and not sure which one to buy, you might search in Google using keywords like any of the following:

  1. Best Electric Shaving Razor
  2. Shaving Razor Reviews
  3. Top 10 Shaving Razors

So buying keywords have some patterns, in most cases buying keywords have these words as suffix or prefix: “Best”, “Reviews”, “Top”.

If you observe the above worksheet data, you will find that some keywords have one of these words (best, reviews, top) in them and some don’t have.

What I want to do is to show only the keywords that have “best”, “reviews”, and “top” from a list of total 1440 keywords (the data has total 1440 records).

Let’s do that. Let’s filter this data for more than two words or texts.

If I had to filter this data for one or two words/texts, I would at first convert this data into a table and then filter the table in the following way.

Before advancing to the main article, I suggest you download the sample workbook that I have used to create this piece of content: Download www.espressogusto.com-url_organic-us.xlsx.

Filtering for one word/criterion

Here are the steps to filter a column for a specific word or text.

  1. Select a cell within the data that you want to filter
  2. Data ribbon → “Sort & Filter” group of commands → Click on the Filter command
  3. Click on the Filtering icon at the top of column and type the word (I have typed “best”) in the Search field.
  4. Filtering data for one word

    Filtering data for one word.

  5. Click OK. You will get the filtered data like the image below.
  6. Filtered data for one word.

    Filtered data for one word.

Check the filtered data carefully, notice that every keyword  starts with word “best” .

Filtering for two words/criteria

If you want to filter the data for two words, the above method will not work.

But, using the same Filter icon, you can filter the data records for two words.

This time, hover your mouse pointer over the Text Filters command in the drop down and then click on the Custom Filter command.

Filter data for two words

Filter data for two words

Custom AutoFilter dialog will appear. In the dialog, you can filter a table for two words. In the first drop down, I select “contains”, and on the right side field, I type “best”.

In the second drop down, I again select “contains”, and on the right side field, I type “reviews”.

Now, you see there are two radio buttons in the dialog: “And” and “Or”.

Custom Auto Filter Dialog Box

Custom Auto Filter Dialog Box

Using “And”

If you select “And”, then filtered keywords will have both the words “best” and “reviews”.

Filter data for two words using "And".

Filter data for two words using “And”.

Only 23 of 1440 records are found for this pattern. You check the keywords; all of them have both “best” and “reviews” words. In this case, the filtering criterion is: the keywords will contain both the words “best” and “reviews”.

Check the filtered data carefully. All the keywords have both “best” and “reviews” text.

Using “Or”

Now see what happens when I select the “Or” radio button. 

Filter data for two words

Filter data for two words

Total 626 of 1440 records are found. These keywords might have “best”, or “reviews” or both “best” and “reviews”.

Let’s filter now for more than two words/criteria

Sometimes, data analysis demands filtering data in complex ways. You might want to filter your data in a complex way with the following criteria:

  1. Keywords column will have “best”, “reviews”, “review”, or “top” words
  2. Position column values will be less than or equal to 10
  3. Search Volume column values will be greater than or equal to 200

To analyze this combination of criteria, you can use Excel’s Advanced Filter dialog box (Data ribbon → “Sort & Filter” group of commands → Click on the Advanced command).

Advanced Filter dialog box

Advanced Filter dialog box

Advanced Filter dialog box options:

  1. Action
    1. You can filter the list, in place
    2. Or you can copy the filtered data to a new location.
  2. In the List range field, input the data that you want to filter.
  3. In the Criteria range field, input the range where you have made your criteria.
  4. Copy to field is inactive (above image) because Filter the list, in-place option is selected above, if Copy to another location is selected, Copy to field will be active. Input a cell or the range into the Copy to field where you want to see the filtered data.
  5. If you want to see only the unique records, select the option: Unique records only.

Before using Advanced Filter dialog, in a new worksheet, I create a range like seen in the image below.

Criteria range

Criteria range

Check the image, I have set the words “best”, “reviews”, “review”, and “top” below the Keyword heading, then I have set Position less than or equal to 10, and Search Volume greater than or equal to 200 for every word.

Now open the Advanced Filter dialog (Data ribbon ” Sort & Filter group of commands) and click on the Advanced command. Advanced Filter dialog box will appear.

In the Advanced Filter dialog, as List Range, I set the whole range: $A$1: $I$1441, as the Criteria range, I set the range from Sheet1 ($A$1: $I$5).

Advanced Filter dialog box

Advanced Filter dialog box with values

If you click OK, you will get only 17 records.

Filtered data with multiple criteria.

Filtered data with multiple criteria.

Check the filtered data one by one (in the image above). You see that the Keywords must start with either “best”, “reviews”, “review”, or “top” words; no values in the Position column is greater than 10, and no values in the Search Volume column is less than 200.

Filtering a column for intervals

You know what intervals are? 1 – 5, 8- 15; these are intervals. Want to filter a column for different intervals?

Say, you want to filter the Search Volume column for two intervals: 8000 – 13000 and 200 – 500 with Keyword column filtering for texts “best”, “review”, “reviews” and “top”.

You will make the Criteria range like the following image.

Criteria range

Criteria range for intervals 8000-13000 and 200-500

You see that I have made a duplicate of Search Volume column at the right side of the criteria range. For every filtering text, I have made two intervals: 5000 – 13000 and 200 – 500.

And here is the result when I perform the filtering using Advanced Filter dialog box.

Filtered data for two intervals

Filtered data for two intervals: 8000-13000 and 200-500

Only 29 records fall into these criteria. Check every row. All the keywords have either “best”, “reviews”, “review” or “top” words, and search volume values are either between 8000 – 13000 or 200 – 500 intervals.

So that’s the beauty of using Advanced Filter dialog box.

Let’s see how to filter data in Excel using formula

Now, think about a complex situation. You want to see only the search volume higher than 2 times of the average search volume. No way, you can do this filtering without a formula.

I make a range like the following:

Filtering with formula range

Filtering with formula

To use a formula for filtering, you have to remember these criteria:

  1. The formula will use the relative reference to refer to the first cells of the columns, the columns on which you are applying the formula for filtering. Other references in the formula will be absolute references.
  2. The formula must return either TRUE or FALSE values
  3. To list the formula, make a new column in the Criteria range. You can use any heading (I have used “Formula”), even you can use no heading, for no heading, keep a blank space in the place of heading.

If you check the formula that I have made (above image) you will find that I will filter the Search Volume column using this formula. Because I have used relative reference C2 in the formula. The other references are absolute references ($C$2: $C$1441).

The formula returns a TRUE value.

Here is an important thing you should remember. All the four formulas are identical because every criterion in the Criteria range works independently.

So, the result will be like: if a Keyword value has “best”, “reviews”, “review” or “top” texts, and for that keyword if the search volume is greater than 2 times of the average search volume, the records will show then.

Here is the result.

Filtering with formula

Filtering with formula result.

The result shows only the Search Volumes that are higher than 2 times of the average search volume. On the right side of the worksheet, you’re seeing the average of the Search Volume is 188.

So, this is just one example of using a formula to filter a data set.

Use of wildcard characters in filtering data

Sometimes you will come across situations where using wildcard characters (?, *, and ~) will save your time.

Before showing you how wildcards can become a handy tool for you, I want to give you the idea how to use these characters.

The table below shows a brief introduction of the characters.

Use wildcard character…To find…Example
?Any single character.“Wor?” and “Wor?” both will represent the words “Work” and “Word”.
*Any number of characters“North*” will represent words “North-South”, “North East”, “Northeast”, and “Northwest”.
~Wildcard characters in text“Excel~*” will find “Excel* worksheet”, “Excel* Sheet”.

“Excel~~” will find “Excel~ worksheet”, “Excel~ sheet”.

Below, you are seeing the list of Fortune G500 companies (a tiny part of a list of total 500 companies).

From here, download the complete file. I am using the Fortune G500 worksheet of this file.

Consider a type of filtering: you want to show only the companies that start with letter “A”.

Follow these steps:

  1. Select a cell within the data that you want to filter
  2. Data ribbon → Sort & Filter group of commands → Click on the Filter command
  3. The data will be in Filtering mode.
  4. Click on the Filtering icon at the top of the column and type “A*” in the Search field.
  5. Filtering with wildcards

    Filtering with wildcards.

  6. Click OK. You will get the filtered data like the image below.
  7. Filtered data with wildcards

    Filtered data with wildcard (*).

Do you think you will get the same result just typing “A” in the Search field?

Filtering data with no wildcards

Filtering data with no wildcards

Here is the result of that thinking.

Filtering data without wildcards.

Filtering data without wildcards.

The filtered data is showing all the companies that have “A” in their text, not only the companies that start with “A”.

So using wildcard characters, you can filter data in some unique ways.

Wrapping up

This content has already become a long post with 1800+ words, so I am stopping myself here. But stay tuned as I am going to update this post for the following topics:

  • Filtering Excel data based on color
  • How to remove filtering
  • How to filter using VBA
  • How to use Auto Filter feature of Excel
  • Data filtering with drop downs
  • Dynamic data filtering
  • And filtering Excel data to different sheet

Do you want to add something or some special technique you know about data filtering in Excel? Please share in the comment box; it will be highly appreciated 🙂

Have a nice day!

Read More


Hello! Welcome to my Excel blog! It took me some time to be a fan of Excel. But now I am a die-hard fan of MS Excel. I learn new ways of doing things with Excel and share here. Not only how to guide on Excel, but you will get also topics on Finance, Statistics, Data Analysis, and BI. Stay tuned!

7 Comments
  1. Reply
    Jomili July 21, 2016 at 9:48 PM

    Nicely done, but I think there should be a clarification on the “Formula” section; you don’t put the formula in the first cell in the “Formula” column and drag down, but instead put the identical formula in each cell of the “Formula” column. I was getting wonky results until I realized that.

    • Reply
      Kawser July 22, 2016 at 12:13 AM

      Thanks for the input, Jomili.
      Yes, the formulas are identical as every criterion in the Criteria range is unique. I am going to add the note there 🙂

  2. Reply
    Kateryna July 30, 2016 at 7:02 PM

    Hi Kawser,

    How are you doing? I have been practicing this lesson (for which one a big THANKS to you). Could you kindly help me with formatting for intervals? I can’t get what I have done wrong:(
    After filtering using Advanced Filter i have got the empty field..

    Your answer would be really helpful!!
    Thanks a lot!

    • Reply
      Kawser August 1, 2016 at 10:39 AM

      Kateryna,
      Thanks for your kind words. Glad to know that it helps.
      You will get empty cells if no data is available for the filtering. What criteria did you use to filter data in intervals?

  3. Reply
    Roger Gruenenfelder December 14, 2016 at 9:00 AM

    Your blog is a marvel. Nevertheless I miss the opportunity to download , lets say above instructions and teaching for later ealuation together with the example file.
    Is it possible to also invent a pdf file for download ?
    I live in the remote Philippines and internet access or solid internet is, well, sometimes a game…..So it would be helpful if one can review your articles offline. Copy/paste seems not the best idea or is it?
    Thanks for suggestions, Roger

    • Reply
      Kawser December 14, 2016 at 9:36 AM

      Roger, this option (make PDF of the blog posts) is on my plan. Will execute in shortest possible time.
      Best regards and thanks for the comments.

  4. Reply
    shivank January 4, 2017 at 7:07 PM

    HI Sir.. thanks for this great blog. I, however, have a question. What if my raw data source is in another excel file (which is closed)? Could you help me paste filtered records (based on a multiple search criteria) in book1.xlsx with raw data source in book2.xlsx

    best,

    Leave a reply