How to Make a Pivot Table and Chart in Excel

For SEOgadget clients, most of the SEO work they receive from me is delivered at the end of a heavily data driven process. If you’re feeling a shift towards data driven SEO too, then the chances are using pivot tables and charts in Excel is a near daily part of your SEO consulting activity.


Image by: Incase Designs

At some point we all have to up our game, especially with Excel and general analysis skills, so at the SEOmoz Pro Training Seminar late last year, I gave a step by step tutorial on how to make a beautiful chart based on an Excel Pivot Table.

Almost 6 months after the presentation I’ve finally gotten to tick another blog post off my ideas list: “how to make a pivot table in Excel”.



The chart above is the finished article showing search volume (or analytics entries) and ranking on the secondary axis. It’s my favourite SEO chart, and feels especially useful for keyword research presentations. Here are the main steps to making the chart above:
Gather your data and create a Master table

Pull down some keyword research data from Google Keyword Tool, the Search based Keyword Tool or your usual source of keyword volume information and paste the exported CSV into a (Master) Excel Table. You don’t have to use tables, but I recommend you do – amongst other reasons, tables are less work for your processor, less work for you and can be fun to name. Next, you need to run your keywords through your favourite rankings checker (mine is Advanced Web Ranking) and put the exported CSV into another table. Excel 2007 pivot tables demand that the data for the pivot comes from one table (until Excel 2010 is commercially launched, I’m sticking with 2007) so let’s do a simple VLOOKUP to pull the rankings data through into your Master table.
Use VLOOKUP to pull the rankings data into your keyword data master table

Use VLOOKUP when your values are located in a column to the left of the data that you want to find, says Microsoft. I’ve written before about the power of this Excel query so, if you’re new to it’s use, try this post on how to use VLOOKUP for matching keywords and rankings data.



Note in my screen grab that there’s the occasional missing value in the rankings columns? That’s because there are no values for that particular keyword in the rankings (raw) data table. If you say, wanted a zero value to appear instead, you could use an IFERROR and nest the VLOOKUP inside the new query. For the time being, we don’t need to complicate things too much.
Create a Pivot Table on a new sheet

Now we have all of our data nicely arranged in one place, let’s get to the fun part. We’re going to add a pivot table to a new sheet. You can add the chart later if you like, but I always add them both at the same time by selecting “Insert > PivotTable > PivotChart“.

Next, a window will appear that looks like the one below. Hopefully, you’ve taken note of your table name (visible via “Design > Table Name:”), though if you don’t know what the table is called it will almost certainly be called “Table1″!



When you click “OK”, you’ll be presented with a blank Pivot Table “field list” and a Pivot Chart “filter pane” on the right of your screen and a very blank looking space on the left called “PivotTable1″.


Add axis fields, values, column labels and filters

If you’re new to Pivot Charts, you’re about to experience a bit of a penny drop moment. We’re going to look at which items of data should be placed where and you’ll see very quickly how a pivot table works.

The PivotTable Field List uses drag and drop functionality to enable you to populate those little white squares with values. As you add values, the table on the left begins to form. Start by picking up your keywords by dragging the keywords (KWs in my screenshot) field into the “Axis Fields” box. Next, drag and drop your search volume figure into the “Values” box. Provided you’re looking at “Sum of KW’s” and not “Count of KWs”, your table on the left will start to make a lot of sense:



You’ll very quickly notice that you’ve created a thing of beauty. A pivot table with all of the keywords in your list and all of their corresponding search volume values. I call this the pivot-table-penny-drop-moment. Having all of your values in a pivot table might not be what you intended, though and as you can see, in my table I have some pesky “-1″ values to clear away. To do so, we need to apply a “Report Filter” by dragging the data point we wish to filter into the correct section:



You can filter by any value in your master data table, which allows for some serious charting! Follow the black arrow to the filter drop down and “Select Multiple Items”. You’re now free to clear out any irrelevant data from your table. Finally, drag down the rankings values into “Values” and you’ll have a pivot table, with keywords, search volume and rankings. Now to make a graph!
A pivot chart is born

You’ll already have the pivot chart right in front of you (mine’s just missing from the screen shots). It may look a little rough around the edges though, so let’s make it look a lot nicer than this:



First, we need to organise the keywords by search volume so we can look at our chart as a tail graph. Highlight your search volume data (the column you’d like to sort in volume order) and select “Data > Sort“.



This will improve matters slightly, but there might be a few too many keywords in the chart. Try filtering out the lower volume terms, at least for the time being.


Sort out the rankings by keyword

We’re really very close to being done. The only remaining challenge is to arrange the rankings in such a way that they make sense, visually. You should never compare fundamentally different types of values on the same chart axis, so lets create a secondary axis for the ranking values.

First, you need to select and format the rankings data series. You could use your right mouse button on the chart and select “format data series”, but that’s fiddly and unnessecary. Instead, select your chart and navigate to the “Format” pane. You’re looking for the “Current Selection” drop down on the far left hand side of the screen. Select the drop down and click your rankings data series, now, select “Format Selection”.

We’re going to place the data on a secondary axis, and change the chart type to a line chart. Finally, we’ll remove the lines in the rankings chart to leave only the markers.
Dealing with the secondary axis and changing chart type

My ranking charts use a reversed secondary axis to place position 1 rankings at the top of the chart. To be able to do this, we’ll need to edit the secondary axis. Right click on the secondary axis in your chart, and choose “Format Axis“. Setting your minimum value to “1.0″ will exclude all of the zeroes in your rankings data and setting a maximum of say, 15 would exclude any ranking higher than 15th. You choose the range that’s right for you.



Finally, check “values in reverse order” and you’re almost done.
Finishing touches

One tiny point left to do, we should change the chart type so our secondary data makes a little more sense. Select the rankings data bars and navigate to “Design > Change Chart Type”. Select the line graph option with visible data markers in the line. Now, take out the line colour from inside “Format Data Series > Line Colour” and you’re done. Here’s the chart I reproduced while I was writing the blog post:



And there you have it. How to make a pivot chart and table in Excel. Have fun making your own!