How to Make a Pivot Table and Chart in Excel

For Builtvisible 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.

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.

vlookup in action

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.

add a pivot chart to your excel spreadsheetCreate 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″!

Create Pivot Table with Pivot Chart dialogue

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″.

blank pivot table

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:

pivot field list populated

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:

pivot field list filtered

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:

Your chart's so ugly, when it joined an ugly contest, they said "Sorry, no professionals."

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“.

sort by volume

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.

chart with fewer terms

Sort out the rankings by keyword

format selectionWe’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”.

secondary axisWe’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.

axis options

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

chart typeFinishing 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:

Our completed pivot chart

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



Stay Updated: Sign Up for Webinar & New Blog Alerts

14 thoughts on “How to Make a Pivot Table and Chart in Excel

  1. Shahid says:

    Nice tutorial Richard, may have to pinch this off you :-)….

    Shahid

  2. With the correct attribution, of course!

  3. Gareth James says:

    Great post, my excel skills should be much better. Will re-read tomorrow and have a play around.

    p.s. “gotten” is not a word – you’ve been spending too much time with the moz crew…never forget you’re a UK SEO :)

  4. Haha – thanks, you are quite right of course :-)

  5. Jordan says:

    I’ve just come from your site from SEOMoz, seems like you have a lot of fantastic articles! :)

    One thing I would say is that I was looking for a “more” (or equivalent) link below your article snippets featured on the home page. I think it’s a usability feature a lot of people expect to see. I thought your newsletter sign-up could be in a more prominent position too.

  6. Hi Jordan – thanks for the feedback! I’ll take a look at that for my next piece of dev work!

  7. rickvalentine says:

    Great post Richard. Been looking for a nice simple, but well explained tutorial like this.
    Thanks again,

    Rick

  8. Al Sefati says:

    HI
    I read your article on SEO Moz. This is by far one of the most useful SEO posts I have read recently. Thanks and keep up the good job.

  9. Epithet says:

    Hi,
    Thanks for the tips, great reading. Any chance you can write something more about how you are using ‘Advanced Web Ranking’?
    Thanks
    Epithet

  10. Dyson Deals says:

    Thanks for the idea! I’m a sucker for stats and much prefer to use Excel for reporting.

  11. Simone Loizzo says:

    Thanks Richard, exactly what I was looking for.
    I am definitely going to keeping an eye on your blog ;-)
    Simone

  12. Cara says:

    Hi. Great article and if you ever teach a class or give a webinar, I’d love to join you! I’d even pay for it. Thanks :) Cara

  13. Amber says:

    Wow I felt like I was back in my excel class doing pivot tables! Thank you so much for the tutorial, I needed that refresher and it will be so useful for helping me track my seo progress with keywords! Thanks!

  14. Ramesh says:

    Hey, Superb and well explained tutorial. Thanks a lot

Comments are closed.