Excel has always been a favorite tool amongst SEOs, but now there’s a new, potentially more powerful kid on the block: Google Sheets. I’m sure most of you are somewhat familiar with Sheets (if not, it’s basically just like Excel, but cloud-based and completely free) and know just how powerful it can be when it comes to collaboration. But, its capabilities reach far beyond collaboration. Google Sheets can be used to scrape data from websites, create semi-automated SEO workflows, manipulate big data sets (e.g. a Site Explorer export), automate follow-ups for outreach campaigns, and much more.
In this post
I’ll introduce you to 10 formulas and show. How you can use them for everyday SEO tasks. OK, so if you only ever work with a tiny amount. Of data, feel free to company data skip straight to the next section. But, given the fact that this guide is for SEO’s, I’m going to assume that you’re working with reasonably large amounts of data on a regular basis. If this is the case, I’d hazard a guess that you spend far too much time dragging formulas down across hundreds, possibly even thousands, of cells. Enter: ARRAYFORMULA.
Merging data from multiple sources (e.g. merging a list of domains with corresponding Ahrefs DR ratings from a separate sheet); Checking if a value exists in another data set (e.g. checking for duplicates across two or more lists of outreach prospects); Pulling in email addresses (from a master database of contacts) alongside a list of prospects.
Let’s assume we have
A list of outreach prospects (i.e. a bunch of people linking to a competitor’s website, pulled from Site Explorer). We also have a master database of contact DT Leads information (i.e. email addresses) in another spreadsheet. Query a master link prospect database for specific prospects e.g. find. Only prospects tagged as guest post opportunities, with a DR. Of above 50, and contact details present Create super-granular. Client-facing documents that pull in data from a “master” spreadsheet. Query a massive on-site audit to pluck out only the pages that need attention. Google Sheets is insanely powerful; this post. Only scratches the surface of what you can do with it.