I absolutely love Google Sheets. It’s powerful, free, cloud-based and overall just a fantastic tool to work with. What makes it especially great, however, is its extensive list of formulas.
Without a set of proficient formulas at the ready, you could spend hours sorting, splitting, merging, adding, deleting and searching for data. On the other hand, if you know exactly which formulas to use for specific tasks, you’ll save yourself so much time and energy. The ROI of even one well-placed formula is limitless.
As a SEO analyst, it’s essential you make yourself aware and utilize these formulas in order to make your work that much more effective.
A Few Essentials Before We Start
Before we dive into the formulas themselves, we want to cover a few fundamentals for beginners out there. Knowing these basic definitions will make it much easier to pick up on how to use the formulas we share today and others you learn in the future.
- A cell is a single data point within a sheet.
- A row is a set of cells that extend horizontally.
- A column is a set of cells that extend vertically.
- A function is an operation used to generate a desired result.
- A formula is a combination of cells, columns, rows, functions, and ranges used to create an equation.
- A range is the selection of cells across a column or row (or both).
- All formulas must start with an equals sign (=).
While the following is not the entire list of formulas that can be of use to you, they are a great set to get started with.
1. VLOOKUP for Importing Data
Most SEOs agree that the VLOOKUP (V for vertical) function is one of the best tools to have in your toolbox. With it, you can pull data from one spreadsheet into another spreadsheet – as long as the two sheets share a common value.
This comes in handy when you have large sets of data you want to combine without having to go in and copy and paste the data in every single cell manually. It’s especially helpful when you’re working with many spreadsheets.
Let’s say I have one spreadsheet with two tabs (see screenshot below). One tab shows a list of keywords and each keyword’s monthly search volume. The second tab has the same list of keywords with their SEO difficulty scores. My goal is to pull the SEO difficulty scores into the first tab using the VLOOKUP formula:
=VLOOKUP(search_key, range, index, is_sorted)
It’s important to note that the data point (in this case, “Keyword”) that I want to pull from one tab into the other must be the same in both sheets. This data point must also be located in the first column of the second tab – column A.
I want to pull the data from column two (DifficultyScore) into my “Volume” spreadsheet (first tab). So, here’s what my formula looks like:
Let’s break down each part of this equation:
- Search_key is the value both sheets have in common. We’ll start with the first keyword in the list, located in cell A2.
- Range is where you insert the name of the tab you’re pulling data from, along with the range of columns you want your formula to read. I want to pull in columns A (Keyword) and B (SEO Difficulty).
- Index is where you add the column number that you want to pull data from. A=1, B=2, C=3, and so on.
- Last but not least, I will simply set is_sorted to either zero or “FALSE,” which is usually recommended in this formula to return an exact match.
To enter the formula, I click on cell C2, type it in the formula, and press enter. The correct value is pulled in: 31. I can then click the corner of cell C2 and drag down to fill in the remainder of the values throughout the column.
2. IF a Condition is True or False
The IF formula lets you check and see if a certain condition is true or false for a list of data in your spreadsheet.
The formula is =IF(condition, value_if_true, value_if_false) where condition is what you’re testing to be true or false.
If it is true, “value_if_true” is the operation that will be displayed. If it’s false, “value_if_false” will be displayed instead.
Let’s look at a basic example so we can see how this plays out.
This time, I want to check a list of keywords to see which ones get over 5,000 visitors per month. I’ll start with the first keyword’s volume in cell B2. Here’s the formula:
Once I enter the formula for the first keyword and press enter, I can drag from the first cell (D2) down the column and the answers will auto-fill.
All the keywords with traffic over 5,000 visitors per month show up as “YES,” and those with traffic under 5,000 show up as “NO.”
You can also use the following functions with this equation:
- Equal to (=)
- Not equal (<>)
- Greater than (>)
- Less than (<)
- Greater than or equal to (>=)
- Less than or equal to (<=)
3. Set a Default for Errors with IFERROR
Let’s say one of your cells shows an error message – which could show up as “#ERROR!” or “#DIV/0!” or “#VALUE!” – when you try to run a formula.
Error prompts can happen if you divide by zero or an unknown range is used. This can become a problem when you’re trying to make calculations that involve these cells. It can also make your spreadsheet look messy and may be confusing when you present it to others.
IFERROR helps you sidestep this issue by replacing error values with default values that you set yourself.
Simply insert your original formula along with whatever you want your default value to be. You can even leave it blank and just type ” ” for value_if_error.
=IFERROR (original_formula, value_if_error)
In this example, I want to find out which keywords could bring over 500 visitors to my website by multiplying search volume by click-through rate (CTR). Since “digital marketing agency near me” is (hypothetically) missing a CTR, it returns an error message.
Instead of turning up an error message, I want this cell to remain blank, so I’ll wrap the equation in an IFERROR formula:
4. SPLIT Your Data into Two Cells
Next up is the SPLIT formula:
This allows you to split the data from one cell into multiple cells. Let’s say you have a spreadsheet of contacts with their full names in one column. To import your data correctly into your database, you need to split first and last names into two separate columns.
If I want to divide the names in column A into first and last names in columns B and C, my formula would be =SPLIT(A2, ” “) where A2 is the text I want to split up, and my delimiter is what separates the first and last names – a space in this case.
I’ll click on cell B2, and both B2 and C2 should populate once I enter the formula. Next is to drag from the first cell I populated (Bob) down to the last cell in the column (B12).
All first and last names will auto-populate for me.
5. Count Cells Quickly with COUNTIF
Say goodbye to counting cells manually. With the COUNTIF formula, you can count cells that meet specific criteria.
This formula will not only save you time – but it’ll also make sure your numbers are accurate.
In my very simplified example below, I want to count all the cells in my spreadsheet that mention “Betty.” Here: =COUNTIF(B2:B15, “Betty”)
I’ll paste this formula into an empty cell, press “Enter,” and I’ll have my answer:
6. Apply One Formula to Multiple Cells with ARRAYFORMULA
You’ll love this one.
ARRAYFORMULA allows you to write out your formula one time and apply it to an entire row or column. You just take the formula you want to use and wrap it in an ARRAYFORMULA:
I have an IF formula I’d like to use on column D below to determine which keywords get more than 5,000 visits each month. I select my first cell (D2) and paste my formula into the formula box.
I just need to be sure to include my first and last cells in my formula, i.e., B2 and B24. Then I press enter and the column auto-populates.
7. Use LEN to Count Characters
The LEN formula helps you count the number of characters in a cell.
Let’s say you’re reviewing meta descriptions and page titles for all your website pages in a spreadsheet, and you want to count the characters to make sure they’re all the right length.
Click on a blank cell and enter the formula, replacing “insertcell” with the cell that contains the characters you’d like to count.
In my example, A2 is the first cell I’d like to count, so my formula would simply be =LEN(A2). I press enter afterward and the character count appears.
I can then use this formula for the rest of my column by dragging from the first cell down to the last cell.
8. Easily SORT Your Cells
Want to sort your cells? You guessed it – we’ve got a formula for that. With the SORT formula, you can sort rows by the value in one or more columns. Use the formula =SORT(range, sort_column, is_ascending)
- Range is the group of cells you want to sort.
- Sort_column is the number (index) of the column you want to sort (A=1, B=2, etc.).
- If you want the values to sort in ascending order, simply mark “TRUE” for is_ascending. If you want values to sort in descending order, mark “FALSE” instead.
Let’s look at a simple example.
I want to see which of my top keywords have the highest search volume. Here’s what my formula looks like:
This formula comes in handy when you want to sort values in multiple columns. In the next example, I want to determine my top-performing emails.
I need to set up my formula so the emails with the highest open rates and lowest unsubscribes appear first. My formula for this is =SORT(A2:C9,2,FALSE,3,TRUE)
9. Import Other Data with IMPORTRANGE
IMPORTRANGE is similar to VLOOKUP in that it allows you to import data from another Google Sheets spreadsheet.
Replace spreadsheet_url with the URL of the other spreadsheet, and then specify the range of cells you’d like to import.
If you have multiple tabs in the spreadsheet you want to pull from, you’ll need to specify.
Let’s say you’re pulling from a tab called “Keywords,” and you want the data from cells A2 to H150. Your formula would look like this:
10. Keep Only The UNIQUE Data
Those of us who work with a lot of data know full well how often duplicate information can show up. The UNIQUE formula can help here.
Sometimes, you need to merge multiple family members into a single household, or one person ends up in your system twice by signing up for your newsletter multiple times under different email addresses.
There’s a myriad of ways data can be duplicated, but this problem can quickly turn our spreadsheets and content management systems into huge sloppy messes. That’s where this formula can help.
In this example, I clicked on an empty cell and entered the formula =UNIQUE(A2:C24) to include all three columns of data, then I hit enter.
All duplicate fields are removed, and the unique fields remain.
11. SEARCH for a Specific Value
The SEARCH formula can be used to check a set of data for a certain value.
For instance, let’s say you have all the URLs from your website listed in a spreadsheet and want to identify all of your blog articles.
Instead of going through and manually picking out all the blog posts, you can use this formula:
To flag all your blog posts as “Blog,” you’ll need to combine the above formula with the IF formula, which we covered above.
Here’s what it could look like: =IF(SEARCH(“/blog/”,A2),”Blog”,””)
The formula will pull out all URLs containing /blog/ starting with cell A2.
URLs that don’t contain /blog/ will pull up an error message (#VALUE!). To avoid this, you can wrap this formula in an IFERROR formula to label non-blog URLs as “Other” (or leave it blank) and keep your data clean and error-free.
=IFERROR (IF(SEARCH(“/blog/”,A2),”Blog”,””), “Other”)
12. Merge Cell Data with CONCATENATE
We’ve already learned how to split data into cells, but what about merging cell data? This formula is useful for working with URLs.
Sometimes, when you download a list of URLs, the website addresses aren’t formatted the way you want them.
Maybe you downloaded a list from Google Analytics and the domain is missing. Or maybe the URLs are missing the leading protocol.
Instead of manually updating each URL individually, you can use the CONCATENATE formula.
Once you do the first one, simply pull down from the first box where you entered the formula, and the remaining cells will automatically populate.
If a single prefix or suffix is not present in the sheet, you could manually insert it also without needing to create an extra column of cells for it – where the formula would look like =CONCATENATE(“https://”,A2)
13. Find Category Totals With SUMIF
The SUMIF formula can be useful for adding categories of values based on a specific criterion.
=SUMIF(range, criterion, [sum_range])
In the example below, I want to add up the page views of my top-performing blog posts to determine which blog category is the most popular. I can quickly add up page views by applying this formula for each category.
To calculate the page views in the SEO category:
- The range includes the column of cells under “Category”.
- “SEO” is the criterion.
- The sum_range includes the cells in the column under “Page Views”.
The formula will add up the number of total page views in the SEO category: 36,834. I’ll use this same formula for the other three categories and just switch the criterion for each.
Without having to sort or make any extra calculation, I am able to observe the totals for each blog category and easily determine which category of blog post gets the most page views.
Keep These Formulas On Hand
Now you know some helpful formulas and functions to use in Google Sheets to make your spreadsheet life SO MUCH EASIER as a professional SEO.
Bookmark this page to keep these formulas handy so you can quickly copy and paste them as you’re working. We hope they can help you report, organize and present data much more quickly and professionally (and let you show off your new skills a little in the process)!