Excel for Search: How to analyse keyword fluctuations

Author avatar

Sean Longthorpe

Excel is a much more powerful tool than we often give it credit for. In fact, with the tools and reporting that can be made with Excel, I think its uses far exceed its initial purpose.

So, how can we use Excel within Search? We’ve already shown one method in a previous post that showcases how we can utilise simple(!) stats to show whether rankings have increased, but what else can we do with Excel?

Investigate rankings within niches

Retrieve your keyword rankings for any two given days into an Excel workbook. Put each day’s rankings into a different sheet to be organised. Now, in a master sheet, have your master keyword list formatted as a table. Formatting as a table allows you to filter your table to investigate keyword categories, providing that you have categorised those keywords.

Add new columns to your table for the rankings. We can do this by simply inserting a new column to the right of the final column. Now transfer your rankings into the table using the VLOOKUP function. This function matches strings between tables so that we can link data together. In this case, we are matching keywords with their rankings. Your VLOOKUP function should look something like this:

=VLOOKUP([Keyword], [Table number], [Table Column Number], FALSE)

If you’re still having issues, ExcelJet is a good source of information for all the functions of Excel. This is the VLOOKUP example.

Excel VLOOKUP example

Add a new column to the table so you can calculate the differences in the rankings, or even traffic. Hopefully you’ve categorised those keywords, so you can investigate each niche. What we have now is a data source to analyse: use a PIVOT table! Insert your PIVOT table, using Insert > Pivot Table.

Inserting a pivot table

Click on the whole table to choose as the Pivot table source.

Pivot table source

Drag Keyword Category into the Row box.

Keyword category in the row box

Drag your Change column into the Values box. Make sure you have this value as a Sum.

Change column in Values box

And there you have it, a handy table that shows which keyword categories have increased or decreased the most in rankings or traffic, or whatever change your looking for. A valuable, easy, and quick analysis we can all do in Excel. Category 5 giving us the greatest increases here!

Table showing keyword fluctuation

Advanced Excel

If your keywords have secondary or multiple categories, or you have loads of data, you can be as granular as you want to be. Drag as many variables into the columns and rows for a more detailed comparison across as many niches as you are investigating!

Summary

  • Putting your data into a table means it can be filtered and referenced easier.
  • The VLOOKUP function makes linking data much easier.
  • Pivot tables make analysis of the data simpler and easier to gain insight.

Click here for more information on how we can help with your insights and analytics, and put more useful tools, including Excel functions like these, into practice.

Bath

+44 (0) 1225 480 480

20 Manvers Street

Bath

BA1 1JW

Leeds

+44 (0) 113 260 4010

2nd floor, 2180 Century Way,

Thorpe Park,

Leeds, LS15 8ZB.

London

+44 (0) 113 260 4010

5th Floor, Cordy House,

91 Curtain Road

London, EC2A 3BS

Part of the St. Ives Group

  • By pressing submit you consent for Edit to contact you via your email or telephone number for purposes relevant to your request for our goods or services. Your contact details, including your name, company, telephone number and email address will be used by Edit. By contacting you are agreeing to Edit’s Privacy Policy. If you have any questions, please ensure you review this section before submitting.

  • This field is for validation purposes and should be left unchanged.

© 2018 Edit. St Ives Group. Company reg. no. 3624881, All rights reserved. VAT Registered GB 927458295 Privacy Policy | Terms & Conditions | Cookie Policy