Awesome Examples of How to Use SeoTools for Excel

Today I’m going to talk about a tool which I use almost every day within my SEO life!

I want to put the word out there because this thing has saved me countless hours and helped me to learn about Xpath and get a lot better with Excel. I also want to give people working examples of why this tool is great and what can be done with the tool.

SeoTools for Excel is a great tool made by Niels Bosma, which runs as an add-on to Microsoft Excel and features some functions which are incredibly useful for everyday SEO tasks and allow you to grab information from websites, quickly and without even leaving Excel!

You can grab the tool from Niels website for free! Once you have the tool up and running you will see a new tab in the excel layout which looks something like this:

There are tonnes of default functions, a list of which can be found here:

To call a function you select them from the SeoTools menu and fill in the information needed or call the function from a selected cell and apply it to a URL.

I am going to run through some tools and hacks of my own and from other people from across the internet for educational and demonstration purposes.

Indexation Checker

This quick hack/tool lets you check a list of URLs to see if there are indexed in Google and makes use of the HttpStatus function to let you know what status code is being served for that URL. I have found this tool useful for checking the index ratio of a websites content (the amount of URLs indexed) and for quickly checking how much content of a sitemap is indexed.

The tools works by running a site: command in Google for the URL you have entered and uses Excel functions to show whether that exact URL is returned as indexed or not.

You can find this tool in the examples Excel sheet at the end of this post.

Google Keyword Ranking Tool

My friend made this tool and posted in on the SeoTools for Excel forum you can download the ranking tool directly from the forum after signing up and it is also included in the example Excel sheet at the end of this post.

The tool checks current rankings in Google for keywords against the site name you declare.

Although the rankings positions should be taken with a pinch of salt and you shouldn’t ping Google too many times for results (as requests will all be from your IP) this tool is very handy for quickly understanding which pages of your website are ranking. It is also great for getting an idea of the sites in the top 30 positions for keywords.

Also this tool is a great example of what is possible using Xpath, SeoTools for Excel and a little bit of Excel magic!

Live 404 Checker

Using the httpstatus function within SeoTools for Excel, you can quickly assess a 404 error list from Google Webmaster Tools.

This is handy when you are working to remove 404 errors associated with your website as the results you see will be live, which means you can see results faster than waiting for Google Webmaster Tools to update.

Domain Name Research

Niels also gave me a cool tip during the research for this blog post which is crazy useful if you are doing some domain name research.

Using the following string will show you 45 possible combinations of domain names which you can then cross check with the IsDomainRegistered function to see if they are available!

=Dump(SpinText(“{super|awesome|big}{boats|cars|bikes}.{com|nu|se|net|org}”,45))

Using the above line will give you this list which you can then filter to show domains which are available:

On Page Elements

If you want to quickly get an understanding of a websites health and SEO set up but don’t have Google Webmaster Tools then SeoTools for Excel can be a great tool to grab meta information quickly. This is also handy for checking to see if changes have been implemented on a site during an SEO project.

You can paste in a number of URLs and use default SEO Tools functions to show page information:

In the above example I was able to quickly grab the following metrics for a list of URL’s:

  • Page Title
  • Meta Description
  • H1 Headings
  • H2 Headings
  • Link Checker

    SeoTools for Excel can also be used to check if a link is live on a page, which is super useful for link building as you can quickly check a number of URLs to see if links are live and that rel=nofollow isn’t being applied.

    This could also be used for link monitoring to ensure that important links to your site stay live!

    Using the CheckBacklink function you can tell the tool to check a URL (in this example it is link location, cell C5) to see if this URL contains a link to your destination URL (in this example it is link destination, cell B5) with your expected anchor text (contents of cell D5). If the link is live on the page the tool will return Ok.

    Additional Uses

    Pairing this baby with Screaming Frog, Xenu or IIS will make you a much more efficient SEO and remove reliance on slower third party tools. Having a number of ways to perform the same task can be extremely helpful if one solution is no longer viable.

    As far as SeoTools for Excel goes these are just a few ideas and you can easily use the tool to perform the following tasks as well as much more:

    -Grab Whois information for a domain including expiration dates and even domain age using the DomainAge and WhoIsDomainExpires functions.

    -Bulk check PageRank values for a list of websites using GooglePageRank which can be handy for authority checking during link building or link prospecting

    -Grab a few social metrics like Facebook Likes, Google Plus counts and Tweet counts for a URL using the social functions within the tool

    -Check a number of Google metrics for a site including the number of pages indexed and link numbers

    Recent Updates and Additional Reading

    Recent updates to the tool have also included the addition of Google Analytics and there are some great examples of how the tool can be used on Niels website from various bloggers which include gems like the inclusion of SEOMoz API data and how to identify lost links.

    Richard Baxter from SEOgadget has also written a great post about the SEOTools for Excel which runs through some of the main functions.

    While these tools may be useful to some they should be used with caution as things can change rapidly. Also it is important to note that SeoTools for Excel uses your IP address so you will need to be careful about the amount of requests you make to websites.

    If you have put together any tools with SeoTools for Excel which you think other SEO’s may find useful please let people know in the comments.

    Here is the example excel doc which contains all of the examples I have mentioned which you can play around with once you have the SeoTools add on installed

    Have fun!

    Get blog posts via email

    22 Comments

    1. Thanks Luke, shall help me fancify/streamline my SEO excel reporting.

      The best things in life really are free. :-D

      reply >
    2. Thanks Luke. We have been developing SEO audit templates using Niels Excel plugin this month - extremely useful data which is easy to use with just basic Excel knowledge. Combining this plugin with traditional Excel functions with conditional formatting is data heaven :)

      reply >
    3. This is a fantastic guide Luke! Thank you for putting it together. SEOTools has completely changed the way we approach on-page seo recommendation documents. We're able to process more actionable data in less time. Only trouble I've run into it trying to crunch data for 10k+ rows. I've got 12 gigs of RAM and a core i5 processor, but this process takes about an hour or more to run.

      reply >
      • Luke Masters

        Hi Dev,

        No problem glad you liked it! Yes if you are using the tool for larger amounts of data it can get problematic, perhaps another solution like Screaming Frog or IIS might be better suited when handling large data sets?

        Thanks
        Luke

    4. Jay

      Great article, one thing to note while his tool is free he does except donations to pay for hosting and other assorted expenses. Might be nice to let people know

      reply >
    5. Amazing post, I need to pick up my excel SEO game a bit I know most things but some of the great examples you use here are crucial to running an effective SEO project, thanks for sharing this fantastic guide.

      reply >
    6. Great round up, Luke.

      Gladly there are a few PPC uses that I've found for this also. In particular, the XPathOnUrl is great for quickly grabbing key info for like up-to-date prices or stock status by selecting the relevant class and then cross checking with ad copy.

      reply >
    7. Scott

      Any chance to get the 'Indexation checker' to give the date the page was last cached?

      reply >
    8. Hi Luke, absolutely brilliant write up - thank you. Quick question, does this work for Excel on Mac?

      reply >
    9. Scott

      Hey guys I like the tool and was trying to do some link build analysis. Is it possible to add a column into the 'indexation checker' that shows the date the page was last cached?

      reply >
      • Luke Masters

        I have had a play around and it should be possible will have a look into it and let you know :D

    10. Tom

      Thanks Luke. I never knew that in exel i can do such things.

      reply >
    11. Real good piece of work. Thanks for this :)

      reply >
    12. For a lot of the tasks above, I tend to find that SF or Xenu (or even SEOmoz Custom Crawl) works more effectively and quicker, I love SEOtools and it is my starting point for an SEO audit (showing root vs www indexed pages, checking headers on sitemap, robots and a 404 as well as checking lengths of title tags and a few other bits) but typically this is on a single page - I find that doing big spreadsheets, well it sloooowwws right down - If you do use it a lot it is worth disabling calculating things and relying on the f9 update approach!

      reply >
      • Yes, it slows down and can lock your Excel. So what I do is once the date gets pulled in, I copy and paste values over it so that the formulas are not running anymore. This helps tremendously.

    13. Great write up! Just downloaded tool and this is a great guide.

      In the sample document you provide, the ranking tool doesn't seem to be working. It's not showing the results for the different positions?

      Any ideas?

      Alex

      reply >
    14. I will definately be using these for the domain name research.

      reply >
    15. Labib Anderson

      can you please explain step-by-step how to use this function,i am not able making it work: =Dump(SpinText(“{super|awesome|big}{boats|cars|bikes}.{com|nu|se|net|org}”,45))

      thank you

      reply >
    16. Very inspiring. The keyword ranking tool is awesome. :)

      reply >
    17. Inspiring story there. What happened after? Thanks!

      reply >

    Leave a Reply

    Your email address will not be published. Required fields are marked *

    *
    *

    You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>