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.
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)
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:
You could also choose conditional formatting – text, cell fill colour etc.