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.
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.
After opening the keyword list in Excel, you should first remove unwanted spaces from each keyword.
=LOWER(CLEAN(TRIM(A2)))where A2 is the empty cell beside the first keyword.
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:
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”.
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.
=LEN(B1)-LEN(SUBSTITUTE(B1," ",""))+1where B1 is the cell of the first keyword in your list.
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.
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).
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:
=in the first empty column. This will tell Excel that you are starting to write a formula.
*and click in the weight value for that criteria (0.40).
+to begin selecting another criteria.
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.
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.
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”.
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:
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.
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.
In my case, it seems like there are keywords related to cars and vehicles, which isn’t what I want. To remove those keywords:
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.
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.
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.
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: