Excel or Similar – Use It For Keyword Research!

Straight up, I am already aware this may be stepping a few steps back for some people, however this recently came to my attention in during a chat with a relatively “savvy” SEO Specialist. I was shocked to learn they weren’t aware of this very simple way of generating large amounts keywords. Not to say if you’re not aware your stupid, however if you’ve been in the SEO industry for a while and still do not, then, well, your close to it!

What We Want To Do – Generate a s***load of search terms (or keywords), whichever you prefer to call them.

Why? – Because, often most webmasters or companies running websites will be missing out on a tonne of potential traffic.

How? – Using the Concatenate feature within excel to generate masses of keywords.

Let’s Go…

So we’re going to shoot through this pretty quickly, if it’s not detailed enough for you or feel there’s a part which could be explained further – please do let me know in the comments and I’ll be more than happy to explain it.

Let’s use “holidays” as an example here, in fact, holidays to European countries.

Step 1.

In Cell 1A, write “Keyword 1″. Using the cells below 2,3,4… and so on write out your list of countries – in this case, a few European counties.

Step 2.

In Cell 1B, write “Keyword 2″. In Cell 1C, write “Keyword 3″. This can go on as long as necessary, I’m sure you get the idea.

In Cell 2B, write “holidays in “. Notice how I’ve put a space after the word “in” too, this is so that when we join up the keywords there will be a space between them. Then duplicate Cell 2B down to Cell 6B.

Repeat this step with the remaining columns so you have a decent variety of keywords. People do not just look for “holidays in {country}”, another example could be “holidays to {country}”. Repeat until it’s looking similar to my version below.

Step 3.

Now that we have a list of keyword variations, we need to join them together. In Cell G1, write “Phrase 1″ and carry this for 5 Cells across.

As shown below, in Cell G2, write “=concatenate(B2,A2)” and hit enter. Concatenate simply strings together the contents of as many Cells as necessary.

Upon hitting enter you will see it has strung together the two Cells to make the phrase we’re looking for. This cell can then be duplicated downwards to complete the series for all countries.

Step 4.

Repeat Step 3 for the remaining phrase columns to generate the phrases you’re looking for.

And that’s it! As shown above, we can see that with just a short few steps we are able to generate 5 x 5 variations of keywords totalling 25 different search terms. 25 may not sound like much now, but a holiday based website would be using this for 100 + counties x 5 variations, creating 500 search terms. This can work in so many ways for as many or little variations as necessary. Digging deeper, you would look at; Countries, Cities & Holidays, Flights, Hotels etc. I’m sure you see the picture I’m painting!

Once you’ve mastered this technique, you can literally generate yourself thousands of keywords in 30 minutes. Well, that’s one way to do it…

Hope this helps you, please let me know if you need anything else explained further. Thanks!