Creating Keyword Opportunity Lists with Excel Custom Sort

Creating Keyword Opportunity Lists with Excel Custom Sort: "

Posted by kasy.allen

Using Find & Select and Custom Sort in excel for keyword research helps a simple list of keywords turn into an organized cluster, and everyone likes organized clusters right?

I use this method to show clients new blog post ideas, landing pages they could create and a smarter way to organize their site based on keyword research.

Ready? Let’s do research for a recipe blog. The holidays are around the corner and it’s time to start preparing, but what should we write about? Can we create any posts that will help for after the holidays?

1. Head over to your favorite keyword tool and start typing in your keywords: “holiday recipes,” + “Christmas recipes” + “recipes”

2. Export all of the keywords to excel (2007), combine them into one long list and sorted them from highest monthly clicks to the lowest

3. Insert a new column and enter in the formula “=IF(A2=A3,TRUE)” next to the first keyword

4. Pull the formula all the way down so that you see a full list of “TRUE” and “FALSE”

5. Delete all of the keywords that have “TRUE” next to it, so that you have a list free of duplicates

6. Scan through the list and delete all of the obscene keywords and branded keywords, like “Rachel Ray holiday cooking”

7. As you go through the list, make note of common terms that you come across: Christmas, Cooking, Baking, Country Terms (Mexican, German, Italian, Ethiopia), Vegetarian/Vegan, Punch/Drinks, Dessert, Meats (Chicken, Turkey, Salmon, Beef), Crockpot/Slow Cooker, Kid Friendly

8. Create a new tab, because you don’t want to delete the raw data in case you want to reference it later

9. Click on Find & Select (under the home tab, in the editing category) > Find, and enter one of the terms that you saw come up in the keyword list, like “cookie”

10. Instead of inserting a term next to Replace With, click on Format, pick your favorite color and click Replace All

11. Click on Sort & Filter (next to Find & Select) > Custom Sort > Expand the Selection > Sort. Sort by the column that holds your keywords (in the picture below you can see that I have already labeled my column as “Keyword”)

12. Under Sort On, change the drop down to “Cell Color,” then pick the color that you chose under Order

13. Click OK

14. Highlight all of the “cookie” keywords, copy and paste in the new tab that you created (this becomes your first list of organized keywords)

15. Go back to the first tab and un-fill all of the color for “cookie”

16. Re-sort by monthly searches (highest to lowest) - Make sure to do this, because some terms that you sort are going to contain multiple keywords, like “baking Christmas cookies”

17. Go back and complete the list of terms that were found

Once done, all of the keywords are categorized and a lot more manageable. It’s easy to see different keywords that are getting searched, trends that are coming up and possibly categories you didn’t think of, like “recipes for Christmas leftovers.”

Here's the completed list of opportunity keywords for our holiday terms.