6 Super Simple Keyword Research Excel Tips to Help With SEO

Excel can be an absolute Godsend to SEOs if used to its full potential especially for keyword analysis. From writing your own macros and cell functions, to simply learning how to make the most out of the built-in tools, Excel has a lot more to offer than first meets the eye and can provide many Excel keyword search formulas to help speed up and organise the process.

Here are some useful tips that we use on a day-to-day basic here at Liberty Marketing. These are all fairly basic Excel tips and not necessarily SEO-specific but each can massivly aid in Excel keyword research. A good rule of thumb when using Excel regularly is that almost everything can be automated. If you find yourself doing the same Excel procedure repeatedly, research ways of automating the task.

Here are 6 simple ways you can use Excel to supercharge your SEO keyword research analysis and more on a daily basis.

1)     Prepending a string to cells

With all the different keyword modifiers [1] in an SEOs toolbelt, it’s handy to have a quick way of applying them to your keywords. Here’s a really fast way of appending or prepending a keyword to your cells. There are many ways you can format your sheet with this method, here’s an example:

Have your main keyword in Column A, and then create a new column for each of the modifiers you want to use. In this case those modifiers are “Buy”, “Where to buy”, and “Cheapest” – all typical commercial modifiers.

In Cell B2 write the following, where “MODIFIER ” is a placeholder for your actual keyword modifier.

=”MODIFIER ”&A2

Another way you can achieve this is by using Excel’s concatenate function to combine 2 cells into one.

 

Note: You need to add a space before the last closing quotation mark otherwise your modifier will combine your keyword as one word.

Useful Tip
Keep a “keyword modifiers” Excel document that contains a list of all different types of keyword modifiers. Some examples can be:

• Commercial – buy, cheap, where to buy, buy online, etc.
• Locations – List locations by region ready to use for location-specific clients.
• Informational – how to, what is, which, download, etc.

2)     Filter keyword by number of words present

This is a really useful tip for very long-tail keyword research in particular.  You’ll want to sort your keywords by how long-tail they are (i.e. how many words they contain). With this tip you can filter by the amount of words present in your keywords list. Here’s how it’s done:

1)      Highlight your keywords column

2)      Go to Data > Filter

3)      Click on the arrow that is now at the top of your selected keyword list, then: Text filter > Contains

4)      Where each asterisk represents any word, enter the amount of asterisks you want to filter by, separated by a space:

Now all of your keyword will be filtered so that only keywords that contain 5 or more words are shown.

Useful Tip

When using this method, it can be useful to include all keywords from the GKP (Google Keyword Planner) as opposed to only selected keywords. Doing this and then filtering for extremely long-tail in Excel will give you more ideas than if you manually filtered and added keywords from GKP and then filtered long-tail in Excel.

3) Highlight Positive and Negative Keywords with Conditional Formatting

Sometimes negative keywords slip through the GKP net. For example, if you are researching a site that only sells different types of gold jewellery and no other types of metal, you may wish to completely exclude results that contain negative keywords like “silver” or “platinum”.

Using this method lets you see at a glance which keywords you instantly want to consider keeping.

1)      Highlight all of the cells you want to conditionally format. (Ctrl + A) highlights everything.

2)      Go to Home > Conditional formatting > Manage rules

3)      From the “home” ribbon tab, click conditional formatting > manage rules > new rule, and select “format only cells that contain”, and then “specific text” from the dropdown below:

4)      In this case the keyword we have highlighted is a negative one, so we will colour it red. Now click on “format” (in the screenshot above), then navigate to “fill” and select the lightest red:

5)      Click ok, and now all the keywords that match your negative keyword rule will be highlighted red.

Useful tip

Make sure you’re not filtering out keywords such as “gold vs. silver”, which could be useful for long-tail keywords and on-site blog posts. Consider doing a search for those keyword modifiers such as “or“, “vs”, or “versus” first to see if they’re worth considering. Another example could be something like “Shopify vs Magento” which could actually make for a very good blog post and would be a shame to filter out.

4)     Skip to the Top or Bottom of a Range with the Keyboard

This is particularly useful when highlighting a number of specific columns, for example when opening GKP Excel docs that contain keywords. My process looks like this:

1)      Highlight entire columns C + D (Keyword match type + Currency) and hit (Ctrl + -) to completely wipe out both columns

2)      Highlight cell B2, then, holding Ctrl + Shift, press ↓ once (down on the  keyboard)

1. This highlights all keywords in column B

3)      Let go of Ctrl, but still holding shift, now press the → key (right on the keyboard)

4)      Press Ctrl + C to copy all keywords and their associated search volume

5) Using VLOOKUP and INDEX to retrieve text from a cell

VLOOKUP can seem confusing at first, but it’s a really helpful function and is easy to implement when you know how. So what is VLOOKUP? Taken from the Microsoft Office website, here’s the definition:

Searches for a value in the first column of a table array and returns a value in the same row from another column in the table array.

The V in VLOOKUP stands for vertical. Use VLOOKUP instead of HLOOKUP when your comparison values are located in a column to the left of the data that you want to find.

For those of you familiar with another database, such as MySQL, VLOOKUP basically selects the entire row as an array and then, based on your chosen index, uses your chosen array index for the column. See the example image below:

Using the red example:

=VLOOKUP(1,$A1:$D6, 2, FALSE)

Here are the options for the VLOOKUP function (in their representative order):

• Lookup_value: VLOOKUP searches the far left column of the chosen sheet. The lookup_value takes the value of the cell you wish to select. For example in this case we wish to select the order ID of 1.
• Table_array: This is telling Excel which range of cells to use for the lookup results.
• Col_index: Tells Excel which column you want the results for. For example, in this case with an index=2, we are selecting the “Product name” column.
• Range_lookup: (default = TRUE). This takes a Boolean value (meaning TRUE or FALSE). If TRUE, Excel will try to use a different appropriate value if it cannot find your exact match lookup_array. Excel will use the next largest value that is less than the lookup_value. If set to FALSE, Excel will only use the exact match to your lookup_value. If it is not found, then an #N/A error is returned.

6)     Stop formula incrementing when copy/pasting cells

Sometimes you want to copy and paste cells while keeping the formulae in place, but Excel automatically increments the cells for you. This feature is actually extremely helpful, but you don’t always want it! The fix boils down to whether or not you wish to use absolute references or relative references. So how do you choose which you want?

Use a $ (dollar sign) to lock particular parts of your formula in place so they stay the same regardless of where in your sheet they’re moved to. Here’s an example:

The following formula would auto-increment its function relative to where you paste it:

=VLOOKUP(A1, A1:D6, 5)

Whereas adding in dollar signs will lock down that specific reference to act strictly as the desired contents regardless of where you paste it:

=VLOOKUP(A1, $A$1:$D$6, 5)

Summary

Excel really is an awesome tool for anyone who wishes to store and manipulate data and keyword analysis in Excel is a key aspect of any SEO’s day to day activity. What I have found is that the more I use it and realise how much it can do, the more I wish to learn all the stuff I don’t yet know. I come from a web development background which lends itself nicely to developing custom macros, so lately I’ve been searching for ways in which writing custom macros can help my workflow.

So far it’s been extremely useful for speeding up and improving the quality of my long-tail keyword research. For example, I can now split up keywords into different columns depending on how many words they contain, or automatically appending/prepending a keyword modifiers to a selected range, all by clicking a button from the Excel ribbon bar.

See what we can do for you by getting in touch with our SEO team today.