10 Google Sheets Formulas Every SEO Should Know

June 20, 2017 1.3K shares 74 Comments
I'm an "SEO" with 7+ years experience; founder of The SEO Project; "link building" enthusiast; regular Ahrefs contributor; avid drinker of red wine; self-proclaimed steak expert; and all-round cool guy. I'm also shorter than you (probably).

Article stats

  • Referring domains 36
  • Organic traffic 152
Data from Content Explorer tool.
Quick links
    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 Google Sheets formulas and show how you can use them for everyday SEO tasks.

    Let’s start with the basics…

    In this short section, I’m going to share three basic must-know formulas.

    No matter what kind of SEO work I’m doing in Google Sheets, I find myself using these three formulas (almost) every time:

    • IF;
    • IFERROR;
    • ARRAYFORMULA

    Let’s start with an IF statement.

    This is super simple; it’s used to check if a condition is true or false.

    Syntax: =IF (condition, value_if_true, value_if_false)

    Here’s an example spreadsheet containing a list of keywords with their respective estimated search volumes (note: these were gathered using Keyword Explorer):

    Let’s 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).

    According to this study, #1 rankings in the US (desktop searches only) have a 29% CTR, roughly.

    Sidenote.
    We don’t recommend relying on this methodology to estimate search traffic as CTR varies greatly across different queries, devices, and so forth. That’s why we use clickstream data to estimate traffic.

    So, let’s write an IF statement that will return “GOOD” 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 “BAD” for the rest.

    Here’s the formula:

    =IF(B2*0.29>=500,“GOOD”,“BAD”)

    GIF

    Here’s what this does (in plain English):

    1. It checks to see if B2*0.29 (i.e. 29% of the search volume) is bigger than or equal to 500;
    2. If the condition is true, it returns “GOOD”. If it’s false, it returns “BAD”.

    This works very well for our current data set, but look what happens when we throw some non-numerical values into the mix:

    That’s an error.

    This happens because it’s impossible to multiply a non-numerical value by 0.29 (obviously).

    Sidenote.
    I’ve added some conditional formatting so wherever the IF statement evaluates to TRUE, the cells are highlighted green. If the statement evaluates to FALSE, they’re highlighted red. 

    This is where IFERROR comes in handy.

    IFERROR allows you to set a default value should the formula result in an error.

    Syntax: =IFERROR (original_formula, value_if_error)

    Let’s incorporate this into the example above (we’ll leave the cell blank if there’s an error) and see what happens:

    GIF

    Perfect — that’s the formula complete!

    OK, so if you only ever work with a tiny amount of data, feel free to 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.

    Syntax: =ARRAYFORMULA (array_formula)

    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.

    So, let’s delete all formulas in cells B2 onwards, and wrap the entire formula currently in cell B1 in an ARRAYFORMULA, like so:

    =ARRAYFORMULA(IFERROR(IF(B2:B29*0.29>=500,“GOOD”,“BAD”),””))

    GIF

    Magic.

    That’s the basics covered; let’s take a look at some more useful formulas.

    Sidenote.
    Here’s a spreadsheet showing how each of these formulas work (note: cells containing formulas will be highlighted yellow). I’ll be including more of these spreadsheets throughout the post. 

    1. Use REGEXTRACT to extract data from strings

    REGEXTRACT uses regular expressions to extract substrings from a string or cell.

    Syntax: REGEXEXTRACT (text, regular_expression)

    Here are just a handful of potential use cases for this:

    • Extract domain names from a list of URLs (keep reading to see an example!);
    • Extract the URL (i.e. without the root domain);
    • Check if URL uses HTTP or HTTPS;
    • Extract email addresses from a big chunk of text;
    • Identify URLs with/without certain words in them from a list of URLs (e.g. URLs containing the “/category/guest-post” slug).

    Let’s assume we want to extract the root domains from a list of “write for us” page URLs (i.e. guest post opportunities).

    In column B, we can write a REGEXTRACT formula to do this.

    Here is the regex syntax we need: ^(?:https?:\/\/)?(?:[^@\n]+@)?(?:www\.)?([^:\/\n]+)

    Sidenote.
    If you’re not familiar with regex (don’t worry, I’m not great at it either), you have two options: (i) Learn the basics — check out Regexr.com (ii) Google the solution for whatever you need — seriously, it’s surprising what you can find with a bit of Googling! 

    Here’s our final formula:

    =REGEXEXTRACT(A2,”^(?:https?:\/\/)?(?:[^@\n]+@)?(?:www\.)?([^:\/\n]+)”)

    Paste this into cell B2 and, hey presto, we’ve extracted the domain.

    Let’s wrap this in an ARRAYFORMULA and IFERROR to complete the entire column.

    =IFERROR(ARRAYFORMULA(REGEXEXTRACT(A2:A,”^(?:https?:\/\/)?(?:[^@\n]+@)?(?:www\.)?([^:\/\n]+)”)),””)

    GIF
    Sidenote.
    Here’s the spreadsheet showing how this formula works. 

    2. SPLIT strings into multiple data points

    SPLIT divides (i.e. splits) strings into fragments using a delimiter.

    Syntax: SPLIT (text, delimiter)

    Here are just a handful of potential use cases for this:

    • Split a prospect’s full name into “first name” and “last name” columns;
    • Split a URL into 3 columns for HTTP protocol, root domain, and URL slug;
    • Split a list of comma-separated values into multiple columns;
    • Split a root domain into 2 columns for domain name and domain extension (e.g. .com, .org, etc.)

    I have a nice list of Ahrefs’ team members (full names) in a spreadsheet.

    Here’s a simple SPLIT formula we could use in cell B2 to divide these into first name and last name:

    =SPLIT(A2,” ”)

    GIF
    Sidenote.
    We’re using a space (i.e. ” ”) as our delimiter as this tells the SPLIT formula where to split the string. 

    Again, let’s wrap this in an IFERROR and ARRAYFORMULA to split the entire list with a single formula.

    =IFERROR(ARRAYFORMULA(SPLIT(A2:A,” ”)),””)

    GIF

    Here’s another example formula that will split root domains into site name and domain extension:

    =SPLIT(A2,”.”)

    GIF
    Sidenote.
    Here’s the spreadsheet.

    3. Merge multiple data sets using VLOOKUP

    VLOOKUP allows you to search a range using a search key—you can then return matching values from a specific cell in said range.

    Syntax: VLOOKUP(search_key, range, index_key)

    Here are just a handful of potential use cases for this:

    • 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 information (i.e. email addresses) in another spreadsheet.

    Site Explorer export (note: I removed many of the columns here, as much of the data isn’t needed for this example).

    Master contact database — this is the database we’ll be querying using a VLOOKUP function.

    Sidenote.
    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. 

    We don’t want to waste time looking for contact information that we already have, so let’s use VLOOKUP to query the master database and see if we already have contact info for any of these prospects.

    Here’s the formula we’re going to use:

    =VLOOKUP(D2:D,‘Master contact database ‘!A:C,2,false)

    GIF

    OK, let’s do the same for the email column; we’ll also wrap both formulas in an IFERROR and ARRAYFORMULA.

    GIF
    Sidenote.
    Here’s the spreadsheet.

    4. Scrape data from any website using IMPORTXML

    IMPORTXML lets you import data (using an XPath query) from a number of structured data types including XML, HTML, and RSS (amongst others).

    In other words, you can scrape the web without ever leaving Google Sheets!

    Syntax: IMPORTXML(url, xpath_query)

    Here are just a handful of potential use cases for this:

    • Scraping metadata from a list of URLs (e.g. title, description, h-tags, etc);
    • Scraping email addresses from web pages;
    • Scraping social profiles (e.g. Facebook) from web pages;
    • 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!)

    Let’s assume that we wanted to grab the meta title for our post about keyword research.

    We can see in the HTML that the meta title reads: “How To Do Keyword Research in 2017 — Ahrefs’ Guide”.

    The XPath query we use to grab the meta title is quite simply: “//title”

    Here’s the formula:

    =IMPORTXML(“https://ahrefs.com/blog/keyword-research/”,”//title”)

    It’s also possible to use cell references in the formula; this makes scraping data for a bunch of URLs super simple.

    GIF
    Sidenote.
    Unfortunately, IMPORTXML does not work with an ARRAYFORMULA, so it’ll be a case of manually dragging this one down. 

    IMPORTXML isn’t limited to scraping basic meta tags, either; it can be used to scrape virtually anything. It’s just a case of knowing the XPath.

    Here are a few potentially useful XPath formulas:

    • Extract all links on a page: “//@href”;
    • Extract all internal links on a page: “//a[contains(@href, ‘domain.com’)]/@href”
    • Extract all external links on a page: “//a[not(contains(@href, ‘domain.com’))]/@href”;
    • Extract meta description: “//meta[@name=‘description’]/@content”;
    • Extract H1: “//h1”;
    • Extract email address(es) from page: “//a[contains(@href, ‘mailTo:’) or contains(@href, ‘mailto:’)]/@href”
    • Extract social profiles (i.e. LinkedIn, Facebook, Twitter): “//a[contains(@href, ‘linkedin.com/in’) or contains(@href, ‘twitter.com/’) or contains(@href, ‘facebook.com/’)]/@href”
    • Extract lastBuildDate (from RSS feed): “//lastBuildDate”

    You can find the XPath for any element by doing the following (in Chrome):

    Right-click > Inspect > Right-Click > Copy > Copy XPath

    Sidenote.
    Here’s the spreadsheet (tons of examples in this one :D)

    5. SEARCH strings for certain values

    SEARCH lets you check whether or not a value exists in a string; it then returns the position at which the value is first found in the string.

    Syntax: SEARCH (search_query, text_to_search)

    Here are some use cases:

    • Check if a particular subdomain exists in URL (this is useful for bulk categorizing a list of URLs);
    • Categorize keywords into various intent-based categories (e.g. branded, commercial, etc);
    • Search for specific, undesirable characters within a URL;
    • Search for certain words/phrases within a URL to categorize link prospects (e.g. “/category/guest-post”, “resources.html”, etc)

    Let’s take a look at an example of SEARCH in action.

    Here is a list of the top 300+ pages on Ahrefs.com (note: I used Site Explorer to gather this data):

    Sidenote.
    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) 

    All of the pages with /blog/ in the URL are blog posts. Let’s say that I wanted to tag each of these pages as “Blog post” during a content audit.

    SEARCH (combined with an IF statement — this was discussed earlier in the guide) can do this in seconds; here’s the formula:

    =IF(SEARCH(“/blog/”,A2),“YES”,””)

    GIF

    Let’s wrap it in an IFERROR and ARRAYFORMULA to neaten things up.

    GIF

    Here are a few other useful formulas:

    • Find “write for us” pages in a list of URLs: =IF(SEARCH(“/write-for-us/”,A2),“Write for us page”,””);
    • Find resource pages in a list of URLs: =IF(SEARCH(“/resources.html”,A2),“Resource page”,””);
    • Find branded search terms (in a list of keywords): =IF(SEARCH(“brand_name”,A2),“Branded keyword”,””);
    • Identify internal/external links (from a list of outbound links): =IF(SEARCH(“yourdomain.com”,A2),“Internal Link”,“External Link”);
    Sidenote.
    Here’s the spreadsheet (a few examples in this one, too!) 

    6. Import data from other spreadsheets using IMPORTRANGE

    IMPORTRANGE allows you to import data from any other Google Sheet.

    It doesn’t 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 case!)

    Syntax: IMPORTRANGE (spreadsheet_ID, range_to_import)

    Here are a few use cases:

    • Create client-facing sheets that piggyback off your “master” spreadsheet;
    • Search and cross reference data across multiple Google Sheets (i.e. using IMPORTRANGE combined with VLOOKUPs);
    • Pull in data from another sheet for use in a data validation;
    • Pull in contact data from a “master” spreadsheet using VLOOKUPs

    Let’s take a look at an example of IMPORTRANGE in action.

    Here is a sheet with a list of hypothetical SEO clients + their budgets:

    Let’s assume that I wanted to use this client list in another Google Sheet — I can import this entire data range using the following formula:

    =IMPORTRANGE(“SPREADSHEET_KEY”,“‘SheetName’!A2:A”)

    GIF
    Sidenote.
    Here is where to find your spreadsheet key. 

    Let’s also assume that you’re 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 for.

    You can use IMPORTRANGE to create a dropdown of all clients using a data validation, like so:

    GIF

    This dropdown will self-update whenever you add/remove clients from your master spreadsheet.

    Sidenote.
    Here’s the main spreadsheet (and the data spreadsheet).

    7. QUERY data sets using SQL queries (this one is crazy powerful!)

    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.

    Syntax: QUERY (range, sql_query)

    Here are a few use cases:

    • 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.

    Let’s go back to our sheet of tagged “blog posts”.

    GIF

    If we wanted to pull in all of the URLs that were tagged with “blog post” into a brand new spreadsheet, we could use this QUERY function:

    =QUERY(DATA!A:B,“select A where B = ‘Blog Post’”)

    GIF
    Sidenote.
    This tells the spreadsheet to select all the values in column A where column B = “Blog Posts”. 

    But let’s say that we had a bigger data set. An export file from Site Explorer, perhaps.

    These export files can be quite data heavy, so let’s assume that we wanted to pull out a list of all referring pages with the following attributes:

    • Dofollow link;
    • DR > 50;
    • Backlink status = active (i.e. not tagged as “removed”);
    • External links count < 50;

    Here’s the formula:

    =QUERY(‘DATA — site explorer export’!A2:R,“SELECT E where D > 50 AND H < 50 AND M = ‘Dofollow’ AND N <> ‘REMOVED’”)

    GIF

    NOTE: It’s also possible to incorporate IMPORTRANGE into a QUERY function; this allows you to QUERY data from other sheets.

    Sidenote.
    Here’s the spreadsheet.

    Final thoughts

    Google Sheets is insanely powerful; this post only scratches the surface of what you can do with it.

    I’d recommend playing around with the formulas above and seeing what you can come up with. I also recommend checking out the full library of Google Sheets formulas.

    But, that’s still just the beginning: Google Sheets also integrates with Zapier and IFTTT, which means you can connect with hundreds of other tools and services, too.

    And if you want to get really advanced, look into Apps Script—it’s crazy powerful!

    If you have any creative uses for Google Sheets of your own, please let me know in the comments. I’d love to hear them!

    I'm an "SEO" with 7+ years experience; founder of The SEO Project; "link building" enthusiast; regular Ahrefs contributor; avid drinker of red wine; self-proclaimed steak expert; and all-round cool guy. I'm also shorter than you (probably).

    Article stats

    • Referring domains 36
    • Organic traffic 152
    Data from Content Explorer tool.

    Shows how many different websites are linking to this piece of content. As a general rule, the more websites link to you, the higher you rank in Google.

    Shows estimated monthly search traffic to this article according to Ahrefs data. The actual search traffic (as reported in Google Analytics) is usually 3-5 times bigger.

    Get notified of new articles

    47,080 marketers are already subscribed to Ahrefs blog. Leave your email to get our weekly newsletter.

    • Enaan Farhan

      It is not possible for me to appreciate you. But have to say something marvelous!!! job you did.

      • Joshua Hardwick

        Appreciate than, Enaan 🙂

    • I have been personally using a lot of these but found few new ones. Specially VLOOKUP. Awesome work as usual Joshua!

      • Joshua Hardwick

        Thanks, Udit! 🙂

        Check out the included sheets to see all of the formulas in action; there are a ton of formulas in there. I personally feel this is the most valuable part of this post.

    • maxim adlabs

      In par.4 its incorrect formula. instead “,” arguments should be divided by “;” https://uploads.disquscdn.com/images/625e393fb01040ae78b48ddc638d644b70582f59e0e54996de2ac6a270946852.jpg

      • Joshua Hardwick

        I believe this actually depends on where you are in the world (i.e. where your locale is set within the Google Sheets settings). In the UK, which is where I am, commas and semicolons are interchangeable. I have read in the past that it’s different elsewhere, though.

        Also, there was an old version of Google Sheets that used semicolons, while the new version seems fine with commas. 

        If it’s not working for you, I suggest either changing your locale in the settings or doing a quick find and replace for “,” within the formulas 🙂

        • maxim adlabs

          hm. maybe) so in Ukraine it’s semicolon 🙂

          • Joshua Hardwick

            Just tried this on my sheet, and Google Sheets actually automatically changes “;” to “,” when I execute the formula. Strange.

            Like I said, do a quick find and replace (or change manually) and you should be fine 🙂

            • maxim adlabs

              understand) but maybe you add P.S. with this “different
              delimiter in different locales” ? It would be helpful for nonUK users.
              and thank for work!

    • Whoa thanks for this post! I only knew about like 2 of these. This is a game changer!

      • Joshua Hardwick

        Haha, thanks Brad! 🙂 

        Don’t forget to take a look at the included sheets; they show every formula in the post in action, so you can literally just copy/paste them into your projects.

    • Dang man. This is freaking epic. Been working and sheets and looking for so answers and this helps a ton. Appreciate all the set-by-step details!

      • Joshua Hardwick

        Really appreciate that, Ryan! Nice to meet a fellow Google Sheets advocate! 🙂

        • It’s a great tool and I use it yo create visualizations to help my team and clients better understand their data. SEO’s have a big advantage in the data world, we just need to use it. Love this post and saving to our company wiki. Again. Thanks!

    • Jaspal Singh

      Thanks Joshua Hardwick, really appreciate your work. I am more interested in Apps Script. My email id is jassworld@gmail.com, if you can help me with apps script. 🙂

      • Joshua Hardwick

        Apps Script is a broad area — what do you want to do with it, exactly?

        • Jaspal Singh

          hi, I am from educational field so wanted an app that can send information from sheets on phone.

    • Awesome stuff. I use Google Sheets for a lot of this and only used about a 1/4 of what you’ve just shared. Thanks!

      • Joshua Hardwick

        Thanks, Michael! Google Sheets is crazily powerful — I’m pretty sure I know hardly anything tbh; there’s so much you can do with it, especially when you start looking into Apps Script! 🙂

    • Taylor Richardson

      How is using an Array formula different than just double clicking the corner of the cell to copy the formula all the way down your column?

      Love the article by the way!

      • Joshua Hardwick

        With an ARRAYFORMULA, you don’t have to click and drag; it does it automatically for the entire column 🙂 

        Don’t get me wrong, clicking and dragging ain’t that hard but when you’re working with thousands of rows of data, well, dragging can certainly “drag” on for a good 20–30 seconds…

        EDIT: And, yeah, I realise you can just double-click the corner of the cell but if you add new rows of data to a sheet, you’ll have to double-click it again to extend the formula. ARRAYFORMULA will dynamically update as you adjust the data.

        • Taylor Richardson

          Totally — but did you know that instead of clicking and dragging you can just double-click the corner of the cell? It automatically copies the cell all the way down the column which is also a great time saver. I love that there are many ways of doing a thing in Excel or Google Sheets, and I love swapping tips with other SEOs. <3

          • Joshua Hardwick

            Yeah, just edited the comment above with my thoughts on that, as I somehow completely neglected to comment on that originally 🙂 

            TL;DR — an ARRAYFORMULA will dynamically update as you add/remove data from the sheet; without it, you would have to drag (or double-click) to extent the formula every time you remove/add a row. IMHO it’s quicker and easier just to wrap everything in an arrayformula 🙂

            • Taylor Richardson

              Wonderful! Thank you so much!!

    • Wow. Still it is abracadabra to me.

    • This is awesome. This post comes at the right time! I always wanted to explore more on google sheet for seo purposes. 

      Did you know any other resources (even paid ones) to give more actual case training scenario?

    • Sam

      Josh,
      This is absolutely cool. Thanks for your post. Keep rockin!

      A big Q for you since you have unleashed the ‘chimera’ 🙂 — How can I use a query to generate an output for the top 100 sites(by traffic) across various niches? E.g. Clothing UK or say Travel USA

      Cheers,
      Sam

      • Joshua Hardwick

        Thanks, Sam! 🙂

        That would’t be an easy task. I doubt it would be possible in Google Sheets, as you need access to some kind of traffic data, so would need to obtain this via an API of some kind.

        Ahrefs will estimate the amount of organic traffic for any website/page that you paste into Site Explorer, so that might be the best way of doing it?

        • Link Hustler

          That gave me some idea to streamline the research for Sam!

    • awesome. Though I’ve been using importXML for a while, the demos are still so cool 😛

      • Joshua Hardwick

        Yeah, IMPORTXML is amazing for scraping small amounts of data 🙂

    • Great article, Joshua! I’m an absolute beginner in these formulas, made copies of all the sheets 🙂

      • Joshua Hardwick

        Let me know how you get on, Michael 🙂

    • tshaba

      This has to be one of the greatest articles I’ve read in a long time. Good work, man!

      • Joshua Hardwick

        Appreciate that! 🙂

    • NCode Technologies

      Fantastic article. I was not aware of these advanced formulas for Google sheets. It can really save time and make SEO work easy with easy data filtering. Specially the IMPORTXML can be really helpful.

      • Joshua Hardwick

        Yeah, IMPORTXML is amazing. However, as you start to use it, you’ll probably realise just how limited it can be if you’re hoping to scrape large amounts of data. Google throttles the usage so it sometimes just stops working. That’s where actual scraping software will come in handy 🙂

        • NCode Technologies

          Oh! That’s really frustrating. I was thinking of using it to scrape data from large number of website list. 🙂

          • Link Hustler

            Well It works just fine for 10 -20 scrape at a time, so drag your formulas from row 1 to 10, wait for results, then copy paste value only of row 1 to 9 , drag formula from 10 to 20 etc.… You can also get more of it using multiple account sharing the sheet or Import range but that’s more of a hassle.

            • NCode Technologies

              Yes. No one want to wait for the data to load or use the formula in parts. It’s of no use if it’s not going to work in a single command for large file. Thanks for clarification.

            • Link Hustler

              I guess, you could streamline with some script and IF formulas so it randomize the input URL in one unique formula and then it saves the results IF value is loaded. It would then do it all for you with no manual action. However you’d need to let it run for some hours if list is long. Put on AC as Gsheet is CPU hungry and computer gets hot !
              Personally I found that I could do with just 10 formulas. I run them when I’m building lists of link Creators. Every time I input some domain from ahrefs targeted exports, I’ll check the meta desc and ditch immediately if not suitable. That is allowing me to build and clean my list as I go. Up to 150 list, it does the trick.

    • Mohammed Eugene-Muzdalie

      Mouth wide open

      • Joshua Hardwick

        =IF(JAWDROPPING = TRUE, “Mouth wide open” , “”)

        😀

    • Excellent article Joshua, I have been working through these and applying some of them to my worksheets.

      The only one I wanted that wouldn’t work is using =IMPORTXML to get meta descriptions, it just won’t work.

      • Joshua Hardwick

        Thanks, Kuchiki! — what page(s) are you trying to scrape? Are you sure they have meta descriptions on them?

      • Hi Kuchiki,

        You may want to try this formula 

        =IMPORTXML(A2, “//meta[@name=‘description’]/@content”)

        where A2 is the URL from which you’d want the meta description to be fetched from.

        Nilesh

    • dsottimano

      Nice post. 2 things, Vlookup isn’t as fast or as flexible as index + match, we should really be teaching folks to use that instead. Importxml (which I love) barely works anymore, it’s not reliable. You’re far better off using custom functions that make reliable requests via urlfetchapp and then parse using regular expressions or other JS string methods.

      • Joshua Hardwick

        Yeah, agree about IMPORTXML, it really doesn’t work well if you’re scraping a lot of data, but works find for the odd thing here and there.

        VLOOKUP is still something I use a lot (perhaps just through habit) but I tend to use QUERY for more complex stuff. 

        Tbh it was kind of difficult to choose just 10 formulas for this post (as there’s so many!) but who knows, maybe INDEX + MATCH will be featured in “10 MORE Google Sheets Formulas Every SEO Should Know” one day 🙂

    • Link Hustler

      Wow! You described a good chunk of useful google sheet formulas for SEO here. I like that you open up on the integration// Automation with IFTTT or other add-ons. I have to add that using all those tricks won’t make sense to any SEO if they don’t have clear mind of what they are digging for. Building data base for the sake of it won’t make people more agile. I would love to see your thoughts on a piece about “how to plan your data management in order to take wise & quick SEO decisions”, because using those tricks + a very sharp mind can make a one-man business compete with 60 employee agencies IMHO :). Aka @DjahillZwawi

      • Joshua Hardwick

        Totally agree with what you’re saying here, Djahill — I think most people understand Google Sheets is “powerful” but simply have no clue how it can be used to automate every day tasks; this is why I tried to include actual examples in this post, as hopefully this will get peoples’ creative juices flowing 🙂

      • John

        Please, please write this post or let me know where I can find tutorials on how to go about this! 🙂

    • Josh, many thanks for putting all this together. I routinely refer back to your articles because of how valuable they are. 

      If I overlooked this or am confused, my apologies…but I think you can get away without having to add the Arrays for every formula.
      1. Create the formula
      2. double-click the tiny blue drag box in the corner
      3. it will complete the rest of the rows

      Also, AbleBits offers some free, amazing add-ons like Duplicate remover, Merge Sheets for Sheets. They have a more robust, all-in-one tool for Excel that I use for database work and it’s been a life-saver.

      • Link Hustler

        So right Patrick, Add-on on Gsheets are the real deal. I love “remove duplicate” + “Sort by colour” + “crop ending rows”. It just makes using the tool much more effective. I’ve been pulling some Majestic data as well from there official add-on. I remember my very first boss telling me “if it takes more than 10 minutes to do something, you should be using your time to find the add-on that does it for you”

        • Josh- Good stuff, didn’t know about =UNIQUE(), thanks!
          Link Hustler- Thanks for sort by color, I’ll be using that today!

      • Joshua Hardwick

        Thanks, Patrick — appreciate that! 🙂

        Yeah, you can totally get away without using ARRAYFORMULA for everything. However, the cool thing about ARRAYFORMULA is that, as you add data (i.e. new rows) to the sheet, it’ll automatically fill in the data for that column without any double-clicking/dragging. I usually just wrap anything I can in an ARRAFORMULA as it takes seconds and saves time down the line 🙂 

        Also, the “remove duplicates” add-on works great and clearly has it’s purposes. For most things, though, I just use the =UNIQUE() function, as this seems a little quicker to me.

        All just personal preference, though. There’s really no right or wrong answer and, as with anything like this, there are multiple ways to do the same things. If something works for you, I say stick with it 🙂

    • What a wonderful information here,

      Thanks for this 🙂 

      Umer!

    • Good Work. Appreciated (y)

    • The spreadsheet was very useful . Awesome article

    • Awesome post. Thanks

    • This is really great post about google sheet VLOOKUP formula. This will help me a lot, I will try this. thanks Joshua for wonderful article

    • great post Joshua, very informative and enjoyable to read. will surely implement these in my Google Sheets. thank you mate you have done a great job.
      cheers!

    • This is epic! Great guide Joshua! This helps a lot. IMPORTXML is amazing! how did you know all of this? 🙂 Thanks a lot for sharing!

    • Thanks Joshua, This is amazing insight around extracting what you need from exports. It will save ton of time. Not everyone is good in Excel or worksheets. This post will provide with small and easy to implement codes.

    • Awesome Dude… What a valuable information. Cheers!!

    • This is fantastic content Joshua! You’ve done a great job in your explantations. You’re right, Google Sheets can do way more than collaborate but unfortunately, this isn’t taught in a typical setting. Thank you!

    • Appreciated efforts, detailed step-by-step process helped me to learn something new today 🙂

    • Wow, this article is fantastic! I mainly use VLOOKUP, but I’ll definitely be utilizing more of these formulas. Super excited to try IMPORTXML to grab meta tags. Thanks so much!

    • Wow, this is super cool piece of content! Thank you very much for this 🙂

      I am not good at this excel stuff and I wanted to learn something new, so I bought excel course. But your article is so much better and much more practical. 

      Thanks again.

    • I was not know completely about it. Thank you so much for sharing.

    • I had never thought of these ideas. I will check them out and apply them to my business.

    • I really Appreciate for this Post. Useful Information For Me. Will Surely Share your post.

    • tech iweb

      great article joshua!! i usually apply all these to my worksheets while working . thanks for the post.

    • Önder Bağcı

      Great article! I’d like to share one thing I hate about Google Sheets: not being able to set page layout for a file. I mean inserting page breaks and stuff without needing to fine tune row sizes. It really bums me out especially if I’m dealing with large files that I need to export as a pdf with many pages.