Simple Excel Formula to Categorize Keywords For SEO or PPC

Simple Excel Formula to Categorize Keywords For SEO or PPC

Keyword research is not about choosing high volume and low competition search terms. It is the foundation of a good SEO campaign. When keyword research done right, it can help you to optimize and rank your website more effectively.

While trying to make sense of keyword data, we have to categorize keywords under various themes. This keyword categorization not only group the search terms but also help us to define content strategy and aligning with target pages. The categorization also helps in building the entire information architecture of the site, creating new landing pages, new content, or meta data of a webpage.

How to Categorize Keywords in Excel Sheet

I am not going to teach you Keyword research process. [Internet is filled with such articles]. Here, we are just trying to be more productive and save huge time by automatically categorizing keywords in Microsoft excel sheet.

Let just skip the whole story and come to the point now, and go through the really cool way to categorize the keywords in excel.

STEP 1: KEYWORD DATA COLLECTION

First you build all the keywords list with their respective data set. You can use Search Console, Keyword Planner, SEMRush, Ahrefs keyword research tools to gather all the varied keywords list full of potential opportunity.

Simple Excel Formula to Categorize Keywords For SEO or PPC

STEP 2: KEYWORD COMBINING

You can combine all those keywords in one sheet, for a keyword research report to be easily identifiable, and place them into meaningful and useful buckets.

NOTE: Make sure you TRIM the data to remove any spaces at the beginning or end.

STEP 3: REMOVING DUPLICATE DATA

Since, you might be using multiple keyword research tools, you might end up adding duplicate keywords data.  Just remove it.

STEP 4: CATEGORY CREATION

After doing all the keyword collection from different SEO tools, you have to make a two-column table in the same sheet that match with the keyword data set. This array goes into the MATCH function.

Lists are a powerful way of dynamically classifying the search phrases. Here are some list examples:

• Genders (men's, man, women's, woman, ladies)
• Materials (denim, leather, suede)
• Occasions (birthday, Christmas, Easter)
• Relationships (husband, wife, brother, sister)
• Seasons (spring, summer, autumn, winter)
• Time-frames (Monday, Tuesday, weekend, weekday, January, February)

Excel Formula to Categorize Keywords:

=INDEX(CATEGORY_Column,MATCH(TRUE,ISNUMBER(SEARCH(WORDS_Column,KEYWORD_Column)),0)

STEP 5: APPLYING FORMULA

This is an array formula. Press Ctrl + Shift + Enter keys together, and you will get the first result, then, drag the fill handle down to the cells that you want to apply this formula, and all text strings have been categorized as the specific group as you need.

THE PROBLEM

If the words is Keywords List does not match with the category table, the fomula will get broke and produces #N/A error. To escape from such error, we can use below formula

=IFERROR(LOOKUP(9.99999999999999E+307,SEARCH(FOOD[Words],A2), FOOD[Category]),"NOT FOUND")

If a word in the category does not match with the word in Keyword, the cell will produce "NOT FOUND." Remember, this is not an array formula, just like above. Use this formula and hit enter.

You can categorize anything you want with this formula. You can also get the template file. Just e-mail me at dmi @ gmail dot com.

YOU CAN ALSO SEARCH THIS ARTICLE BY USING THESE KEYWORDS

how to categorize data in excel using formula
excel keyword search formula
assigning categories based on keywords
assign categories in excel
how to categorize in excel
categorize bank transactions in excel
excel categorize data by range
how to classify data in excel