{"id":16122,"date":"2017-06-20T01:34:01","date_gmt":"2017-06-20T09:34:01","guid":{"rendered":"https:\/\/ahrefs.com\/blog\/?p=16122"},"modified":"2021-06-29T12:44:31","modified_gmt":"2021-06-29T17:44:31","slug":"google-sheets-formulas-seo","status":"publish","type":"post","link":"https:\/\/ahrefs.com\/blog\/google-sheets-formulas-seo\/","title":{"rendered":"10 Google Sheets Formulas Every SEO Should Know"},"content":{"rendered":"<div class=\"intro-txt\"> Excel has always been a favorite tool amongst SEOs, but now there\u2019s a new, potentially more powerful kid on the block: <a href=\"https:\/\/www.google.com\/sheets\/about\/\" target=\"_blank\" rel=\"noopener noreferrer\">Google Sheets<\/a>. <\/div>\n<p>I\u2019m sure most of you are somewhat familiar with Sheets (if not, it\u2019s basically just like Excel, but cloud-based and completely free) and know just how powerful it can be when it comes to collaboration.<\/p>\n<p>But, its capabilities reach <i>far<\/i> beyond collaboration.<\/p>\n<p>Google Sheets can be used to scrape data from websites, create semi-automated SEO workflows, manipulate big data sets (e.g. a <a href=\"https:\/\/ahrefs.com\/site-explorer\">Site Explorer<\/a> export), automate follow-ups for outreach campaigns, and much&nbsp;more.<\/p>\n<p>In this post, I\u2019ll introduce you to 10 Google Sheets formulas and show how you can use them for everyday SEO&nbsp;tasks.<\/p>\n<h2>Let\u2019s start with the basics\u2026<\/h2>\n<p>In this short section, I\u2019m going to share three basic must-know formulas.<\/p>\n<p>No matter what kind of SEO work I\u2019m doing in Google Sheets, I find myself using these three formulas (almost) every&nbsp;time:<\/p>\n<ul>\n<li>IF;<\/li>\n<li>IFERROR;<\/li>\n<li>ARRAYFORMULA<\/li>\n<\/ul>\n<p>Let\u2019s start with an IF statement.<\/p>\n<p>This is super simple; it\u2019s used to check <i>if<\/i> a condition is true or&nbsp;false.<\/p>\n<p><strong>Syntax:<\/strong> <code>=IF(condition, value_if_true, value_if_false)<\/code><\/p>\n<p>Here\u2019s an example spreadsheet containing a list of keywords with their respective estimated search volumes (<strong>note:<\/strong> these were gathered using <a href=\"https:\/\/ahrefs.com\/keywords-explorer\">Keyword Explorer<\/a>):<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-16135\" src=\"https:\/\/ahrefs.com\/blog\/wp-content\/uploads\/2017\/06\/paleo-keyword-explorer-export.png\" alt width=\"900\" height=\"446\" srcset=\"https:\/\/ahrefs.com\/blog\/wp-content\/uploads\/2017\/06\/paleo-keyword-explorer-export.png 900w, https:\/\/ahrefs.com\/blog\/wp-content\/uploads\/2017\/06\/paleo-keyword-explorer-export-768x381.png 768w, https:\/\/ahrefs.com\/blog\/wp-content\/uploads\/2017\/06\/paleo-keyword-explorer-export-680x337.png 680w\" sizes=\"auto, (max-width: 900px) 100vw, 900px\"><\/p>\n<p>Let\u2019s assume, hypothetically, that we have a strong site capable of ranking #1 for any of these keywords. However, we only want to go after keywords that are likely to bring in 500+ visitors per month (assuming we have a #1 ranking).<\/p>\n<p>According to <a href=\"https:\/\/www.advancedwebranking.com\/cloud\/ctrstudy\/\" target=\"_blank\" rel=\"noopener noreferrer\">this study<\/a>, #1 rankings in the US (desktop searches only) have a 29% CTR, roughly.<\/p>\n<div class=\"sidenote\"><div class=\"sidenote-title\">Sidenote.<\/div> We don\u2019t recommend relying on this methodology to estimate search traffic as CTR varies greatly across different queries, devices, and so forth. That\u2019s why <a href=\"https:\/\/ahrefs.com\/blog\/website-traffic\/\">we use clickstream data to estimate traffic<\/a>. <\/div>\n<p>So, let\u2019s write an IF statement that will return \u201cGOOD\u201d for keywords that are likely to bring 500+ visitors (i.e. those where 29% of the search volume is bigger than or equal to 500) and \u201cBAD\u201d for the&nbsp;rest.<\/p>\n<p>Here\u2019s the formula:<\/p>\n<p><code>=IF(B2*0.29&gt;=500,\"GOOD\",\"BAD\")<\/code><\/p>\n<p><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/ahrefs.com\/blog\/wp-content\/uploads\/2017\/06\/1-if-formula-gif-3.gif\" alt width=\"898\" height=\"306\" class=\"alignnone size-full wp-image-39200\"><\/p>\n<p>Here\u2019s what this does (in plain English):<\/p>\n<ol>\n<li>It checks to see <em>if<\/em> B2*0.29 (i.e. 29% of the search volume) is bigger than or equal to&nbsp;500;<\/li>\n<li><em>If<\/em> the <i>condition<\/i> is <em>true<\/em>, it returns \u201cGOOD\u201d. <em>If<\/em> it\u2019s <em>false<\/em>, it returns \u201cBAD\u201d.<\/li>\n<\/ol>\n<p>This works very well for our current data set, but look what happens when we throw some non-numerical values into the&nbsp;mix:<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone wp-image-16144 size-full\" src=\"https:\/\/ahrefs.com\/blog\/wp-content\/uploads\/2017\/06\/non-numerical-data.png\" alt width=\"900\" height=\"356\" srcset=\"https:\/\/ahrefs.com\/blog\/wp-content\/uploads\/2017\/06\/non-numerical-data.png 900w, https:\/\/ahrefs.com\/blog\/wp-content\/uploads\/2017\/06\/non-numerical-data-768x304.png 768w, https:\/\/ahrefs.com\/blog\/wp-content\/uploads\/2017\/06\/non-numerical-data-680x269.png 680w\" sizes=\"auto, (max-width: 900px) 100vw, 900px\"><\/p>\n<p>That\u2019s an&nbsp;error.<\/p>\n<p>This happens because it\u2019s impossible to multiply a non-numerical value by 0.29 (obviously).<\/p>\n<div class=\"sidenote\"><div class=\"sidenote-title\">Sidenote.<\/div> I\u2019ve added some conditional formatting so wherever the IF statement evaluates to TRUE, the cells are highlighted green. If the statement evaluates to FALSE, they\u2019re highlighted red.&nbsp;<\/div>\n<p>This is where <strong>IFERROR<\/strong> comes in&nbsp;handy.<\/p>\n<p>IFERROR allows you to set a default value should the formula result in an&nbsp;error.<\/p>\n<p><strong>Syntax:<\/strong>&nbsp;<code>=IFERROR(original_formula, value_if_error)<\/code><\/p>\n<p>Let\u2019s incorporate this into the example above (we\u2019ll leave the cell blank if there\u2019s an error) and see what happens:<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/ahrefs.com\/blog\/wp-content\/uploads\/2017\/06\/2-iferror-gif-1.gif\" alt width=\"900\" height=\"306\" class=\"alignnone size-full wp-image-39202\"><\/p>\n<p>Perfect - that\u2019s the formula complete!<\/p>\n<p>OK, so if you only ever work with a tiny amount of data, feel free to skip straight to the next section.<\/p>\n<p>But, given the fact that this guide is for SEO\u2019s, I\u2019m going to assume that you\u2019re working with reasonably large amounts of data on a regular basis.<\/p>\n<p>If this is the case, I\u2019d hazard a guess that you spend far too much time dragging formulas down across hundreds, possibly even thousands, of&nbsp;cells.<\/p>\n<p>Enter: <strong>ARRAYFORMULA<\/strong>.<\/p>\n<p><strong>Syntax:<\/strong> <code>=ARRAYFORMULA(array_formula)<\/code><\/p>\n<p>Basically, an ARRAYFORMULA converts your original formula into an array, thus allowing you to iterate the same formula across multiple rows by writing only a single formula.<\/p>\n<p>So, let\u2019s delete all formulas in cells B2 onwards, and wrap the entire formula currently in cell B1 in an ARRAYFORMULA, like&nbsp;so:<\/p>\n<p><code>=ARRAYFORMULA(IFERROR(IF(B2:B29*0.29&gt;=500,\"GOOD\",\"BAD\"),\"\"))<\/code><\/p>\n<p><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/ahrefs.com\/blog\/wp-content\/uploads\/2017\/06\/3-arrayformula-gif-1.gif\" alt width=\"898\" height=\"422\" class=\"alignnone size-full wp-image-39204\"><\/p>\n<p>Magic.<\/p>\n<p>That\u2019s the basics covered; let\u2019s take a look at some more useful formulas.<\/p>\n<div class=\"sidenote\"><div class=\"sidenote-title\">Sidenote.<\/div> Here\u2019s <a href=\"https:\/\/docs.google.com\/spreadsheets\/d\/1IMOYOqDY8mli8ctp66FpS8a-UK0n9c387EQukXAz9qw\/edit?usp=sharing\" target=\"_blank\" rel=\"noopener noreferrer\">a spreadsheet<\/a>&nbsp;showing how each of these formulas work (<strong>note:<\/strong> cells containing formulas will be highlighted yellow). I\u2019ll be including more of these spreadsheets throughout the&nbsp;post.&nbsp;<\/div>\n<h2>1. Use REGEXTRACT to extract data from strings<\/h2>\n<p>REGEXTRACT uses regular expressions to extract <i>substrings<\/i> from a <i>string<\/i> or <i>cell<\/i>.<\/p>\n<p><strong>Syntax:<\/strong> <code>=REGEXEXTRACT(text, regular_expression)<\/code><\/p>\n<p>Here are just a handful of potential use cases for&nbsp;this:<\/p>\n<ul>\n<li>Extract domain names from a list of URLs (keep reading to see an example!);<\/li>\n<li>Extract the URL (i.e. without the root domain);<\/li>\n<li>Check if URL uses HTTP or&nbsp;HTTPS;<\/li>\n<li>Extract email addresses from a big chunk of&nbsp;text;<\/li>\n<li>Identify URLs with\/without certain words in them from a list of URLs (e.g. URLs containing the \u201c\/category\/guest-post\u201d slug).<\/li>\n<\/ul>\n<p>Let\u2019s assume we want to extract the root domains from a list of \u201cwrite for us\u201d page URLs (i.e. guest post opportunities).<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone wp-image-16152 size-full\" src=\"https:\/\/ahrefs.com\/blog\/wp-content\/uploads\/2017\/06\/guest-post-opportunities.png\" alt width=\"660\" height=\"312\"><\/p>\n<p>In column B, we can write a REGEXTRACT formula to do&nbsp;this.<\/p>\n<p>Here is the regex syntax we need: <code>^(?:https?:\\\/\\\/)?(?:[^@\\n]+@)?(?:www\\.)?([^:\\\/\\n]+)<\/code><\/p>\n<div class=\"sidenote\"><div class=\"sidenote-title\">Sidenote.<\/div> If you\u2019re not familiar with regex (don\u2019t worry, I\u2019m not great at it either), you have two options: (i) Learn the basics \u2014 check out <a href=\"http:\/\/regexr.com\/\" target=\"_blank\" rel=\"noopener noreferrer\">Regexr.com<\/a>&nbsp;(ii) Google the solution for whatever you need \u2014 seriously, it\u2019s surprising what you can find with a bit of Googling!&nbsp;<\/div>\n<p>Here\u2019s our final formula:<\/p>\n<p><code>=REGEXEXTRACT(A2,\"^(?:https?:\\\/\\\/)?(?:[^@\\n]+@)?(?:www\\.)?([^:\\\/\\n]+)\")<\/code><\/p>\n<p>Paste this into cell B2 and, hey presto, we\u2019ve extracted the domain.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-16170\" src=\"https:\/\/ahrefs.com\/blog\/wp-content\/uploads\/2017\/06\/regextract-formula.jpg\" alt width=\"900\" height=\"327\" srcset=\"https:\/\/ahrefs.com\/blog\/wp-content\/uploads\/2017\/06\/regextract-formula.jpg 900w, https:\/\/ahrefs.com\/blog\/wp-content\/uploads\/2017\/06\/regextract-formula-768x279.jpg 768w, https:\/\/ahrefs.com\/blog\/wp-content\/uploads\/2017\/06\/regextract-formula-680x247.jpg 680w\" sizes=\"auto, (max-width: 900px) 100vw, 900px\"><\/p>\n<p>Let\u2019s wrap this in an ARRAYFORMULA and IFERROR to complete the entire column.<\/p>\n<p><code>=IFERROR(ARRAYFORMULA(REGEXEXTRACT(A2:A,\"^(?:https?:\\\/\\\/)?(?:[^@\\n]+@)?(?:www\\.)?([^:\\\/\\n]+)\")),\"\")<\/code><\/p>\n<p><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/ahrefs.com\/blog\/wp-content\/uploads\/2017\/06\/4-regextract-arrayformula.gif\" alt width=\"900\" height=\"422\" class=\"alignnone size-full wp-image-39206\"><\/p>\n<div class=\"sidenote\"><div class=\"sidenote-title\">Sidenote.<\/div> Here\u2019s <a href=\"https:\/\/docs.google.com\/spreadsheets\/d\/1mjv2ePMi3EdhPFasI2wdEp9SsBSeOaL0D6BnCAaVEmg\/edit?usp=sharing\" target=\"_blank\" rel=\"noopener noreferrer\">the spreadsheet<\/a>&nbsp;showing how this formula works.&nbsp;<\/div>\n<h2>2. SPLIT strings into multiple data points<\/h2>\n<p>SPLIT divides (i.e. splits) strings into <i>fragments<\/i> using a <i>delimiter<\/i>.<\/p>\n<p><strong>Syntax:<\/strong> <code>=SPLIT(text, delimiter)<\/code><\/p>\n<p>Here are just a handful of potential use cases for&nbsp;this:<\/p>\n<ul>\n<li>Split a prospect\u2019s full name into \u201cfirst name\u201d and \u201clast name\u201d columns;<\/li>\n<li>Split a URL into 3 columns for <i>HTTP protocol<\/i>, <i>root domain<\/i>, and <i>URL slug<\/i>;<\/li>\n<li>Split a list of comma-separated values into multiple columns;<\/li>\n<li>Split a root domain into 2 columns for <i>domain name<\/i> and <i>domain extension<\/i> (e.g. .com, .org,&nbsp;etc.)<\/li>\n<\/ul>\n<p>I have a nice list of <a href=\"https:\/\/ahrefs.com\/team\" target=\"_blank\" rel=\"noopener noreferrer\">Ahrefs\u2019 team members<\/a>&nbsp;(full names) in a spreadsheet.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-16177\" src=\"https:\/\/ahrefs.com\/blog\/wp-content\/uploads\/2017\/06\/ahrefs-team-members.jpg\" alt width=\"900\" height=\"483\" srcset=\"https:\/\/ahrefs.com\/blog\/wp-content\/uploads\/2017\/06\/ahrefs-team-members.jpg 900w, https:\/\/ahrefs.com\/blog\/wp-content\/uploads\/2017\/06\/ahrefs-team-members-768x412.jpg 768w, https:\/\/ahrefs.com\/blog\/wp-content\/uploads\/2017\/06\/ahrefs-team-members-680x365.jpg 680w\" sizes=\"auto, (max-width: 900px) 100vw, 900px\"><\/p>\n<p>Here\u2019s a simple SPLIT formula we could use in cell B2 to divide these into <em>first name<\/em> and <em>last name<\/em>:<\/p>\n<p><code>=SPLIT(A2,\" \")<\/code><\/p>\n<p><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/ahrefs.com\/blog\/wp-content\/uploads\/2017\/06\/5split-gif.gif\" alt width=\"900\" height=\"226\" class=\"alignnone size-full wp-image-39208\"><\/p>\n<div class=\"sidenote\"><div class=\"sidenote-title\">Sidenote.<\/div> We\u2019re using a space (i.e. \u201d \u201d) as our delimiter as this tells the SPLIT formula where to split the string.&nbsp;<\/div>\n<p>Again, let\u2019s wrap this in an IFERROR and ARRAYFORMULA to <i>split<\/i> the entire list with a single formula.<\/p>\n<p><code>=IFERROR(ARRAYFORMULA(SPLIT(A2:A,\" \")),\"\")<\/code><\/p>\n<p><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/ahrefs.com\/blog\/wp-content\/uploads\/2017\/06\/6-split-arrayformula.gif\" alt width=\"900\" height=\"396\" class=\"alignnone size-full wp-image-39210\"><\/p>\n<p>Here\u2019s another example formula that will&nbsp;<em>split<\/em>&nbsp;root domains into <i>site name<\/i> and <i>domain extension<\/i>:<\/p>\n<p><code>=SPLIT(A2,\".\")<\/code><\/p>\n<p><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/ahrefs.com\/blog\/wp-content\/uploads\/2017\/06\/7-split2-arrayformula.gif\" alt width=\"900\" height=\"396\" class=\"alignnone size-full wp-image-39212\"><\/p>\n<div class=\"sidenote\"><div class=\"sidenote-title\">Sidenote.<\/div> Here\u2019s <a href=\"https:\/\/docs.google.com\/spreadsheets\/d\/1y6EqrKJxKMJThDkHYuszJiE8rdkQD7ymkaiXlMbFmfc\/edit?usp=sharing\" target=\"_blank\" rel=\"noopener noreferrer\">the spreadsheet<\/a>. <\/div>\n<h2>3. Merge multiple data sets using VLOOKUP<\/h2>\n<p>VLOOKUP allows you to search a <i>range<\/i> using a <i>search key\u2014<\/i>you can then return matching values from a specific <i>cell<\/i> in said <i>range<\/i>.<\/p>\n<p><strong>Syntax:<\/strong> <code>=VLOOKUP(search_key, range, index_key)<\/code><\/p>\n<p>Here are just a handful of potential use cases for&nbsp;this:<\/p>\n<ul>\n<li>Merging data from multiple sources (e.g. merging a list of domains with corresponding Ahrefs DR ratings from a separate sheet);<\/li>\n<li>Checking if a value exists in another data set (e.g. checking for duplicates across two or more lists of outreach prospects);<\/li>\n<li>Pulling in email addresses (from a master database of contacts) alongside a list of prospects.<\/li>\n<\/ul>\n<p>Let\u2019s assume we have a list of outreach prospects (i.e. a bunch of people linking to a competitor\u2019s website, pulled from <a href=\"https:\/\/ahrefs.com\/site-explorer\" target=\"_blank\" rel=\"noopener noreferrer\">Site Explorer<\/a>). We also have a master database of contact information (i.e. email addresses) in another spreadsheet.<\/p>\n<div id=\"attachment_16185\" style=\"width: 949px\" class=\"wp-caption alignnone\"><img loading=\"lazy\" decoding=\"async\" aria-describedby=\"caption-attachment-16185\" class=\"size-full wp-image-16185\" src=\"https:\/\/ahrefs.com\/blog\/wp-content\/uploads\/2017\/06\/site-explorer-export-data.jpg\" alt width=\"939\" height=\"533\" srcset=\"https:\/\/ahrefs.com\/blog\/wp-content\/uploads\/2017\/06\/site-explorer-export-data.jpg 939w, https:\/\/ahrefs.com\/blog\/wp-content\/uploads\/2017\/06\/site-explorer-export-data-768x436.jpg 768w, https:\/\/ahrefs.com\/blog\/wp-content\/uploads\/2017\/06\/site-explorer-export-data-680x386.jpg 680w\" sizes=\"auto, (max-width: 939px) 100vw, 939px\"><p id=\"caption-attachment-16185\" class=\"wp-caption-text\">Site Explorer export (note: I removed many of the columns here, as much of the data isn\u2019t needed for this example).<\/p><\/div>\n<div id=\"attachment_16184\" style=\"width: 911px\" class=\"wp-caption alignnone\"><img loading=\"lazy\" decoding=\"async\" aria-describedby=\"caption-attachment-16184\" class=\"size-full wp-image-16184\" src=\"https:\/\/ahrefs.com\/blog\/wp-content\/uploads\/2017\/06\/master-contact-database.jpg\" alt width=\"901\" height=\"316\" srcset=\"https:\/\/ahrefs.com\/blog\/wp-content\/uploads\/2017\/06\/master-contact-database.jpg 901w, https:\/\/ahrefs.com\/blog\/wp-content\/uploads\/2017\/06\/master-contact-database-768x269.jpg 768w, https:\/\/ahrefs.com\/blog\/wp-content\/uploads\/2017\/06\/master-contact-database-680x238.jpg 680w\" sizes=\"auto, (max-width: 901px) 100vw, 901px\"><p id=\"caption-attachment-16184\" class=\"wp-caption-text\">Master contact database \u2014 this is the database we\u2019ll be querying using a VLOOKUP function.<\/p><\/div>\n<div class=\"sidenote\"><div class=\"sidenote-title\">Sidenote.<\/div> I added two new (empty) columns for the VLOOKUP data (i.e. full name and email) to the Site Explorer export sheet. This will be shown in the next few screenshots.&nbsp;<\/div>\n<p>We don\u2019t want to waste time looking for contact information that we already have, so let\u2019s use VLOOKUP to query the master database and see if we already have contact info for any of these prospects.<\/p>\n<p>Here\u2019s the formula we\u2019re going to&nbsp;use:<\/p>\n<p><code>=VLOOKUP(D2:D,'Master contact database'!A:C,2)<\/code><\/p>\n<p><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/ahrefs.com\/blog\/wp-content\/uploads\/2017\/06\/8-vlookup-query.gif\" alt width=\"900\" height=\"214\" class=\"alignnone size-full wp-image-39214\"><\/p>\n<p>OK, let\u2019s do the same for the <i>email<\/i> column; we\u2019ll also wrap both formulas in an IFERROR and ARRAYFORMULA.<\/p>\n<p><code>=IFERROR(ARRAYFORMULA(VLOOKUP(D2:D,'Master contact database'!A:C,3)),\"\")<\/code><\/p>\n<p><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/ahrefs.com\/blog\/wp-content\/uploads\/2017\/06\/9-vlookup-arrayformula.gif\" alt width=\"900\" height=\"274\" class=\"alignnone size-full wp-image-39216\"><\/p>\n<div class=\"sidenote\"><div class=\"sidenote-title\">Sidenote.<\/div> <a href=\"https:\/\/docs.google.com\/spreadsheets\/d\/1ws40EwmY7qOWhHNdWWXCCXLdUpy4cwKXVcKUFiBTv1o\/edit?usp=sharing\" target=\"_blank\" rel=\"noopener noreferrer\">Here\u2019s the spreadsheet<\/a>. <\/div>\n<h2>4. Scrape data from any website using IMPORTXML<\/h2>\n<p>IMPORTXML lets you import data (using an XPath query) from a number of structured data types including XML, HTML, and RSS (amongst <a href=\"https:\/\/support.google.com\/docs\/answer\/3093342?hl=en\" target=\"_blank\" rel=\"noopener noreferrer\">others<\/a>).<\/p>\n<p>In other words, you can scrape the web without ever leaving Google Sheets!<\/p>\n<p><strong>Syntax: <\/strong><code>=IMPORTXML(url, xpath_query)<\/code><\/p>\n<p>Here are just a handful of potential use cases for&nbsp;this:<\/p>\n<ul>\n<li>Scraping metadata from a list of URLs (e.g. title, description, h-tags, etc);<\/li>\n<li>Scraping email addresses from web&nbsp;pages;<\/li>\n<li>Scraping social profiles (e.g. Facebook) from web&nbsp;pages;<\/li>\n<li>Scraping lastBuildDate from RSS feeds (this is a really sneaky way to see how recently the site was updated without even having to load the website!)<\/li>\n<\/ul>\n<p>Let\u2019s assume that we wanted to grab the meta title for <a href=\"https:\/\/ahrefs.com\/seo\/keyword-research\" data-ahr=\"https:\/\/ahrefs.com\/blog\/keyword-research\/\" target=\"_blank\" rel=\"noopener noreferrer\">our post about keyword research<\/a>.<\/p>\n<p>We can see in the HTML that the meta title reads: \u201cHow To Do Keyword Research in 2017 - Ahrefs\u2019 Guide\u201d.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-16193\" src=\"https:\/\/ahrefs.com\/blog\/wp-content\/uploads\/2017\/06\/title-tag.jpg\" alt width=\"901\" height=\"305\" srcset=\"https:\/\/ahrefs.com\/blog\/wp-content\/uploads\/2017\/06\/title-tag.jpg 901w, https:\/\/ahrefs.com\/blog\/wp-content\/uploads\/2017\/06\/title-tag-768x260.jpg 768w, https:\/\/ahrefs.com\/blog\/wp-content\/uploads\/2017\/06\/title-tag-680x230.jpg 680w, https:\/\/ahrefs.com\/blog\/wp-content\/uploads\/2017\/06\/title-tag-650x220.jpg 650w\" sizes=\"auto, (max-width: 901px) 100vw, 901px\"><\/p>\n<p>The XPath query we use to grab the meta title is quite simply: \u201c\/\/title\u201d<\/p>\n<p>Here\u2019s the formula:<\/p>\n<p><code>=IMPORTXML(\"https:\/\/ahrefs.com\/blog\/keyword-research\/\",\"\/\/title\")<\/code><\/p>\n<p>It\u2019s also possible to use cell references in the formula; this makes scraping data for a bunch of URLs super simple.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/ahrefs.com\/blog\/wp-content\/uploads\/2017\/06\/10-importxml-formula.gif\" alt width=\"898\" height=\"274\" class=\"alignnone size-full wp-image-39218\"><\/p>\n<div class=\"sidenote\"><div class=\"sidenote-title\">Sidenote.<\/div> Unfortunately, IMPORTXML does not work with an ARRAYFORMULA, so it\u2019ll be a case of manually dragging this one&nbsp;down.&nbsp;<\/div>\n<p>IMPORTXML isn\u2019t limited to scraping basic meta tags, either; it can be used to scrape virtually anything. It\u2019s just a case of knowing the&nbsp;XPath.<\/p>\n<p>Here are a few potentially useful XPath formulas:<\/p>\n<ul>\n<li><strong>Extract all links on a page:<\/strong> <code>\"\/\/@href\"<\/code>;<\/li>\n<li><strong>Extract all internal links on a page:<\/strong> <code>\"\/\/a[contains(@href, 'domain.com')]\/@href\"<\/code>;<\/li>\n<li><strong>Extract all external links on a page:<\/strong> <code>\"\/\/a[not(contains(@href, 'domain.com'))]\/@href\"<\/code>;<\/li>\n<li><strong>Extract meta description:<\/strong> <code>\"\/\/meta[@name='description']\/@content\"<\/code>;<\/li>\n<li><strong>Extract H1:<\/strong> <code>\"\/\/h1\"<\/code>;<\/li>\n<li><strong>Extract email address(es) from page:<\/strong> <code>\"\/\/a[contains(@href, 'mailTo:') or contains(@href, 'mailto:')]\/@href\"<\/code>;<\/li>\n<li><strong>Extract social profiles (i.e. LinkedIn, Facebook, Twitter):<\/strong> <code>\"\/\/a[contains(@href, 'linkedin.com\/in') or contains(@href, 'twitter.com\/') or contains(@href, 'facebook.com\/')]\/@href\"<\/code>;<\/li>\n<li><strong>Extract lastBuildDate (from RSS feed):<\/strong> <code>\"\/\/lastBuildDate\"<\/code><\/li>\n<\/ul>\n<p>You can find the XPath for any element by doing the following (in Chrome):<\/p>\n<blockquote><p>Right-click &gt; Inspect &gt; Right-Click &gt; Copy &gt; Copy&nbsp;XPath<\/p><\/blockquote>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-medium wp-image-16196\" src=\"https:\/\/ahrefs.com\/blog\/wp-content\/uploads\/2017\/06\/copy-xpath.jpg\" alt width=\"900\" height=\"392\" srcset=\"https:\/\/ahrefs.com\/blog\/wp-content\/uploads\/2017\/06\/copy-xpath.jpg 900w, https:\/\/ahrefs.com\/blog\/wp-content\/uploads\/2017\/06\/copy-xpath-768x335.jpg 768w, https:\/\/ahrefs.com\/blog\/wp-content\/uploads\/2017\/06\/copy-xpath-680x296.jpg 680w\" sizes=\"auto, (max-width: 900px) 100vw, 900px\"><\/p>\n<div class=\"sidenote\"><div class=\"sidenote-title\">Sidenote.<\/div> <a href=\"https:\/\/docs.google.com\/spreadsheets\/d\/1hDTLgb7AYrLgT2vVCi7fzceC-p8vaii5OUTLVF4mpck\/edit?usp=sharing\" target=\"_blank\" rel=\"noopener noreferrer\">Here\u2019s the spreadsheet<\/a> (tons of examples in this one :D) <\/div>\n<h2>5. SEARCH strings for certain values<\/h2>\n<p>SEARCH lets you check whether or not a&nbsp;<i>value<\/i> exists in a <i>string<\/i>; it then returns the position at which the&nbsp;<i>value<\/i> is first found in the <i>string<\/i>.<\/p>\n<p><strong>Syntax: <\/strong><code>=SEARCH(search_query, text_to_search)<\/code><\/p>\n<p>Here are some use&nbsp;cases:<\/p>\n<ul>\n<li>Check if a particular subdomain exists in URL (this is useful for bulk categorizing a list of&nbsp;URLs);<\/li>\n<li>Categorize keywords into various intent-based categories (e.g. branded, commercial, etc);<\/li>\n<li>Search for specific, undesirable characters within a&nbsp;URL;<\/li>\n<li>Search for certain words\/phrases within a URL to categorize link prospects (e.g. \u201c\/category\/guest-post\u201d, \u201cresources.html\u201d, etc)<\/li>\n<\/ul>\n<p>Let\u2019s take a look at an example of SEARCH in action.<\/p>\n<p>Here is a list of the top 300+ pages on Ahrefs.com (<strong>note:<\/strong> I used <a href=\"https:\/\/ahrefs.com\/site-explorer\">Site Explorer<\/a> to gather this&nbsp;data):<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-16198\" src=\"https:\/\/ahrefs.com\/blog\/wp-content\/uploads\/2017\/06\/ahrefs-urls.jpg\" alt width=\"900\" height=\"579\" srcset=\"https:\/\/ahrefs.com\/blog\/wp-content\/uploads\/2017\/06\/ahrefs-urls.jpg 900w, https:\/\/ahrefs.com\/blog\/wp-content\/uploads\/2017\/06\/ahrefs-urls-260x166.jpg 260w, https:\/\/ahrefs.com\/blog\/wp-content\/uploads\/2017\/06\/ahrefs-urls-768x494.jpg 768w, https:\/\/ahrefs.com\/blog\/wp-content\/uploads\/2017\/06\/ahrefs-urls-661x425.jpg 661w\" sizes=\"auto, (max-width: 900px) 100vw, 900px\"><\/p>\n<div class=\"sidenote\"><div class=\"sidenote-title\">Sidenote.<\/div> I cleaned up the data in the screenshot above by removing a few columns; Site Explorer actually gives you much more information than this (e.g. top keyword for each URL, traffic volume, search volume, position, etc)&nbsp;<\/div>\n<p>All of the pages with \/blog\/ in the URL are blog posts. Let\u2019s say that I wanted to tag each of these pages as \u201cBlog post\u201d during a <a href=\"https:\/\/ahrefs.com\/blog\/seo-audit\/\">content audit<\/a>.<\/p>\n<p>SEARCH (combined with an IF statement - this was discussed earlier in the guide) can do this in seconds; here\u2019s the formula:<\/p>\n<p><code>=IF(SEARCH(\"\/blog\/\",A2),\"YES\",\"\")<\/code><\/p>\n<p><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/ahrefs.com\/blog\/wp-content\/uploads\/2017\/06\/11-search.gif\" alt width=\"896\" height=\"274\" class=\"alignnone size-full wp-image-39220\"><\/p>\n<p>Let\u2019s wrap it in an IFERROR and ARRAYFORMULA to neaten things up.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/ahrefs.com\/blog\/wp-content\/uploads\/2017\/06\/12-search-arrayformula.gif\" alt width=\"896\" height=\"274\" class=\"alignnone size-full wp-image-39222\"><\/p>\n<p>Here are a few other useful formulas:<\/p>\n<ul>\n<li><strong>Find \u201cwrite for us\u201d pages in a list of URLs:<\/strong> <code>=IF(SEARCH(\"\/write-for-us\/\",A2),\"Write for us page\",\"\")<\/code>;<\/li>\n<li><strong>Find resource pages in a list of URLs:<\/strong> <code>=IF(SEARCH(\"\/resources.html\",A2),\"Resource page\",\"\")<\/code>;<\/li>\n<li><strong>Find branded search terms (in a list of keywords):<\/strong> <code>=IF(SEARCH(\"brand_name\",A2),\"Branded keyword\",\"\")<\/code>;<\/li>\n<li><strong>Identify internal\/external links (from a list of outbound links): <\/strong><code>=IF(SEARCH(\"yourdomain.com\",A2),\"Internal Link\",\"External Link\")<\/code>;<\/li>\n<\/ul>\n<div class=\"sidenote\"><div class=\"sidenote-title\">Sidenote.<\/div> <a href=\"https:\/\/docs.google.com\/spreadsheets\/d\/1HPXnzkHkoTsIcXQTgPf2oYrH80nGWc-SC4GMUALATL4\/edit?usp=sharing\" target=\"_blank\" rel=\"noopener noreferrer\">Here\u2019s the spreadsheet<\/a> (a few examples in this one,&nbsp;too!)&nbsp;<\/div>\n<h2>6. Import data from other spreadsheets using IMPORTRANGE<\/h2>\n<p>IMPORTRANGE allows you to import data from any other Google Sheet.<\/p>\n<p>It doesn\u2019t have to be on your Google Drive, either; it could belong to someone else (note: you will need permission to access the sheet if this is the&nbsp;case!)<\/p>\n<p><strong>Syntax: <\/strong><code>=IMPORTRANGE(spreadsheet_ID, range_to_import)<\/code><\/p>\n<p>Here are a few use&nbsp;cases:<\/p>\n<ul>\n<li>Create client-facing sheets that piggyback off your \u201cmaster\u201d spreadsheet;<\/li>\n<li>Search and cross reference data across multiple Google Sheets (i.e. using IMPORTRANGE combined with VLOOKUPs);<\/li>\n<li>Pull in data from another sheet for use in a data validation;<\/li>\n<li>Pull in contact data from a \u201cmaster\u201d spreadsheet using VLOOKUPs<\/li>\n<\/ul>\n<p>Let\u2019s take a look at an example of IMPORTRANGE in action.<\/p>\n<p><a href=\"https:\/\/docs.google.com\/spreadsheets\/d\/1YW0WyTDPsfG2mE0q7KZ8e1gZfIjV5HUob16G7QFoeh0\/edit?usp=sharing\" target=\"_blank\" rel=\"noopener noreferrer\">Here is a sheet<\/a> with a list of hypothetical SEO clients + their budgets:<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-16204\" src=\"https:\/\/ahrefs.com\/blog\/wp-content\/uploads\/2017\/06\/seo-budet-spreadsheet.jpg\" alt width=\"901\" height=\"414\" srcset=\"https:\/\/ahrefs.com\/blog\/wp-content\/uploads\/2017\/06\/seo-budet-spreadsheet.jpg 901w, https:\/\/ahrefs.com\/blog\/wp-content\/uploads\/2017\/06\/seo-budet-spreadsheet-768x353.jpg 768w, https:\/\/ahrefs.com\/blog\/wp-content\/uploads\/2017\/06\/seo-budet-spreadsheet-680x312.jpg 680w\" sizes=\"auto, (max-width: 901px) 100vw, 901px\"><\/p>\n<p>Let\u2019s assume that I wanted to use this client list in another Google Sheet \u2014 I can import this entire data range using the following formula:<\/p>\n<p><code>=IMPORTRANGE(\"SPREADSHEET_KEY\",\"'SheetName'!A2:A\")<\/code><\/p>\n<p><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/ahrefs.com\/blog\/wp-content\/uploads\/2017\/06\/13-importrange-data.gif\" alt width=\"892\" height=\"330\" class=\"alignnone size-full wp-image-39224\"><\/p>\n<div class=\"sidenote\"><div class=\"sidenote-title\">Sidenote.<\/div> <a href=\"https:\/\/wiki.mozilla.org\/images\/9\/91\/Google_Spreadsheet_Key_from_url.png\" target=\"_blank\" rel=\"noopener noreferrer\">Here<\/a> is where to find your spreadsheet key.&nbsp;<\/div>\n<p>Let\u2019s also assume that you\u2019re recording links built for these clients in a master spreadsheet; in one column you have the link URL and in the other, you want to record which client the link was&nbsp;for.<\/p>\n<p>You can use IMPORTRANGE to create a dropdown of all clients using a data validation, like&nbsp;so:<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/ahrefs.com\/blog\/wp-content\/uploads\/2017\/06\/14-importrange-dropdown.gif\" alt width=\"900\" height=\"304\" class=\"alignnone size-full wp-image-39226\"><\/p>\n<p>This dropdown will self-update whenever you add\/remove clients from your master spreadsheet.<\/p>\n<div class=\"sidenote\"><div class=\"sidenote-title\">Sidenote.<\/div> <a href=\"https:\/\/docs.google.com\/spreadsheets\/d\/1qnsG8Fm6sIRsDYH21wWWsI7e2-NZT0oGfvxxF813gRY\/edit?usp=sharing\" target=\"_blank\" rel=\"noopener noreferrer\">Here\u2019s the main spreadsheet<\/a> (and <a href=\"https:\/\/docs.google.com\/spreadsheets\/d\/1YW0WyTDPsfG2mE0q7KZ8e1gZfIjV5HUob16G7QFoeh0\/edit?usp=sharing\" target=\"_blank\" rel=\"noopener noreferrer\">the data spreadsheet<\/a>). <\/div>\n<h2>7. QUERY data sets using SQL queries (this one is crazy powerful!)<\/h2>\n<p>QUERY is like VLOOKUP on steroids. It lets you query data using SQL, which allows you to get super-granular when it comes to data querying\/retrieval.<\/p>\n<p><strong>Syntax: <\/strong><code>=QUERY(range, sql_query)<\/code><\/p>\n<p>Here are a few use&nbsp;cases:<\/p>\n<ul>\n<li>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);<\/li>\n<li>Create super-granular client-facing documents that pull in data from a \u201cmaster\u201d spreadsheet;<\/li>\n<li>Query a massive on-site audit to pluck out only the pages that need attention.<\/li>\n<\/ul>\n<p>Let\u2019s go back to our sheet of tagged \u201cblog&nbsp;posts\u201d.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/ahrefs.com\/blog\/wp-content\/uploads\/2017\/06\/15-list-of-blog-posts.gif\" alt width=\"900\" height=\"304\" class=\"alignnone size-full wp-image-39228\"><\/p>\n<p>If we wanted to pull in all of the URLs that were tagged with \u201cblog post\u201d into a brand new spreadsheet, we could use this QUERY function:<\/p>\n<p><code>=QUERY(DATA!A:B,\"select A where B = 'Blog Post'\")<\/code><\/p>\n<p><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/ahrefs.com\/blog\/wp-content\/uploads\/2017\/06\/16-query-blog-post-urls.gif\" alt width=\"896\" height=\"392\" class=\"alignnone size-full wp-image-39230\"><\/p>\n<div class=\"sidenote\"><div class=\"sidenote-title\">Sidenote.<\/div> This tells the spreadsheet to select all the values in column A where column B = \u201cBlog&nbsp;Posts\u201d.&nbsp;<\/div>\n<p>But let\u2019s say that we had a bigger data set. An export file from Site Explorer, perhaps.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-16215\" src=\"https:\/\/ahrefs.com\/blog\/wp-content\/uploads\/2017\/06\/site-explorer-export.jpg\" alt width=\"900\" height=\"420\" srcset=\"https:\/\/ahrefs.com\/blog\/wp-content\/uploads\/2017\/06\/site-explorer-export.jpg 900w, https:\/\/ahrefs.com\/blog\/wp-content\/uploads\/2017\/06\/site-explorer-export-768x358.jpg 768w, https:\/\/ahrefs.com\/blog\/wp-content\/uploads\/2017\/06\/site-explorer-export-680x317.jpg 680w\" sizes=\"auto, (max-width: 900px) 100vw, 900px\"><\/p>\n<p>These export files can be quite data heavy, so let\u2019s assume that we wanted to pull out a list of all referring pages with the following attributes:<\/p>\n<ul>\n<li>Dofollow link;<\/li>\n<li>DR &gt;&nbsp;50;<\/li>\n<li>Backlink status = active (i.e. not tagged as \u201cremoved\u201d);<\/li>\n<li>External links count &lt;&nbsp;50;<\/li>\n<\/ul>\n<p>Here\u2019s the formula:<\/p>\n<p><code>=QUERY('DATA - site explorer export'!A2:R,\"SELECT E where D &gt; 50 AND H &lt; 50 AND M = 'Dofollow' AND N &lt;&gt; 'REMOVED'\")<\/code><\/p>\n<p><strong>NOTE: <\/strong>It\u2019s also possible to incorporate IMPORTRANGE into a QUERY function; this allows you to QUERY data from other sheets.<\/p>\n<div class=\"sidenote\"><div class=\"sidenote-title\">Sidenote.<\/div> <a href=\"https:\/\/docs.google.com\/spreadsheets\/d\/1aj4Ofs99pJ7B-_gPmBvQfuGrVZfFMU0EaXolnchiBSo\/edit?usp=sharing\" target=\"_blank\" rel=\"noopener noreferrer\">Here\u2019s the spreadsheet<\/a>. <\/div>\n<h2>Final thoughts<\/h2>\n<p>Google Sheets is insanely powerful; this post only scratches the surface of what you can do with&nbsp;it.<\/p>\n<p>I\u2019d recommend playing around with the formulas above and seeing what you can come up with. I also recommend checking out <a href=\"https:\/\/support.google.com\/docs\/table\/25273?hl=en\" target=\"_blank\" rel=\"noopener noreferrer\">the full library of Google Sheets formulas<\/a>.<\/p>\n<p>But, that\u2019s still just the beginning: Google Sheets also integrates with <a href=\"https:\/\/zapier.com\/zapbook\/google-sheets\/\" target=\"_blank\" rel=\"noopener noreferrer\">Zapier<\/a> and <a href=\"https:\/\/ifttt.com\/google_drive\" target=\"_blank\" rel=\"noopener noreferrer\">IFTTT<\/a>, which means you can connect with hundreds of other tools and services, too.<\/p>\n<p>And if you want to get really advanced, look into <a href=\"https:\/\/developers.google.com\/apps-script\/\" target=\"_blank\" rel=\"noopener noreferrer\">Apps Script<\/a>\u2014it\u2019s crazy powerful!<\/p>\n<p>If you have any creative uses for Google Sheets of your own, please let me know in the comments. I\u2019d love to hear&nbsp;them!<\/p>\n","protected":false},"excerpt":{"rendered":"<p>I\u2019m sure most of you are somewhat familiar with Sheets (if not, it\u2019s 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<span class=\"ellipsis\">\u2026<\/span><\/p>\n<div class=\"read-more\">Read more \u203a<\/div>\n<p><!-- end of .read-more --><\/p>\n","protected":false},"author":114,"featured_media":16240,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"wp_typography_post_enhancements_disabled":false,"footnotes":""},"categories":[335],"tags":[],"coauthors":[336],"class_list":["post-16122","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-general-seo","odd"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v27.3 - https:\/\/yoast.com\/product\/yoast-seo-wordpress\/ -->\n<title>10 Google Sheets Formulas Every SEO Should Know<\/title>\n<meta name=\"description\" content=\"Google Sheets can be used to scrape data from websites, create semi-automated SEO workflows, manipulate big data sets, automate follow-ups for outreach campaigns, and much more. Here are 10 Google Sheets formulas every SEO should know.\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/ahrefs.com\/blog\/google-sheets-formulas-seo\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"10 Google Sheets Formulas Every SEO Should Know\" \/>\n<meta property=\"og:description\" content=\"Google Sheets + SEO = YES! Here are 10 Google Sheets formulas every SEO should know.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/ahrefs.com\/blog\/google-sheets-formulas-seo\/\" \/>\n<meta property=\"og:site_name\" content=\"SEO Blog by Ahrefs\" \/>\n<meta property=\"article:publisher\" content=\"https:\/\/www.facebook.com\/Ahrefs\/\" \/>\n<meta property=\"article:published_time\" content=\"2017-06-20T09:34:01+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2021-06-29T17:44:31+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/ahrefs.com\/blog\/wp-content\/uploads\/2017\/06\/google-sheets-formulas-seo.png\" \/>\n\t<meta property=\"og:image:width\" content=\"951\" \/>\n\t<meta property=\"og:image:height\" content=\"495\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/png\" \/>\n<meta name=\"author\" content=\"Joshua Hardwick\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@JoshuaCHardwick\" \/>\n<meta name=\"twitter:site\" content=\"@ahrefs\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\\\/\\\/ahrefs.com\\\/blog\\\/google-sheets-formulas-seo\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/ahrefs.com\\\/blog\\\/google-sheets-formulas-seo\\\/\"},\"author\":{\"name\":\"Joshua Hardwick\",\"@id\":\"https:\\\/\\\/ahrefs.com\\\/blog\\\/#\\\/schema\\\/person\\\/e6a89cbde8e750d22996aa26e213e712\"},\"headline\":\"10 Google Sheets Formulas Every SEO Should Know\",\"datePublished\":\"2017-06-20T09:34:01+00:00\",\"dateModified\":\"2021-06-29T17:44:31+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/ahrefs.com\\\/blog\\\/google-sheets-formulas-seo\\\/\"},\"wordCount\":2630,\"commentCount\":77,\"publisher\":{\"@id\":\"https:\\\/\\\/ahrefs.com\\\/blog\\\/#organization\"},\"image\":{\"@id\":\"https:\\\/\\\/ahrefs.com\\\/blog\\\/google-sheets-formulas-seo\\\/#primaryimage\"},\"thumbnailUrl\":\"https:\\\/\\\/ahrefs.com\\\/blog\\\/wp-content\\\/uploads\\\/2017\\\/06\\\/google-sheets-formulas-seo.png\",\"articleSection\":[\"General SEO\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/ahrefs.com\\\/blog\\\/google-sheets-formulas-seo\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/ahrefs.com\\\/blog\\\/google-sheets-formulas-seo\\\/\",\"url\":\"https:\\\/\\\/ahrefs.com\\\/blog\\\/google-sheets-formulas-seo\\\/\",\"name\":\"10 Google Sheets Formulas Every SEO Should Know\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/ahrefs.com\\\/blog\\\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\\\/\\\/ahrefs.com\\\/blog\\\/google-sheets-formulas-seo\\\/#primaryimage\"},\"image\":{\"@id\":\"https:\\\/\\\/ahrefs.com\\\/blog\\\/google-sheets-formulas-seo\\\/#primaryimage\"},\"thumbnailUrl\":\"https:\\\/\\\/ahrefs.com\\\/blog\\\/wp-content\\\/uploads\\\/2017\\\/06\\\/google-sheets-formulas-seo.png\",\"datePublished\":\"2017-06-20T09:34:01+00:00\",\"dateModified\":\"2021-06-29T17:44:31+00:00\",\"description\":\"Google Sheets can be used to scrape data from websites, create semi-automated SEO workflows, manipulate big data sets, automate follow-ups for outreach campaigns, and much more. Here are 10 Google Sheets formulas every SEO should know.\",\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/ahrefs.com\\\/blog\\\/google-sheets-formulas-seo\\\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/ahrefs.com\\\/blog\\\/google-sheets-formulas-seo\\\/#primaryimage\",\"url\":\"https:\\\/\\\/ahrefs.com\\\/blog\\\/wp-content\\\/uploads\\\/2017\\\/06\\\/google-sheets-formulas-seo.png\",\"contentUrl\":\"https:\\\/\\\/ahrefs.com\\\/blog\\\/wp-content\\\/uploads\\\/2017\\\/06\\\/google-sheets-formulas-seo.png\",\"width\":951,\"height\":495},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/ahrefs.com\\\/blog\\\/#website\",\"url\":\"https:\\\/\\\/ahrefs.com\\\/blog\\\/\",\"name\":\"SEO Blog by Ahrefs\",\"description\":\"Link Building Strategies &amp; SEO Tips\",\"publisher\":{\"@id\":\"https:\\\/\\\/ahrefs.com\\\/blog\\\/#organization\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/ahrefs.com\\\/blog\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Organization\",\"@id\":\"https:\\\/\\\/ahrefs.com\\\/blog\\\/#organization\",\"name\":\"Ahrefs\",\"url\":\"https:\\\/\\\/ahrefs.com\\\/blog\\\/\",\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/ahrefs.com\\\/blog\\\/#\\\/schema\\\/logo\\\/image\\\/\",\"url\":\"https:\\\/\\\/ahrefs.com\\\/blog\\\/wp-content\\\/uploads\\\/2023\\\/06\\\/ahrefs-logo.png\",\"contentUrl\":\"https:\\\/\\\/ahrefs.com\\\/blog\\\/wp-content\\\/uploads\\\/2023\\\/06\\\/ahrefs-logo.png\",\"width\":2048,\"height\":768,\"caption\":\"Ahrefs\"},\"image\":{\"@id\":\"https:\\\/\\\/ahrefs.com\\\/blog\\\/#\\\/schema\\\/logo\\\/image\\\/\"},\"sameAs\":[\"https:\\\/\\\/www.facebook.com\\\/Ahrefs\\\/\",\"https:\\\/\\\/x.com\\\/ahrefs\",\"https:\\\/\\\/www.linkedin.com\\\/company\\\/ahrefs\\\/\",\"https:\\\/\\\/www.youtube.com\\\/c\\\/ahrefscom\"]},{\"@type\":\"Person\",\"@id\":\"https:\\\/\\\/ahrefs.com\\\/blog\\\/#\\\/schema\\\/person\\\/e6a89cbde8e750d22996aa26e213e712\",\"name\":\"Joshua Hardwick\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/ahrefs.com\\\/blog\\\/wp-content\\\/uploads\\\/2019\\\/10\\\/meme.jpg109e89523fcea81015d3cc08c79f9036\",\"url\":\"https:\\\/\\\/ahrefs.com\\\/blog\\\/wp-content\\\/uploads\\\/2019\\\/10\\\/meme.jpg\",\"contentUrl\":\"https:\\\/\\\/ahrefs.com\\\/blog\\\/wp-content\\\/uploads\\\/2019\\\/10\\\/meme.jpg\",\"caption\":\"Joshua Hardwick\"},\"description\":\"Head of Content @ Ahrefs (or, in plain English, I'm the guy responsible for ensuring that every blog post we publish is EPIC).\",\"sameAs\":[\"https:\\\/\\\/x.com\\\/JoshuaCHardwick\"],\"url\":\"https:\\\/\\\/ahrefs.com\\\/blog\\\/author\\\/joshua-hardwick\\\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"10 Google Sheets Formulas Every SEO Should Know","description":"Google Sheets can be used to scrape data from websites, create semi-automated SEO workflows, manipulate big data sets, automate follow-ups for outreach campaigns, and much more. Here are 10 Google Sheets formulas every SEO should know.","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/ahrefs.com\/blog\/google-sheets-formulas-seo\/","og_locale":"en_US","og_type":"article","og_title":"10 Google Sheets Formulas Every SEO Should Know","og_description":"Google Sheets + SEO = YES! Here are 10 Google Sheets formulas every SEO should know.","og_url":"https:\/\/ahrefs.com\/blog\/google-sheets-formulas-seo\/","og_site_name":"SEO Blog by Ahrefs","article_publisher":"https:\/\/www.facebook.com\/Ahrefs\/","article_published_time":"2017-06-20T09:34:01+00:00","article_modified_time":"2021-06-29T17:44:31+00:00","og_image":[{"width":951,"height":495,"url":"https:\/\/ahrefs.com\/blog\/wp-content\/uploads\/2017\/06\/google-sheets-formulas-seo.png","type":"image\/png"}],"author":"Joshua Hardwick","twitter_card":"summary_large_image","twitter_creator":"@JoshuaCHardwick","twitter_site":"@ahrefs","schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/ahrefs.com\/blog\/google-sheets-formulas-seo\/#article","isPartOf":{"@id":"https:\/\/ahrefs.com\/blog\/google-sheets-formulas-seo\/"},"author":{"name":"Joshua Hardwick","@id":"https:\/\/ahrefs.com\/blog\/#\/schema\/person\/e6a89cbde8e750d22996aa26e213e712"},"headline":"10 Google Sheets Formulas Every SEO Should Know","datePublished":"2017-06-20T09:34:01+00:00","dateModified":"2021-06-29T17:44:31+00:00","mainEntityOfPage":{"@id":"https:\/\/ahrefs.com\/blog\/google-sheets-formulas-seo\/"},"wordCount":2630,"commentCount":77,"publisher":{"@id":"https:\/\/ahrefs.com\/blog\/#organization"},"image":{"@id":"https:\/\/ahrefs.com\/blog\/google-sheets-formulas-seo\/#primaryimage"},"thumbnailUrl":"https:\/\/ahrefs.com\/blog\/wp-content\/uploads\/2017\/06\/google-sheets-formulas-seo.png","articleSection":["General SEO"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/ahrefs.com\/blog\/google-sheets-formulas-seo\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/ahrefs.com\/blog\/google-sheets-formulas-seo\/","url":"https:\/\/ahrefs.com\/blog\/google-sheets-formulas-seo\/","name":"10 Google Sheets Formulas Every SEO Should Know","isPartOf":{"@id":"https:\/\/ahrefs.com\/blog\/#website"},"primaryImageOfPage":{"@id":"https:\/\/ahrefs.com\/blog\/google-sheets-formulas-seo\/#primaryimage"},"image":{"@id":"https:\/\/ahrefs.com\/blog\/google-sheets-formulas-seo\/#primaryimage"},"thumbnailUrl":"https:\/\/ahrefs.com\/blog\/wp-content\/uploads\/2017\/06\/google-sheets-formulas-seo.png","datePublished":"2017-06-20T09:34:01+00:00","dateModified":"2021-06-29T17:44:31+00:00","description":"Google Sheets can be used to scrape data from websites, create semi-automated SEO workflows, manipulate big data sets, automate follow-ups for outreach campaigns, and much more. Here are 10 Google Sheets formulas every SEO should know.","inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/ahrefs.com\/blog\/google-sheets-formulas-seo\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/ahrefs.com\/blog\/google-sheets-formulas-seo\/#primaryimage","url":"https:\/\/ahrefs.com\/blog\/wp-content\/uploads\/2017\/06\/google-sheets-formulas-seo.png","contentUrl":"https:\/\/ahrefs.com\/blog\/wp-content\/uploads\/2017\/06\/google-sheets-formulas-seo.png","width":951,"height":495},{"@type":"WebSite","@id":"https:\/\/ahrefs.com\/blog\/#website","url":"https:\/\/ahrefs.com\/blog\/","name":"SEO Blog by Ahrefs","description":"Link Building Strategies &amp; SEO Tips","publisher":{"@id":"https:\/\/ahrefs.com\/blog\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/ahrefs.com\/blog\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Organization","@id":"https:\/\/ahrefs.com\/blog\/#organization","name":"Ahrefs","url":"https:\/\/ahrefs.com\/blog\/","logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/ahrefs.com\/blog\/#\/schema\/logo\/image\/","url":"https:\/\/ahrefs.com\/blog\/wp-content\/uploads\/2023\/06\/ahrefs-logo.png","contentUrl":"https:\/\/ahrefs.com\/blog\/wp-content\/uploads\/2023\/06\/ahrefs-logo.png","width":2048,"height":768,"caption":"Ahrefs"},"image":{"@id":"https:\/\/ahrefs.com\/blog\/#\/schema\/logo\/image\/"},"sameAs":["https:\/\/www.facebook.com\/Ahrefs\/","https:\/\/x.com\/ahrefs","https:\/\/www.linkedin.com\/company\/ahrefs\/","https:\/\/www.youtube.com\/c\/ahrefscom"]},{"@type":"Person","@id":"https:\/\/ahrefs.com\/blog\/#\/schema\/person\/e6a89cbde8e750d22996aa26e213e712","name":"Joshua Hardwick","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/ahrefs.com\/blog\/wp-content\/uploads\/2019\/10\/meme.jpg109e89523fcea81015d3cc08c79f9036","url":"https:\/\/ahrefs.com\/blog\/wp-content\/uploads\/2019\/10\/meme.jpg","contentUrl":"https:\/\/ahrefs.com\/blog\/wp-content\/uploads\/2019\/10\/meme.jpg","caption":"Joshua Hardwick"},"description":"Head of Content @ Ahrefs (or, in plain English, I'm the guy responsible for ensuring that every blog post we publish is EPIC).","sameAs":["https:\/\/x.com\/JoshuaCHardwick"],"url":"https:\/\/ahrefs.com\/blog\/author\/joshua-hardwick\/"}]}},"as_json":null,"_links":{"self":[{"href":"https:\/\/ahrefs.com\/blog\/wp-json\/wp\/v2\/posts\/16122","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/ahrefs.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/ahrefs.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/ahrefs.com\/blog\/wp-json\/wp\/v2\/users\/114"}],"replies":[{"embeddable":true,"href":"https:\/\/ahrefs.com\/blog\/wp-json\/wp\/v2\/comments?post=16122"}],"version-history":[{"count":0,"href":"https:\/\/ahrefs.com\/blog\/wp-json\/wp\/v2\/posts\/16122\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/ahrefs.com\/blog\/wp-json\/wp\/v2\/media\/16240"}],"wp:attachment":[{"href":"https:\/\/ahrefs.com\/blog\/wp-json\/wp\/v2\/media?parent=16122"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/ahrefs.com\/blog\/wp-json\/wp\/v2\/categories?post=16122"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/ahrefs.com\/blog\/wp-json\/wp\/v2\/tags?post=16122"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/ahrefs.com\/blog\/wp-json\/wp\/v2\/coauthors?post=16122"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}