On the “So You Want to Test SEO?” panel at this year’s SMX Advanced Seattle, Branko Rihtman from SEO Scientist presented some spiffy looking ranking charts, measuring positioning by keyword, over time. A few people asked me how exactly you make a chart like that. Being something of an Excel fan, I was instantly inspired to share the approach with my fellow SEOmozzers. Here’s a step by step on how to create a rankings chart using Excel.
Collect the data
To be able to produce a chart like my example below, you’re going to need Microsoft Excel, and a rankings checker that will export ranking data, by search engine and by date. For now, I’m using Advanced Web Ranking, but there are lots of other ranking checkers you can use. Start by putting your data in an Excel table named “rankings” just like this:
Create a pivot chart
Pivot tables were designed for exactly this type of application, and making them is heaps of fun. Let’s start by selecting “Insert > PivotTable >PivotChart” in the options along the top of your Excel ribbon.
You should see a window appear. Make sure you’ve named the correct range (our table name: “rankings”) and select “New Worksheet“, followed by OK.
Drag and drop your legend, axis and value fields
The cool thing about making a pivot table is the drag and drop functionality when you’re creating the row labels and values for the table. Here’s a visual explanation of where to put your keyword, date and position data:
Next, you’ll need to filter for the keywords you’d like to create a chart for. It’s quite inpractical to create a chart with hundreds of keywords, but you can add a good number for comparision purposes. Head to the “Column labels” drop down and filter for the keywords you’d like to build the chart for:
Filter by search engine
If you’ve collected data on multiple search engines, you’ll need to add a filter. Drag the “Search Engine” field down into the “Report Filter” section, and select the search engine you’re interested in using the drop down at the top of your pivot table.
Format your chart nicely
If you’ve followed the instrutions so far, you’ll see a slightly noisy and weird looking bar chart, so next we’ll create a line chart to show the positional changes over time.
For pure charting awesomeness, a simple right mouse click on the chart, followed by “Change chart type > Line“, will do the trick. Finally, you’ll need to reverse your Y axis, leaving position 1 at the top and your lower rankings at the bottom. Using your right mouse button, click on the axis and select “Format axis” – you should see a window like this:
The end result
After spending some time having fun with formatting, you can create really nice charts. Here’s mine:
Hope you find these tips useful, and if you’d like some more of this, please shout in the comments!
