THE RKGBLOG

How to Speed Up Checking Indexation Rates with Excel

I’ll forewarn you: this post may completely change your life. I mean, probably not, but it’s possible. As SEOs, there are some tedious but necessary tasks we have to tackle. One of those tasks is checking indexation, perhaps for an initial benchmark.

Sometimes it’s just one check on a single site, in which case, a quick site:URL search is the obvious choice. But for those other times when there’s a need to check a batch of sites, I’m going to share an easy process to speed you up.

A caveat before we dive in: site: searches can be a slightly sketchy way to check indexation. Results can vary wildly depending on what data center you happen to hit when you search, and monitoring indexation using XML Sitemaps in GWT and BWT is more reliable and provides more visibility. That said, the site: search does have its place, and that’s what I’ll address here.

There are a bunch of steps, but combined with keyboard shortcuts, this will all take a matter of seconds. Don’t be intimidated! Once you have the process down, it’s very fast.

  1. Open an Excel spreadsheet and put all the sites for which you want to check indexation in column B.
  2.  In column A, type “site:” and copy it all the way down to the end of your list.
  3. In cell C1, type “=concatenate(a1,b1)” and hit enter, and voila! C1 now has a site: search populated. 
    Quick tip: if you start typing your formula (“=concat…”) and the formula populates, hit the down arrow and “Enter,” and Excel will populate your cell with this: =concatenate(), with a cursor blinking in the middle of the parenthesis.  You can also utilize the ampersand to concatenate your columns by entering “=A1&A2″ into C1 instead of typing out the concatenate function.
  4. As in step 2, copy your concatenation for each site: search down all rows.
  5. Copy all of your concatenated site: searches and paste the values in place of the formulas: right click, select “Paste Special” and select the “Values” box.
  6. If you did this step right, you’ll see text in place of your concatenation in the formula bar.
  7. Open a tab in Chrome and go download this browser add-on: Copy All URLs. What it lacks in naming creativity, it makes up for in perfectly functional simplicity and a random umbrella icon.
  8. Open a new Chrome window.
  9. Copy your URLs from your Excel spreadsheet, click the umbrella in Chrome and “Paste.”
  10. All your site: searches will show up in their own tabs, so all you have to do is flip through and hit “Enter” to execute each search. 
  11. So, cool, that does it for Google. What if you also have to check for Bing? Well, I thought about that, too. Open your Chrome Settings and change your default search engine to Bing.
  12. Repeat steps 7 through 9 to check indexation on Bing.

When you’re done, don’t forget to change your default browser back to the Google.

  • Joanna McDonald
    Joanna McDonald is a Senior SEO Specialist at RKG.
  • Comments
    6 Responses to “How to Speed Up Checking Indexation Rates with Excel”
    1. Zach Doty says:

      Very neat article! Always nice to see handy tips and tricks.

      I wonder if there is a way to completely automate this through Google Docs? Will have to tinker tonight.

      There are ways to “scrape” (for lack of a better term) SERP URLs and possibly elements, (e.g. Number of results) through the Google Spreadsheets. That could make for super-quick work.

      Thanks again for a good read.

    2. Cara Pettersen cara says:

      Great tips Jo! Thank you!

      @Zach, if you automate that, please share :)

    3. Thanks, guys! Zach, definitely share with us if you come up with making it even speedier. Better to spend our time on analysis and optimization than this kind of stuff!

    4. Maybe an improvement would be to set up a macro that inserts different proxy servers so that you don’t hammer Google or Bing from a single location.

    5. Oliver Mason says:

      Hey Joanna,

      SEOTools for Excel (http://nielsbosma.se/projects/seotools/download/) has an inbuilt function for pulling the “site:” figures from Google. Screenshot – https://twitter.com/ohgm/status/332431067137064960/photo/1

      This won’t help you for Bing (you could use XPATH for that), but can save time if you have a few hundred domains to run through. Can handle inconsistently formed data, too (non http, non www etc).

      Copy URLS looks really useful

      Thanks!

    6. @Michael, yes; I would think if you needed to do a ton of these, going the proxy route would be smart.

      @Oliver, I’m sure SEOTools is rad! I’ll be sure to check it out if I ever go back to a PC ;). Copy URLs is a sweet little tool. I don’t use it every day, but I find it’s frequently helpful to speed up workflow.