You probably want to start your next Keyword Research as soon as possible after reading our Mastering Keyword Research Whitepaper. 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 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.
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.
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]”
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.
Using the concatenate formula to generate more keywords
For those of us who work in an agency, time is gold. We cannot 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 that 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:
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:
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 now you have 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, so now you can 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.
Now we have come to my final piece of advice of this humble list of tips, 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 you information requirements and you can even create different groups if you want.
You can add the name of the groups manually, using text filter as I have already recommended or using other formulas like IF or VLOOKUP (Maybe I’ll show you about those in the next post). 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 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?)
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 keyword research, get in touch by filling out the form below: