Tracking Monthly Differences in Keyword Rankings with Excel

If you track search engine rankings and send reports to clients every month, a good way to present this information in a monthly report is to create an Excel sheet with historical data of your rankings charting your progress from when their campaign began to the present date.

Keyword Checking Tools like RankTracker is good for giving you an indication of whether your search terms have gone up or down. But if you check your rankings often, have many keywords and track both local and global versions of Bing, Yahoo as well as Google, the final published report will be quite long. Often you (or a client) will have to trawl through pages and pages of data before finding the data they require that month.

Using Conditional Formatting in Excel, creating a simple chart to track whether you have made a positive or negative impression in the SERPs is made really simple. All you need to do is set up your keywords when the campaign begins and enter each month’s rankings into the same sheet. You’ll get a green indication for a positive move in the SERPs as well as the number of places, yellow for no change and red for a negative move in the rankings compared to last month.

Here’s a what a few month’s worth of ranking data looks like.

Historical Ranking Keywords

Conditional Formatting is easy! Simply create a column for every month and a column for the difference. (The first month will not need to have a difference as it is essentially a benchmark for when the campaign began)

Conditional Formatting

On the difference column, use a formula to subtract the numbers in January’s cell from February’s cell, in this case, B6-C6. Then set up Conditional Formatting using the Highlight Cells Rule. I have set these three rules as below:

• If ‘Greater than’ 0, use ‘Green fill with Dark Green Text’.
• If ‘Less than’ 0, use ‘Red fill with Dark Red Text’.
• If ‘Equal to’ 0, use ‘Yellow fill with Dark Yellow Text’.

You could also choose conditional formatting - text, cell fill colour etc.