Best Methods To Filter Keywords | Tutorial

Keyword Scrabble Letters

So, you have an extensive list of keywords generated during your keyword research for a page you wish to rank high with. Fantastic! But which ones are the best keywords for your needs? A generated list of keywords can have hundreds or thousands of keywords and handpicking even a few keywords from such a huge list can be a daunting task.

The old method to filter keywords consisted of using an Excel spreadsheet and applying complicated formulas. However, some keyword filtering tools have become sophisticated enough to allow for advanced filtering. Here are two ways to quickly filter the right keywords for your needs: the old method, and the new method.

If you want to give the Excel method a try read further. But if you are in a hurry, you can skip directly to the new (faster) method.

This article assumes that you already have a broad list of keywords you wish to filter. If you don’t already have a keyword list, brainstorm a few main keywords and use a keyword research tool to obtain a keyword list.

 

 

The Old Method: Excel Spreadsheet

 

 

With Excel spreadsheets you can filter keywords to your liking according to a set of criteria that you select. This allows for custom filtering, as you can also adjust each criteria’s weight. This method, however, requires more time and knowledge of Excel formulas. If you think you’re up to the task, follow these steps to filter keywords in Excel.

 

 

Step 1 – Clean Your List

After opening the keyword list in Excel, you should first remove unwanted spaces from each keyword.

  1. Select and copy the entire keyword column.
  2. Create a new sheet in the same workbook.
    • Creating New Sheet in Excel
  3. Paste the keyword column in the first column of the new sheet.
  4. In the first cell of an empty column, enter =LOWER(CLEAN(TRIM(A2))) where A2 is the empty cell beside the first keyword.
    • Formula to clean keyword list in Excel
  5. Press Enter. This will remove unwanted spaces at the beginning and the end of the keyword
  6. Click on the small square at the bottom right of the cell and drag it to the end of the keyword list to apply the formula to each keyword.

 

 

Step 2 – Create Your Decision Criteria

Once your list is cleaned, you’ll need to decide which criteria you wish to use to filter the keyword list. Here are some examples of criteria that you can use:

  • Relevance score
  • Search volume
  • Organic competition
  • Minimum number of words (in my case, I will filter out keywords that are less than 3 words long)
  • Exclude specific words (in my case, I will filter out keywords containing the word “car”)

 

 

Step 3 – Exclude Keywords

Some of the above criteria will return true or false values. These criteria set the minimum requirements for each keyword, meaning that if the keyword does not meet the criteria, it will not be considered at all.

In the example above, each keyword containing less than 3 words and containing the words “car” need to be removed from the list.

First, let’s remove the keywords containing the word “car”.

  1. Select the entire column and rows containing the keyword list and scores.
  2. Click on Sort & Filter -> Filter in the Home tab.
  3. Click on the small arrow on the right side of the first cell of the keyword list column and select Text Filter -> Does Not Contain.
  4. In the first field, enter the word you wish to exclude (in our case, “car”), and click OK.

The resulting list will exclude the keywords that contain the word “car”. Select that list and copy it in a new empty column.

 

Next, we need to calculate the number of words in each keyword.

  1. In an empty column, enter the formula: =LEN(B1)-LEN(SUBSTITUTE(B1," ",""))+1
    where B1 is the cell of the first keyword in your list.
  2. Slide the corner from the lower right of the cell all the way down the list. The resulting number equals to the number of words for each keyword.
  3. Select the entire column containing the number of words and click on Sort & Filter -> Filter in the Home tab.
  4. Deselect the numbers 1 and 2.

This will show only keywords with 3 or more words. Select and copy the resulting keyword list in a new column.

Here’s how this formula works. LEN() calculates the number of characters of any word or cell you place in the brackets. The first part of the formula, LEN(B1), calculates the length of the keyword in cell B1, including spaces. For example, the keyword “bike shop near me” would count 17 characters. The second part of the formula, -LEN(SUBSTITUTE(B1, " ", "")), calculates and subtracts the number of characters for the keyword in cell B1, but without spaces. So “bike shop near me” would count 14 characters without spaces. By counting the number of spaces, we can count the number of words. Of course, our keyword example “bike shop near me” counts 3 spaces, but 4 words. This is why the last part of the formula, +1, makes sure that the word count is correct.

 

 

Step 4 – Decide On Weight For Each Criteria

Now that you have removed unwanted keywords from your list of criteria, you’ll want to attribute different weights to each criteria. This is where you can decide which one is more important for your needs. For example, you may want to place more importance on keywords with lower organic competition and a slightly lower importance on search volume. As an example, you could weight each criteria as follows:

Excel decision criteria

Just make sure that they all add up to 100%.

In a blank area on your spreadsheet (other than in the first empty column after your data), enter your weighting for each criteria. The formula will use these values to apply each criteria’s different weight. You will be able to easily tweak the weighting later if needed, rather than messing with the formula. Make sure that you enter the weights in decimal form (Ex: 45% -­­> 0.45).

 

 

Step 5 – Create A Formula

We are ready to create our formula to calculate each keyword’s score. This is where your Excel skills will be put to the test. For reference, here is what my keyword list looks so far in Excel:

Keyword List

  1. Type in = in the first empty column. This will tell Excel that you are starting to write a formula.Starting a Formula in Excel
  2. Select the first metric value for the first keyword for the first criteria (In our example, the first criteria in our list is search volume, so we would select cell B2).
    • Selecting a cell in Excel
  3. Type an * and click in the weight value for that criteria (0.40).
    • Creating an Excel Formula
  4. Then, type in + to begin selecting another criteria.
    • Adding Plus Sign in Excel
  5. Repeat steps 2 to 4 for all other criteria until you have reached the end and press ENTER. The final score for the first keyword should appear.
    • In the end, this is what my formula looked like:
    • =B2*G3+C2*G2+D2*G4
  6.  Click on the tiny square in the bottom right of the cell and drag it down until it reaches the last keyword in your list. This will apply the formula for all of your keywords.

 

 

Step 6 – Sort Keywords

Once your formula is applied to all keywords, you can sort the entire list to show the keywords with the highest score first. Select the entire keyword column, then click on Sort & Filter in the Home tab, then Sort A – Z. The most relevant keywords, according to your set criteria, will show up at the top.

 

 

The New Method: Keyword Filter Tools

 

 

There are several tools out there that can help you filter keywords, including our own keyword tool Twinword Ideas, as well as other keyword tools such as ZEO Keyword FilterKeyword Filter Pro, and more.

Although probably all of those before mentioned tools can get the job done, Twinword Ideas is especially fast as it uses AI to narrow down your list by user intent, topics, relevance, and several other criteria. This is important, because you want to select the keywords based on your target audience and what they are searching for.

For example, if you’re looking for keywords to rank for a tutorial on a blog post, users would likely start their search query with “How to…” to find your post. Likewise, if you wish to rank for your bike shop in Chicago, you want keywords that will help you with local search for people looking for bike shops in that area. This is where Twinword Ideas excels.

So, to get you started, here’s how you filter keywords using Twinword Ideas.

 

Twinword Ideas

 

 

Step 1 – Import Your Keyword List

At the top of the tool, click on Import. Then, copy and paste your entire keyword list in the textbook. Each keyword should be on a separate line. In this example, I will input a list of keywords I obtained for “bike shop”.

 

Import Keywords in Twinword Ideas

 

Step 2 – Select User Intent

In the Intent tab on the right side of the tool, five intents are listed: Know, Do, Buy, Local, and Web. Make sure you know which user intent you plan to rank for. Here’s a quick description of each intent:

  • Know: Searching for information (Ex: Who is the founder of Microsoft?)
  • Do: Perform an action online (Ex: Covert 84 F to Celcius)
  • Buy: Searching for specific items online to purchase (Ex: Buy office supplies)
  • Web: Navigational, find a web page (Ex: Google Play Store)
  • Local: Finding businesses in user’s immediate area (Ex: Vietnamese restaurants near me)

