Categorising a large keyword list in bulk takes hours to do, unless you do it a special way. Learn how to categorise thousands of keywords in seconds through the use of a magical Excel formula.
You can categorise data in Excel with this formula, it doesn’t only work on keywords. Another popular use of mine is to categorise landing pages, which can significantly help with showing how ranking performance has improved certain landing page’s traffic.
Let me show you how you can categorise text with keywords in Excel, removing the need for another keyword categorization tool you need to pay for.
This is an older video, of the keyword categorisation process in Excel. The overall principles are exactly the same, however this one will not work in Google Sheets.
But, I have a solution for that. A different formula that works in Google Sheets.
Categorising & Classifying Keywords in Excel
To categorise and classify your keywords in Excel, just like in the video, you can download my Excel template below.
Categorising & Classifying Keywords in Google Sheets
If you would rather categorise keywords in Google sheets, my preferred method these days, then you can copy my categorisation sheet below.
You will see a very similar template to what is shown in Excel in the video above, except with a slight tweak to the formula.
The formula I use for this is;
=IFERROR(ARRAYFORMULA(INDEX(*categoryRange*, MATCH( TRUE,ISNUMBER(SEARCH(*findSeedWordRange*,” “&*keyword*&” “)),0))), “Unclassified”)
(might need to change the quotations due to formatting issues)
You’ll see one major difference with the Google sheet though, and that comes down to the cell ranges used for the find/category keywords.
If a cell range in the formula is larger than what’s available, when no category is found it will leave the cell blank.
To use the ‘unclassified’ text, the range needs to be exactly the same as what’s available. This means every time you add keywords into the find column, you’ll need to expand that range.
I tend to use just the blanks as the other/generic/unclassified as it tends to make life a bit easier.
New categorisation formula & setup
I was reached out to on Linkedin by Kyle Faber and he believed he could help tweak the categorisation system a little bit.
Using a combination of regex & a textjoin, he was able to help tweak how the categories are applied.
It does appear to be faster that the setup posted above, and is also a lot simpler & cleaner which will help troubleshooting.
I’ll break it out a bit more later, but for now, the new formula for joining all the seed phrases together is;
=textjoin(“[s]?|”,true,sort(I$3:I,1,false))
Then the formula used in the category cell, is;
=IFERROR(VLOOKUP(REGEXEXTRACT(” “&A2&” “,I$1),I$3:J,2,FALSE),”Unclassified”)
Which will categorise the exact same setup above, with a different formula, by looking for the matching find, and then return the category with a vlookup.
It will default to partial matching, but if you’d like exact matching of a word, then enter a space before & after the find column.
That will then match full mentions of the word, rather than just partial matching.
Was great to get some feedback on this, and be able to tweak the setup that I’ve used for years!
Get the updated sheet at the link below;
Definitely give Kyle a follow on his socials, at https://linkedin.com/in/kylefaber & https://twitter.com/regal_kyle.
Categorising URLs into Site Sections
You can also use this directly on URLs, to categorise them into site sections, locations, or anything you’d like. This would allow you to view SEO performance by site section, based on grouping the URLs by folders, or any structure within them.
Just use the exact same formula, but rather than putting keywords in the ‘find’, add portions of URLs that you would like to group together. The formula will then put each of the URL structures together in to their groups, allowing you o view their peformance.
Let me know how you use this sort of sheet to categorize in Excel or Google sheets, or whether you do this all a different way.
