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 activity. At some point we all have to up our game, especially with Excel and general analysis skills, so at the 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.

Excel Hint: How To Apply A Theme In Microsoft Excel 2011 For The Apple Mac. Say you are new to Microsoft Excel for the Mac, or just an average Excel user, and you want to make your workbook more attractive to the user, your boss in this case.That surly must take a lot of effort, right? Wrong, it is super easy to do, and the impact is instantly measurable.

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, the or your usual source of keyword volume information and paste the exported CSV into a (Master). 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 ) 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. I’ve written before about the power of this Excel query so, if you’re new to it’s use, try this post on 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.

As I mention in my post on creating pivot charts, you can’t create pivot charts in Excel for the Mac.It’s, hands down, my greatest aggravation with Excel. Adobe switch from mac to pc version master suite for a feww. However, Excel for the Mac has an additional bug that renders any pivot chart created on a PC that includes a report filter utterly useless. This gives us the following chart note for example added title produce sales you can add your own by clicking on also filtering charts in excel microsoft blog rh. Filter is turned on by default when you make a table, and you can see these buttons in the header row of a table. You can toggle Filter on or off by pressing Command-Shift-F. When you click the Filter button in a column header, the Filter dialog displays. The column header label is the title of the dialog. Filter lets you sort and filter.

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.