Keyword research is an important part of the optimization process. There are thousands of ways, tools and resources to do keyword research. Every SEO has their own methodology, favorite data and ways to organize and sort through that data in order to create solid keyword lists for their SEO campaigns.

But there is more to keyword research than just the research. You have to be able organize and sort your keywords into groups that will allow you to optimize them most effectively into your website.

This post will walk you through the organizational side of the keyword research process, using a customized keyword research spreadsheet I have created to make the process easy. You still have to do the research, but this tool helps us create keyword lists for on-page optimization.

Let's get to it.

First, you need to download and open the spreadsheet linked above. You should be on the "Phrase 1" tab at the bottom.

If you know what your "core" phrase is, you can rename that tab accordingly. We'll work with the phrase "battery charger" since that will give us plenty of variety to work with.

Open document

Importing Your Keywords

Before you can import anything you need to have already gathered a list of keywords from your favorite keyword tools. For this post we'll use Google and Wordtracker.

You first need to export your keyword lists from the research tool to a different spreadsheet. Make sure your search volume is to the left of the keywords. If not, you'll need to move some columns around until it is. For Google you can insert an extra column between the search volume and the keywords so you can copy/paste cleanly into this keyword organization spreadsheet.

Tip: When exporting from Google, be sure to select the exact match for accurate search volume numbers. You'll then need to use find/replace to remove the brackets before importing to your keyword spreadsheet.

After you paste the keywords, be sure to select the option that allows you to keep the formatting on the current sheet rather than importing the format from the sheet you copied from.

Once you paste both sets of keywords and search volume, your sheet will look something like this:

Import Keywords

Note: Google will let you export up to 100 phrases while Wordtracker can give you hundreds if not a couple thousand. This spreadsheet can handle a little over 2000 phrases from both Google and Wordtracker (or your favorite source) combined. To keep this post simple, I've eliminated all but 600 phrases.

Merging Duplicate Phrases and Data

A good number of phrases will be duplicated between your research tools. We want to merge these so the Google and Wordtracker numbers are on the same row for each keyword.

The first step is to alphabetize the keywords. To do this, click column "D" so the entire column is highlighted. Then, over on the far right on the Excel "Home" tab, you'll see a "Sort & Filter" option. Click that and then select "Sort A to Z."

Sort A to Z

This will create a "Sort Warning" dialogue box:

Sort Warning

Be sure "Expand the selection" is selected; then hit "Sort". This makes sure that the search volume numbers stay with the correct search phrase as they are sorted A to Z.

The result should look something like this:

Sorted Keywords

If you look at rows 16/17, 21/22, 26/27, and 34/35, you'll see the same phrase with a different search volume for both Google and Wordtracker. These are the duplicates we want to merge.

This spreadsheet has a cool "Merge Duplicates" button. Click that button and all the dupes will be merged together. Just like magic!

Tip: Make sure no garbage code is imported in with the keywords. If a bad text is imported into the "All Keywords" column, the merge macro can get screwed up.

Here you can see the duplicates merged together. Notice rows 16, 20, 24, and 31.

Merged Keywords

Now you want to sort by search volume. Going back to the sort button at the top, select that; then, choose "Custom Sort."


A new dialogue box will pop up allowing you to customize your sort options. You want to sort by "Ggl" first, then by "WT" (or visa versa). You may have to "Add Level" to get the second sort option to be available.

Tip: Be sure to sort on "Values" and order "Largest to Smallest."


Sorting by volume, you can get a pretty good idea as to what phrases are more popular than others as noted by either or both tools.

Be careful not to use search volume alone for determining keyword value. As you can see below in rows 18, 30, and 37, Wordtracker isn't showing search volume for some substantial Google phrases. Similarly, if you were to scroll down, you'd also see quite a number of phrases that Google doesn't record as viable, but Wordtracker does.

Custom Sorted Keywords

Note: The lack of search volume on some of the Wortracker phrases here is likely due to me having deleted any phrase that registered less than 10 in Wordtracker search volume.

Eliminating Negative and Junk Words

Anytime you import keyword lists this size you're going to end up with a number of junk keyword phrases. This spreadsheet provides an easy way of getting them out of your way without having to delete them one by one.

First, scan your list looking for any negative words you want to eliminate. Then add those words to column A in the negative word tab at the bottom.


In this case we'll pretend the client sells only automotive battery chargers, so the four words we typed in above can be globally eliminated.

No, go back to your research tab and click the "Check Negatives" button. Any keyword phrase that contains a negative word is automatically highlighted red throughout the worksheet!

Highlight Negative Keywords

Note: The Negative Words tab is designed for global application. Do not add negatives that are core-term specific.

If you accidentally added a negative word that does not belong, no worries. Just remove it and re-click "Check Negatives" to correct.

Important: Don't leave any empty cells in the negative keywords list. If you remove a keyword be sure to fill in the blank spot.

If you have a lot of negative words highlighted and want to get them out of your way, the spreadsheet has a filter option that lets you hide them all. Click the filter icon at the bottom right corner of the negative column heading, then uncheck the "x".

Filter Negatives

This will cause all the negative words to be hidden. Notice below that rows 47, 61 and 77 are now hidden.

Filter Negative Keywords

In most cases, using negative keywords is not enough. You will need to manually go through and eliminate keywords that aren't any good for optimization to your site. Simply place an "x" in the "No Good" column and your phases automatically highlight red.

No Good Keywords

Note: Do not manually add "x's" to the Negative column. If you run the Check Negatives macro again these will automatically be removed. Only manually mark negatives in the No Good column.

You can then filter out all "No Good" keywords just as you did with the negative words, leaving you only with phrases that you believe are worth optimizing!

Categorizing Into Shopping Funnels

I've written about the Research, Shop and Buy funnels for keywords before. You need to be familiar with that before using these categories.

