Categorising your links with Excel and Open Site Explorer

by on 20th June 2011

This is an older post from our blog (before we rebranded to Builtvisible) and we’ve sinced launched an Excel plugin to download link data via the main link API services. Check out the plugin here, and enjoy the post!

Having an understanding of the link profile data associated with a website has always been key to understanding the likely competitiveness of the site in organic search. With some luck, this post will help you discover a way to determine the types of links in yours, or your clients back link profiles.

Link data deep diving

For me, there are a few at play – linking C blocks, quality metrics such as page authority, domain authority and of course, anchor text. There are so many tools available that I probably barely need to mention the fact that Open Site Explorer, Majestic SEO are as critical to link analysis as oxygen is to breathing.

Because of such tools, it’s far easier to understand how many links you have and their overall quality, it’s still pretty tough to pin down what type of links you have. I’m talking about directories, “quality” article websites, forums, “academic” links and so on.

Today I’m going to share a snippet of my link assessment methodology – a method to help you determine what type of links might be lurking in the back link profile of your latest client acquisition.

Background reading

Before you get started, you’ll need some background reading. I’ve already covered the Excel skills you’ll need to assimilate over on SEOmoz, so if you missed out, skip over to “Keyword Research – Using Categories to Make Your Process More Actionable“, have a play around with arrays and the categorisation query and head back here for the rest of this post.

An image from the keyword categorisation post

How to categorise your back links by link type

What type of links do you have? What category of websites might be out there, linking to you? You can’t categorise them all, but, you can have fun trying to get a rough idea. Check out this beauty – a simple dashboard counting the types of links to an example website I created shortly after the first US Panda update.

small excel links dashboard

If your site has a massive directory submission footprint, or has a very large number of links from article sites such as, you’ll be able to see what’s what in this handy dashboard view.

How to get started

To get started, you’ll need to download the file at the bottom of this blog post, and of course, fetch the “linking pages” data from your favourite website via Open Site Explorer:

open site explorer

Then, simply open the file and paste your data in the top right hand cell of the “OSE Data” tab:


Finally, head to the “DashBoard” tab, and select: “Data > Refresh”


As soon as you click refresh, the pivot tables will all update and you’ll get a gorgeous, infographic style report on your back link profile.

How do I drill down on these links?

Being able to “see”, visually, the make up of your back link profile is great to a point – but it’s nice to be able to drill down and get to the data. In the “DashBoard” pivot table view, simply double click a value (see right: “Directory”). The double click action will open a new tab with only the directory links identified.

How does this work (roughly) and how can I improve it?

This particular version of the spreadsheet is ready to be extended to meet whatever purpose you see fit. Essentially, the formula in the spreadsheet checks each row in the “URL” column in the “OSE Data” to see if it matches with any of the domains in the “Domains” tab. To see the domains that have been included, right mouse click the tabs and unhide the “Domains” sheet.

domain list

The “Panda Winners” data is calculated from the original Sistrix data set on the topic, while the free directory list is available on the Directory Maximiser website.

It’s easy to update the lists or rework them as you see fit.  For example, you may wish to extend the directories based on your own data, or include blog links you’ve built to look at crossover between your own, and other’s link building strategies.

Anchor Text Distribution

distribution of anchor text

Ok, this is awesome – where can I download this spreadsheet?

You can download the spreadsheet here – if you find it useful, or have suggestions, modifications and improvements, feel free to add your comments and downloads right here. The best files will get listed on this blog post with a link back to your site. Happy link data deep diving!

Here’s one I made earlier

I couldn’t resist one last demo – click the image for the full sized version

