How To Build Open Site Explorer
in Excel

by on 25th October 2012

Want to Build Opensiteexplorer in Excel?

Back in late July 2012, we launched the SEOgadget Extension for Excel, a free Excel extension for SEOmoz API consumers to quickly and easily pull link data into their reporting.

There’s been a larger volume of requests for the Open Site Explorer tstrongplate I constructed to dstrongonstrate the power of this extension so, I thought I’d share the download and give a little information on how, exactly it works.

To download the file, follow this link.

How does Open Site Explorer for Excel work?

If you recall from the original extension launch post, Open Site Explorer uses a links API call to show you what sites rae linking to a domain, subdomain or full URL.

The API call in the Excel Extension looks like this:


A working API call to fetch link data for say, may look like this (give it a try, copy and paste into Excel!).


The resulting data produced would be 100 external, followed pages pointing to the page sorted by page authority.

A Slight Limitation with Arrays

The *only* problstrong you may have encountered with using these functions is that arrays, once created are impossible to edit. That means you have to copy the formula, make whatever change you need to make, paste the formula back into a call and carry on. This is a minor limitation, as most often you really only need one data set – but (and this is where our extension becomes a platform), you can use variables in the API call to get around the problstrong.

For example, using our OSE  links API call, we could set the URL to be a variable controlled by a value set in cell A1:


Now, you’re able to refresh the data instantly (it really is very fast with SEOmoz’s new API endpoint).

Data Validation in Excel is Awesome

Here’s the Open Site Explorer for Excel query:


Note that many of the parameters, like “external+follow” have now been replaced with cell references, in this case: CONCATENATE($H$12,”+”,$H$11).

Data validation in “list” mode allows a cell to become a drop down list of options. That drop down list is determined from anotehr cell range in the sheet. Like this:

Our “No. Results” drop down list. Where are those data points coming from?

Head to “Data > Data Validation”. See how “List” has been selected and a cell range highlighted? That’s where the options live! It’s in column Z, so let’s scroll over and take a look:

Roll Your Own

There are a myriad of possibilities for this platform – I truly hope to see examples of quick apps, prototypes and proof of concepts gestating from it. When you realise that you don’t need so much development help to make these things possible, it really gets exciting. If you’ve built anything awesome, do share and we’ll put a link to it on our site.

Don’t want to build your own, that’s fine – To download the file, just follow this link.


  1. Hi,

    Amazing tool but I get a “401 not authorized” error.

    Any idea?


    • You might be making an api call that requires a paid account – try the basic command: =SEOMOZ_URLMetrics_toFit(“”) if that works, but your more complex stuff doesn’t then review the post again and work out what’s available in the free api.

  2. I’ve been playing around with using this to create something for awhile. But have recently been getting nothing returned error.

    Really like the use of Microformats on this page btw.

  3. Awesome, will try this our tonight. Will I only get the same amount of request as I would on a free account?

  4. I’m having a problem with the Links API Excel Extension. I can see the functions, but all I get is errors even when I use you template. I also get an error even when I try a simple call like the one one listed above.
    When I paste this in a cell:

    It returns this:
    ERROR: Nothing returned for via***&Expires=130984648&Signiture=*** (Note *** indicates value hidden)

    What am I doing wrong?

  5. Bill – try encasing your url in quote marks

  6. Hi Richard,

    I don’t have any idea as to how to use this template. Need your help. How do i enter data in this template?


  7. Hi
    This is going to be one great spreadsheet but when I go to the link it’s all in unicode and opening it does not work. Can you please please forward the file to me email:

    Thank you ever so much


  8. Hi,

    Have you launched a new version of the Excel plugin. If yes, how can i use that.


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.