As all Search Engine and Pay Per Click folks know, having a good list of search phrases is a vital part of your search marketing efforts. The problem is, generating a long list of phrases and maintaining all the variations can be a chore.

Well, good news everyone, we have a solution in the form of a Microsoft Excel spreadsheet!

Our Excel sheet produces your generated keywords both separately and combined together in one string (where keywords are separated by commas). It works on as many prefixes and keywords as you require. Starting from a small core set of words you can generate many full phrases that could be used in your SEO efforts or to generate a long list for your Adwords campaigns.

You can download the file from here.

How it Works

First we loop through all the prefixes, and through the keywords. Next we fill the separate results column while building a combined results string.

Keywork Variation Generator in Excel

For Each rngPrefix In rngPrefixesList
    For Each rngKeyword In rngKeywordList
        ActiveCell = rngPrefix.Value & " " & rngKeyword
        If strVariationList = "" Then
            strVariationList = ActiveCell
        Else
            strVariationList = strVariationList & ", " & ActiveCell
        End If
        ActiveCell.Offset(1, 0).Select
    Next
Next

As you can see, the meat of the code is some very simple string concatenations. You could modify it to add all the Adwords variations you need very easily, such as Exact Match brackets and so on.

At the end we put the combined result in the proper cell. For convenience we made a short-cut to activate the macro (CTRL+SHIFT+V).

Summary

Obviously this is just the start of what we could do. What else would you like to see incorporated into this tool? Perhaps we could do a follow up with more features?

Yoav Ezer

Yoav co-authors the technology and productivity blog Codswallop. He is also the CEO of a company that produces PDF to XLS conversion software. For more Excel tips, join him on Facebook or Twitter

Codswallop blog

You May Also Like

9 Responses to “A Search Engine Keyword Variation Generator in Excel”

  1. Thank you, Yoav. Keeping track of all my keywords has been a challenge with setting up new websites. This spreadsheet looks like it will be very useful.

    When I tried to download I got a message that I cannot get the macros because of my security settings. I will have to look into that further.

    I will let you know if I think of something that should be added to the spreadsheet functions.
    .-= Tammi @ Website Set Up recently posted: Website Set Up E-Book Is Shipped. =-.

  2. Luke says:

    hi there, thanks for that brilliant tool. it took me quite a while to work out how to edit the concatenation to display quotes and brackets on the terms, so i thought i'd put it here for others in the same boat…

    the line you need to edit is:
    ActiveCell = rngPrefix.Value & " " & rngKeyword

    it you want to add [] brackets for example, you would change that line to:
    ActiveCell = "[" & rngPrefix.Value & " " & rngKeyword & "]"

    hope that helps someone.

    • Yoav Ezer says:

      Luke, your solution is great. Thanks for sharing!

      What is more useful to you? just the terms, terms with brackets, or terms with quotes?

      How do you use the list of search terms?

  3. [...] A Search Engine Keyword Variation Generator in Excel [...]

  4. [...] A Search Engine Keyword Variation Generator in Excel [...]

  5. [...] Generate Keyword Variations [...]

  6. [...] The first step to doing any Search Engine Optimization (SEO) or Pay-Per-Click (PPC) advertising is to have a keyword list of the targets you are going to go after.  Generating a long list that combines different commonly used phrases can be difficult so that is why I was pretty happy when I found this keyword variation generator for excel. [...]

  7. Nice tool, thank you!

    The first thing that come to mind is having a phrase generator which would require changing the sheet from Prefix and Keyword to keyword one, two, three, etc.
    I would think a max of five keywords would be a good phrase length. A test for blank to exclude an extra , at the end would be required.

    Thanks