The general idea is that you want to categorize each keyword into one of these funnels. You can through this section in four steps, marking phrases that fit any one category at a time, or go keyword by keyword and mark the most appropriate category for each as you go down the list.

Tip: If you organize one category at a time you can hide each category when complete leaving only unassigned keywords. This can help make sure none are missed.

Once you're done your spreadsheet will look something like this:

Shopping Funnel

Assigning Keywords to URLs

Next you want to figure out which keywords should be optimized to any given page on your site. Let's start with the "Research" funnel. Use the filter options to hide all other keywords but those.

Research Funnel

Looking at these keywords together can help you determine which URL(s) is/are best to optimize for the keyword group. Place the URLs in the blue area at the top of the page. Start with P1 (Page 1, see above). Now go through your funnel list and add "p1" to each keywords you plan to optimize for that page.

Note: You will likely find that not every keyword in each funnel works for a given page. You might need several unique URLs for each filter group.

Research Funnel 2

As you can see, we placed "car" and "automotive" keywords on page 1, "motorcycle" keywords on page 2 and "boat" keywords on page 3.

When you're ready to optimize, you can use the filtering option to hide all keywords except for the page you you are optimizing. Below I've filtered all but page 1 keywords.

Research Funnel 3

Now you can back and repeat this process for the Shop, Buy and Info keyword funnels.

One last thing. If you run out of blue URL space at the top of this spreadsheet, don't fret! You'll notice the rows jump from 6 to 14. There are six hidden rows that will allow you to add more. All you have to do is highlight rows 6 and 14, right click and select "unhide".


Now you want to repeat the entire process with your next core phrase! If you need more core term tabs, just copy a blank one and start afresh.

Follow at @StoneyD, and @PolePositionMkg.

October 28, 2011

Stoney deGeyter is the President of Pole Position Marketing, a leading search engine optimization and marketing firm helping businesses grow since 1998. Stoney is a frequent speaker at website marketing conferences and has published hundreds of helpful SEO, SEM and small business articles.

If you'd like Stoney deGeyter to speak at your conference, seminar, workshop or provide in-house training to your team, contact him via his site or by phone at 866-685-3374.

Stoney pioneered the concept of Destination Search Engine Marketing which is the driving philosophy of how Pole Position Marketing helps clients expand their online presence and grow their businesses. Stoney is Associate Editor at Search Engine Guide and has written several SEO and SEM e-books including E-Marketing Performance; The Best Damn Web Marketing Checklist, Period!; Keyword Research and Selection, Destination Search Engine Marketing, and more.

Stoney has five wonderful children and spends his free time reviewing restaurants and other things to do in Canton, Ohio.


The link organizational side of the keyword research is broken

Thanks Joseph, the link has been fixed.

Hi Stoney,

I think this spreadsheet is a really great start. However, I am curious as to why only search volume is considered? It seems as part of the keyword strategy it would be a best practice to also consider and weigh in keyword difficulty. Search volume alone should not be the only indicator in determining whether one should optimize for a specific keyword. For example, if a website hardly has any authority and is trying to optimize for a high search volume keyword, it just wouldn't make sense to only target keywords that way.

This article ( ) describes how to examine keyword difficulty... I think your spreadsheet and taking it a step further by examining keyword difficulty and maybe organizing that data by adding in 3 columns for Short Term, Medium Term and Long Term growth strategies is a bonus to your spreadsheet. (Just my 2 cents about it.)

Again, this spreadsheet is a great start --- very easy to use and thank you for providing it.

Hi Kelly, Honestly, keyword difficulty is not something I consider when looking at what keywords to optimize. If a keyword is relevant, to me it *should* be optimized. The difficulty of the keyword only comes into play in setting proper expectations. But it makes no sense to leave out a great keyword just because the site isn't ready to rank for it. Set expectations, optimize, and do what you have to do to build up the site authority.

One thing to also keep in mind, the search volume numbers are not there to make decisions for you. they are there as a reference. Just like the "research", "shop", and "buy" columns are there as references. All this information should be used to properly organize keywords and pages. Just because a keyword is on the list doesn't mean it is a good fit for a particular group or page. At the same time, you can root out difficult/overly-broad keywords when doing the same sorting process.


Sharing is caring! Thank you for putting this great process organization tool out there for folks to use. You've captured so much good process here - this will be of help to so many search marketers!

I've recently finished tech edits for Ron Jones upcoming book, "Keyword Intelligence: Keyword Research for Search, Social, and Beyond." (Wiley), which talks about not only the importance of good keyword managment, but how important it is to develop tools like this one of yours to make the process manageable.

You've once again shown why the search marketing industry has been such a joy to work in - leaders like yourself are willing to share their best tips, tricks and tools for the good of all.

Thank you!

Matt VW

Thanks Matt,

This spreadsheet has been extremely helpful in our KWR efforts. I used to deleted bogus keywords in the keyword research tools, however I've found that this spreadsheet does a much better job of allowing me to delete junk, or just organize them into a category to refer to later.

Great article, and it's nice to see the process in such detail. I'll certainly be using a spreadsheet like this to keep track of where we are! Thanks

Great article and suggestion for organizing the ever complicated management of keywords . . it is so easy to get overwhelmed by all the keyword tools and what to look at, what to do with the information, etc. this will be really helpful and provide more structure for me.

awsome info on keyword organization helps emensly.

thanks, dan

Many thanks for the great tool and detailed guide Stoney! Good starting point to organise your basic site structure, but also to use for optimizing your Adwords campaign folders and groups to deep link to your on-page content with optimised keyword sets.

Comments closed after 30 days to combat spam.

Search Engine Guide > Stoney deGeyter > The Ultimate Keyword Sorting and Organization Spreadsheet