Spreadsheets for SEO: Filtering Google Webmaster Tools Data by Domain

Hey all, I tend to be the guy everyone comes to for Excel help at Bronco, so I thought I'd start doing posts on solving some of the most common issues I find popup being an SEO.

This may well have been talked about before by other bloggers, but it may be new to a lot of you, please let me know whether you want to see more posts like this or not.

Ok, so my first post on spreadsheets for SEO, I'm doing a quick tutorial on how to manipulate your latest links list from Google Webmaster Tools.  Download it as a CSV and you should get something that looks like this when you open up Microsoft Excel:

Let's face it, it's a pretty nasty big list of URL's, with no domain de-duplication, in a nutshell it's not easy for your average user to manipulate.

So one of the first things you might want to do is reorganise it so you can see how many unique domains are linking and maybe a breakdown of the number of links per domain (similar to what you might get from MajesticSEO or Ahrefs – except this is Google's data – I can't stress enough how important that is).

So the first step is to pull out the domain for each url, you can do this by adding a new column in column C with the heading "Domain".  I then use this formula to pull out the domain name:

=LEFT(A2,SEARCH("/",A2,9))

Basically it looks for the first occuring forward slash after character 9 and then grabs everything up to and including that forward slash, this gives you a useful list of domains:

Now if you want to just view the links for one domain, you can simply do the following:

1. Click on any cell containing the data

2. Click on "Data" and then "Filter" (I'm in Excel 2007, so have a look around if your version is different to mine) – incidentally you can also do this in Google Spreadsheets, have a look around.

3. Once the filter is up you should be able to click on one of the arrows in the "Domain" column and filter by a domain name, simply deselect all and then click the domain you want to look at, for example:

This is great if you want to look into a particular domain.

Now if you want to get a good overview you might want to use a pivot table… To do this:

1. De-filter and select the whole piece of data (ctrl + shirt + end might help here).

2. Once you have all the data selected go to "Insert" and then click "PivotTable" (if it gives you an option of a chart or a pivot, choose a pivot).

3. Click "OK" – it'll create a new worksheet using the data you have selected.

You should now have something that looks like this:

4. On the right should be a side bar with "PivotTable Field List" as the title.  Drag the field "Domain Name" to "Row Labels" and then drag the field "Links" to "Values", this should give you a count of the links by domain.  It should look a little something like this:

You can even determine how many domain names are linking into your site, simply select the line in between "Row Labels" and the first domain name, your cursor will change (excuse the wobbly writing, mspaint isn't my thing):

Once you click that, it should select the data in the column (but missing out the heading and the grand total at the bottom), then if you look on the bar at the bottom right it should have a count, that is the number of domains, here's a screenshot of what it should look like:

Anyways, let me know if this is any help, and if anyone wants me to do a tutorial on anything else SEO/Excel related, please let me know