the finished item


  1. Excellent article, I have been doing similar analysis using the Open Site Explorer data, but the downloadable spreadsheets should be very useful for people not as advanced with excel!

  2. Great post Richard, very useful indeed. I’m a big fan of keeping things organized and tidy so this spreadsheet is going to go down a treat.

  3. I love the caption “Link data deep diving”, this is exactly what I’ve done with the spreadsheet. It’s great and my team loves it too..Thanks for sharing

  4. Richard, thank you for the download!! As more a newbie with excel where would you look for either templates like the one you gave or learning resources.

  5. Thanks for sharing your spreadsheet… definitely makes my life much easier!

  6. Tried this but quickly realized my little free version of Excel Starter doesn’t have a Data tab, so I can’t refresh the data (or if I can, I couldn’t figure out how).

    Now I’m sad. :(

    Maybe I’ll pony up the cash to upgrade. maybe…

  7. Great Article. Thanks for sharing.

  8. Wow David, great work – I can see I’m going to spend a few days before the end-of-month reporting playing with this

  9. ¿¿David??

  10. Actually Richard, I think you look like a David to be fair. LOL

  11. Very generous of you to give this away as a download. Will definitely be playing with this for a while!

    Unfortunately I am stuck with Open Office right now. Doh!


  12. Thank you for sharing this great spreadsheet! One note (probably, not very important ;)), there are some duplicates within your list of directories. But of course it doesn’t cause any faults. Cheers.

  13. Great spreadsheet…we work with another one but you gave me some good ideas to implement in our own excel. Thanks for sharing Richard :)

  14. Great post & thanks for sharing.

    Not sure why, but my graphs are not being created by seomoz data added? Not even anchor text pie chart. hmm…

  15. THX for the spreadsheet. I will test ist…

  16. Just tested this. Works like a charm! Thanks a lot Richard. Stumbled & Tweeted.

  17. This is a very slick application. I would buy something like this.

    • Well, now you don’t have to! And of course feel welcome to develop the idea, add new domains and categories and send it back so we can share with the rest of the community. Thanks Brian!

  18. Woah, great Spreadsheet, will definately be using this in the future!

    Your a Sreadsheet Ninja!

  19. Great post. Have been using OSE a lot lately, especially to compare competitor links.

  20. Absolutely awesome article. For laypersons this is an incredible resource.

  21. This is a really nice article. I thank you for the time and effort put into it.

  22. Great article, thanking for sharing the info. Looking forward to your webinar with seomoz.

  23. SEOmoz have added a new column to their reports called ‘Number of Domains Linking to Domain’. So, importing data from OSE don’t forget to add the same column to the link-categoriser. Also, if you want to extend, for example, the list of directories, edit formulas before that. ;) Hope it’s useful. Thanks one more time for the link-categoriser.

  24. Looks like it could be a great tool. Sadly I’m one of the dinosaurs with an out-dated version of Excel :-(

    What’s the minimum version required for this tool?

  25. Thanks, should come in useful

  26. Does anyone know how to get opensite explorer to update the number of links have been staying the same number now for over a month

  27. I have to agree with Matt – in Excel for Mac (at least that’s what I have) the data in the “Dashboard” pie chart isn’t being populated – and I don’t know enough about Excel to monkey around and fix it. Any thoughts?

  28. Hi Richard,

    Thanks so much for sharing this valuable resource for free. I’ve noticed that the new OSE inbound links report contains an extra column: “Number of Links” (F) which is not within this spreadsheet. Easy to deal with, I simply deleted it before pasting the CSV into columns A-J.

    Cheers, and see you at Mozcon!

    Amanda, is it possible that when you pasted the new data, you overwrote columns K-Q? And did you click Data -> Refresh?

  29. I’m a first timer on SEO Gadget, and what a resourceful page to land on. Nice link analysis & spreadsheet!

  30. Great article. Unfortunately, I’m getting the same problem as Amanda with the chart not refreshing, even after deleting the number of links column and making sure columns A-J are correct

  31. ^ on Mac Excel 2011. Anyone else getting the same problem?

  32. Great use of Excel Richard, especially in these “link hypersensitive” times we live in :) Keep the Excel tools coming.

  33. Totally awesome! This is a fantastic tool. What a great way to do a website analysis.

  34. Is it possible to get a more updated version? I see the spreadsheet was 2011 and a lot has changed since then?

  35. Hi,

    Im’just a beginner at organizing data with Excel. The sheet works really great except i can’t seem to expand the list of blog networks. It does not get taken into the formula like the directory column. When i change the reach from F7 to wider range by hand, the whole formula breaks and doesn’t return the right values anymore.

    how can i fix this, or expand these lists without the formula breaking. I’d love to expand this file with social links 2, which will work kind of the samen but then the formula has to work.

  36. Hello Richard,

    First off, great tool. Extremely practical and visually powerful.

    I do have a few questions:

    With OSE changes since the first iteration of your article, should we filter data as follow:

    and when stripping the csv, should we remove the following column:

    Finally, how do you categorize paid directories? When looking at the hidden tab, it seems only free dir are included.

    Thank you for your response and again, thanks for the great tool.



    ps. there is a potential typo:

  37. Thanks for a great article. I am up on your theory.

Comments are closed.

We're hiring – check out our careers page Careers

Get insights straight to your inbox

Stay one step ahead of the competition with our monthly Inner Circle email full of resources, industry developments and opinions from around the web.