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.
Join the Discussion