Link Juice Recovery in Excel | Find Links to Dead Pages on Your Site

How many times have you changed up site architecture? If you’re in ecommerce or you work on larger sites, this might be a common problem for you. One of the biggest SEO related implications is that old, relevant, authoritative links may have been lost in the process. They may be going to pages that were not 301d correctly, or just lost and are now 404s.

404  A quick check in Open Site Explorer, Majestic, (or your new favorite link index) will give you a nice list of all the links to your domain and all of it’s pages. Sometimes there are glaring errors in there that are easy to spot.

  • What if you have 500 links, or 50000?
  • How can you tell if all of these links are going to a LIVE page on your site?

One of the only tools out there that does this CRAZY IMPORTANT task is by Cemper. It comes as part of a toolset and is probably the best tool for the job. It also comes at a premium of around $260/month at the time of this post.

If it’s not in the budget then you have to find another way. If you already subscribe to some link index like Majestic or Open Site Explorer you can try this:

  1. Export all your backlinks to CSV.
  2. Move all the URLs that you will check to Column A. (These are the URLs that are pointing to pages on your site from other sites.)
  3. Implement this handy code. (Link Juice Recovery Code for Excel)

    (It is a VBA script that you will implement into your excel doc. For help on getting setup in excel for that, go here.

  4. Select the cell you would like the result of your test to appear in.
  5. Enter “=HttpExists(A2)”

    (without quotes)(“A2” represents the cell that you are checking. Simply pull down or copy/paste the down the rest of the cells you would like to check.)

At this point “TRUE” and “FALSE” should appear in those cells with the formula. Sort by False to start backlinks checking where your links are going. Save your hard work!

This process is a bit rudimentary but that’s why people like Cemper built tools like they did. But with a little time and tweaking, this VBA Script can check any mass-list of links to see if they are LIVE.