7 Excel Tips applied to Keyword Research

EDIT (13/02/2018): More tips added and title changed to 7 Excel Tips applied to Keyword Research.

Keyword Research is key to any SEO strategy and if you’ve just read our Mastering Keyword Research Whitepaper, we’re sure you can’t wait to get started!

If that’s the case, the first thing you will do is open a spreadsheet.

But what next?

When doing Keyword Research that includes a large amount of keywords in Excel, we often face the same issues again and again, so I decided to prepare a small list of useful tips that we can use in Excel. Some of these are very basic and I’m sure many of you already know them; however, I’ll show you how to use these Excel tricks to specifically help you with Keyword Research.

1. Remove duplicates

It may seem obvious, but sometimes when we use different sources to obtain keywords, there are often duplicate keywords that end up appearing several times in our spreadsheet.

Luckily, this is very easy to fix in Excel:

1. Go to the Data Tab

2. Select the area you want to clean from duplicates.

3. Click “Remove Duplicates”

4. Select the column that contain the duplicates you wish to delete (In this example “Keywords”)

5. Click Ok

6. A message will appear confirming you have successfully deleted the duplicates or that the program was not able to find duplicates. In any case, your set of keywords is now free of duplicates and ready for you to start working on it.

2. Text Filters

Now let’s try something a little bit harder. Imagine that you want to undertake very specific Keyword Research and you only want to see a special term, ignoring the rest of the keywords. In this case, the best option is to use a text filter and here’s how:

1. Go to the Data Tab again.

2. Select the header of the column you want to filter and click on Filter.

3. Now you can add a filter. To do so, click on the little arrow next to the column header. Next, select Text Filters and then Contains…

4. Type the term you want to show in the column. In this case, “Special”. Click ok.

5. Now you can only see the keywords that include the term “Special”. To remove the Filter, click in the small arrow again and select “Clear Filter From [Selected Column]”

Bonus Tip: If you want to see all the terms except a few, select “does not contain” instead and type the term that you don’t want to see. For example “Useless”

Now you can only see the keywords that don’t contain the term “Useless”. You can also combine different text filters to obtain more refined results.

3. Using the concatenate formula to generate more keywords

For those of us who work in an agency, time is gold. We can’t spend a lot of time thinking about new keywords or terms and so using Excel formulas can be a quick and easy way of identifying new keywords. The Concatenate formula is useful to put together different cells or pieces of text. Just what we need for this task.

Let’s imagine that we have a list of 50 products and I want to identify the search volume relevance for each of them. In this case, we can identify the keywords for only the first product and then replicate the keywords to the other products. This is how you do it:

The terms I have identified are:

• Offers
• Sales
• Discounts
• Price
• Prices
• Reviews

And my spreadsheet looks like this:

The steps to generate my keywords are:

1. Go to the cell C3 and add the following formula:

=CONCATENATE($B3, ” “,C$2)

Or a simplified version:

=$B3&” “&C$2

Notice that in both cases I block the Products columns (Column B) and the terms row (Row 2) with a dollar sign ($) so we can drag the formula to other cells. Remember to add a space between the two cells. Any manual text that you add to a formula should be in quotation marks, including a simple space, so you’ll need to type “ “ into your formula.

2. Here comes my favourite part, click on the small box on the right lower corner of the cell and drag it to the right until you cover all the terms.

You’ll see how you’ve generated all the keywords for product 1.

3. If you double click in the little box again, the formula will move down affecting all the cells and generating keywords for all your products. You can then go to Google Adwords to obtain the search volume for each of your new keywords. The good thing about this process is that if you launch a new product or come up with a new term idea, you can populate new keywords immediately.

4. VlookUps

If you know how to use the Vlookup formula on Excel, you’ll know how useful it is. I use it in most of my documents because I tend to mix information from a number of different sources and the Vlookups helps to consolidate all the data.

Imagine that you have two sheets: 1 with search volume data and 1 with competition data, taken from a different tool. You can use the search option or the filters to find the correct keywords and combine them in one sheet, but this is a huge waste of time, especially if you are dealing with hundreds of keywords.

Sheet 1:

Sheet 2:

The best option in this case is using the Vlookup formula on sheet 1 to find the right keyword on sheet 2 in each case:

VLOOKUP(A2,Sheet2!A:B,2,FALSE)

The Vlookup formula has 4 elements:

1. The value we want look up. In our case, the keyword: A2 (Keyword 1)
2. The table where we want to look up the value, you can highlight the table to select it: Sheet2!A:B (Our second sheet)
3. The column on that table where the value needs to be retrieved: As Sheet 2 only has 2 columns we insert a 2, because we want to retrieve the Competition.
4. True or False. True if we want to find similar values to the original keyword or False if we want an exact match. I always use False.

