Best Methods To Filter Keywords | Tutorial

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.
   • 
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.
   • 
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:

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:

1. Type in = in the first empty column. This will tell Excel that you are starting to write a formula.
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).
   • 
3. Type an * and click in the weight value for that criteria (0.40).
   • 
4. Then, type in + to begin selecting another criteria.
   • 
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 Filter, Keyword 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.

Step 1 – Import Your Keyword List

At the top of the tool on the right side, choose “get statistics only“. Then, copy and paste your entire keyword list in the txt box. Each keyword should be on a separate line. In this example, I will input a list of keywords I obtained for “bike shop”.

 

Step 2 – Select User Intent

In the Intent smart filter on the left side of the tool (under “advanced filters”), three intents are listed: Know, Buy, and Local. 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?)
• Buy: Searching for specific items online to purchase (Ex: Buy office supplies)
• 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.

 

Step 3 – Select Relevant Topics

After importing my list of keywords, Twinword ideas has also detected several topics in the keyword list that people are interested in. Simply use the Popular Topics filter on the left side of the tool to view them. 

 

Now you can select all the topics that apply to your business or website, or even create your own custom topic. In my case, I’m very satisfied with the topic options so I simply selected the topics that are useful for my bike shop.

 

Step 4 – Select Minimum Relevance

You can also find a Relevance filter on the left side under the advanced filters. You can use this filter to only get relevant keywords. Simply select the appropriate relevance level, and the keyword list filters automatically. 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 until the keywords are relevant enough. 

 

 

Step 5 – Lower Maximum Organic Competition

This criteria will greatly depend on your overall rankings on search engines. If you rank high 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 SEO Competition filter on the left to filter for low competition keywords.

Step 6 – Sort List And Select Keywords

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. For example, you can sort the list by search volume to show the best keywords with the highest volume. You can also sort by SEO competition score, paid competitive score, keyword score, title score, or relevance.

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 right 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:

• Brainstorm Keywords
• Best Keyword Research Tools
• Types of User Intent
• What is Organic Competition?
• Where to Place Keywords in Your Blog Post