As my bike shop is in Chicago and targets people in that area, I want to get only the keywords used by people searching for bike shops in that area. Selecting the “local” intent will filter the list that way.

 

Selecting User Intent in Twinword Ideas

 

 

Step 3 – Remove Unrelated Topics

After importing my list of keywords, Twinword ideas has also detected several topics in the keyword list. Click on the Topics tab on the right side of the tool to view them. 

 

Twinword Ideas Topics tab

 

In my case, it seems like there are keywords related to cars and vehicles, which isn’t what I want. To remove those keywords:

  1. Select the topic you wish to remove (in my case, “vehicles”). The list will now only display keywords related to vehicles.
  2. Click on the checkmark at the top of the list to select all keywords.
  3. Click on Remove to delete all of the keywords related to that topic.
  4. On the right side under the “Topics” tab, click on Clear to deselect the topic and show the new filtered list.
  5. Repeat these steps for all other unwanted topics.

 

 

Step 4 – Select Minimum Relevance

The sliders on the right lets you adjust different parameters to filter the keywords’ metrics. To find out if your list has lots of unrelated keywords, sort the list by relevance from low to high. Quickly check the first keywords on the list to see if they are related to your topic. If the keywords seem unrelated, adjust the minimum relevance using the slider on the right until the keywords are relevant enough. 

 

Twinword Ideas Relevance Score

 

 

Step 5 – Lower Maximum Organic Competition

This criteria will greatly depend on your overall rankings on search engines. If you rank highly on several pages and you have a high domain authority, you might not worry about organic competition and may want to go ahead and use keywords that are competitive.

However if your site has a lower domain authority, and you wish to use less competitive keywords, use the Organic Competition slider on the right to set a maximum competition score.

To do this, under Organic Competition, drag the right handle bar of the slider to the left. You can also input the number directly or use your up/down arrow keys while your cursor is editing the number.

 

Twinword Ideas Animated GIF Search Volume and Organic Competition Filter

Animated GIF Search Volume And Organic Competition Filter

 

 

Step 6 – Sort List And Select Keywords

Sorting Keyword List in Twinword Ideas

Now that you have filtered your list to include the keywords with the highest potential for your target audience, you can sort it according to your most important criteria. Here, you can sort the list by search volume for example, and show the best keywords with the highest volume. You can also sort by organic competition score, paid competitive score, or relevance score.

Once the list is sorted, you can go ahead and manually pick the right keywords. Since your list is filtered, the first keywords should be used. If you find that the first keywords aren’t related to your business, you might want to go back to the previous steps and tweak some of the filtering settings.

To save your new filtered keyword list, click on the Download button at the bottom to download the list in Excel formats (.CSV or .XSLX) or . ODS format.

That’s it! You now have a short list of the best keywords to target your audience. Tools to filter keywords offer a fast and convenient way to bring up the best keywords for your specific needs. 

 

 

Which Method Is Best?

The method you decide to use to filter keywords depends on your personal preference. Twinword Ideas is much faster and simpler to use for everyday keyword research and it allows for advanced filtering using AI.

For regular blogging, a keyword research tool is your best friend. But, if you’re into customizing formulas or if you need to add other criteria,  an Excel spreadsheet may give you that flexibility.

In the end, both methods will yield high quality keywords. Whichever method you use, it’s critical that you place those keywords in the right place to increase your chances of having search engines rank your page according to those keywords.

 

 


If you would like to read more on keyword research, check out these related articles:

 

 

Shawn Rousseau
Shawn Rousseau
Marketer at Twinword, Inc.

Leave a Reply

Your email address will not be published. Required fields are marked *