So the formula will search for the value on A2 (Keyword 1) on the second sheet and will return the content of the 2^nd column for that value:

Then you only need to drag down the formula and you will have all your data in only one table.

5. Group Keywords

Now let’s move on to something a bit more complicated, grouping keywords to generate a pivot table.

Let’s say that following my previous advice you have generated lots of keywords with their search volume and you now want to know which products are the most popular. In order to do so, you’ll need to do the following:

1. Add a new column to the spreadsheet and call it Groups. Start adding each keyword to a product. In this case we are interested in the distribution according to the product name, so each group will be a different product. However, the groups can differ completely depending on your information requirements and you can even create different groups if you want.

You can add the name of the groups manually, either by using the text filter as I have already recommended or using other formulas like IF or VLOOKUP. Once you have finished, your spreadsheet should look like something similar to this:

2. Once all the keywords have been assigned to a specific group it’s time to work our magic. Select all the cells with the relevant data and go to the Insert tab.

3. Click in Pivot Table and then press ok

4. Now you are on a new screen and you have to select the data you want to add to the pivot table. Add the Group and the Keyword columns to the Row Labels section and the Search Volume to the Values Section so that it looks just like it does in the image below. (To change the value settings, click on the arrow next to the field, select Value Field Settings and choose Sum)

5. Select the first group value and sort them from largest to smallest. Then, select the first keyword value and sort them again largest to smallest.

6. Now click on the first group with the right button of the mouse and select collapse entire field.

Now you should have all your products sorted by most searched, regardless of the keyword used. I find this extremely useful when it comes to prioritising content or pushing campaigns. However, there are a lot of options when it comes to pivot tables…

You can export suggested keywords from Google Adwords and identify new opportunities or, if you work with merchants, identify which are the most popular based on the combination of your keywords and their names (Did someone say concatenate?)

6. Sumif and Averageif

If you have separated your keywords in groups and as a simpler alternative for Pivot Tables, you can use these two formulas to summarise the results of your research.

In this case, for example, we have this table:

If we want to know what the total Search Volume is for Group 1 we just need to use this formula:

SUMIF(A:A,”Group 1″,C:C)

The Sumif formula has the following elements:

1. The column where we want to find our value, in this case, the first one
2. The value we want to search, in this case, “Group 1” (Remember to write the name of the group between quotes or you’ll break the formula)
3. The column with all the values we want to add up

The formula will search for the value we have entered on the selected column and then will sum all the numbers associated with said value.

The Averageif formula is very similar, with the difference that, instead of adding up all of the values it will calculate the average. This is better for other metrics like Competition or some of the Moz metrics. In this case, the formula is as follows:

AVERAGEIF(A:A,”Group 1”,D:D)

Just like the Sumif formula, but selecting the 4^th column instead (The one with the competition).

If we put everything on a table, we can end up with something like this:

And this is perfect for my last recommendation…

7. Graphs

Sometimes keyword research documents can grow gargantuan and it is difficult to identify any trends or opportunities. In these cases, I always like to use a graph that can summarise my findings.

The idea is to use the table above to create a chart that summarises everything we have done to this point.

Select the entire table and insert a column chart.

Now you should have two series of columns, one for the search volume and the other for the competition, but it’s not very intuitive. Let’s change that.

Click on the column that represents the competition with the right button and click on Format Data Series to make this group of data a secondary axis.

This will make the competition data use a different axis, so it will be easier to read. Now just change the chart type for that second set of data from a Column chart to a Line chart (My personal choice, but you can use whatever you like). To do so, just click the column representing the second set of data, and with this set selected, change the chart type to Line chart. The result should look something like this:

Now you can compare all groups. You can see that group 1 has the highest search volume and the lowest competition, so you should prioritise this set of keywords when optimising your website.

If you want a real example, here is a chart that I created for one of our clients in the signage industry:

Keyword Research is the best starting point for your SEO or content strategy so it’s important to get it right. It will help you not only to select the best keywords, but also decide on which areas of your site to prioritise and push first.

I hope you have found these tips useful. Let me know your thoughts in the comments section and if you have any questions or want to know something specifically related to Keyword Research or Excel, let me know! See you soon!

Still unsure? If you’d like to speak to SiteVisibility about how we can help with your SEO strategy or your keyword research, please don’t hesitate to get in touch. You can give us a call on 01273 733 433 or leave us a message via the form below: