A Search Engine Keyword Variation Generator in Excel
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.
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 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
Leave a Reply
Get the SEP Newsletter
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. =-.
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.
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?
[...] A Search Engine Keyword Variation Generator in Excel [...]
[...] A Search Engine Keyword Variation Generator in Excel [...]
[...] Generate Keyword Variations [...]
[...] 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. [...]
[...] 3. Generate Keyword Variations [...]