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 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”.
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:
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 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.
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.
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.
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.
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:
Great post and also the tool on which the article is based on is great keyword research tool and it makes things easier.