For a long time I was a bit scared of spreadsheets.
My only dealings with them was trying and failing to look up order values in Excel at one of my first jobs.
I thought they weren’t useful to my marketing. I thought spreadsheets and Excel in particular were only for accountants.
Then I discovered Google Sheets.
18 Useful Google Sheets Formulas for Marketers
Below you will find short tutorials on how to use 18 of the most useful formulas in Google Sheets:
If you’d rather read the TLDR version, here’s an overview of the best formulas and functions for marketers:
Google Sheets Formulas (aka SEO formulas)
There are loads of Google Sheets formulas to master. Before you move onto more advanced formulas try to get the hang of the more simple ones and go from there.
Here are just some of the most useful ones to marketing:
- LEFT & RIGHT
- LEN
- TRIM
- COUNTIF
- SUMIF
- UNIQUE
- CONCATENATE
- SPLIT
- IF & IFERROR
- IMPORTRANGE
- ARRAYFORMULA
- TODAY & WEEKDAY
- VLOOKUP
There are obviously way more formulas out there you could use. This is just a starting point or some of the formulas I find myself using time and again.
Let’s look at how to use them in some more detail:
Basic Google Sheets Formulas 💪
If you are just tipping your toe into the world of Google Sheets, the below list are some the basic formulas to start with. Don’t underestimate them just because they are basic though.
LEFT & RIGHT
LEFT(text, # of characters)
RIGHT(text, # of characters)
LEFT and RIGHT allow you to cut off a segment of text from either the left or right side of a cell.
And this is what each formula looks like in action:
LEFT
RIGHT
LEN
=LEN(cell)
LEN returns the length of the specified string, in terms actual humans would speak: it counts characters. (btw it also counts spaces)
It’s very useful for writing meta descriptions, page titles, product descriptions or anything that has a character limit.
TRIM
=TRIM(string)
The TRIM formula spits out text from a cell with the leading and trailing spaces removed.
Text spacing in cells often catches people out.
You might find yourself trying to cobble together a URL in sheets from a list of keywords. If there’s a space in the keyword (which happens now and then when exporting them from various tools), you’ll mess up your lovingly crafted URLs. So use TRIM.
UNIQUE
=unique(array)
The UNIQUE formula looks at a range and pulls out all the unique values from it.
It also works with text. Just observe the very official ‘Four Barrys and a Steve Test’:
SPLIT
=split(string, delimiter)
The SPLIT formula splits a string in a cell based on a given delimiter (a character), and puts each value it splits out into its own cell:
Next Level Google Sheets Formulas 💪💪
So you’ve beat the boss. You’ve got basic formulas in the bag.
Now it’s time up our laziness game.
COUNTIF
=countif(range, criteria)
The COUNTIF formula counts the number of cells in a defined range that match the specified criteria.
In this example, any cells in the Status column that contain the word ‘paid’:
SUMIF
=sumif(range, criteria, [sum_range])
The SUMIF formula adds all numbers in a range of cells based on one criteria.
In this case, for any cells in the status column that include the word paid, the formula adds up their value in the £££ column:
CONCATENATE
=concatenate(string1, string2, string3)
The CONCATENATE formula allows you to merge cells into one. Pretty useful for marking URLs, email addresses and loads of other more advanced functions:
IMPORTRANGE
=importrange(spreadsheet_ID, range_to_import)
The IMPORTRANGE formula allows you to import data from one Google Sheet to another.
If you’ve got a client facing sheet, this is a handy way to pull in sections of data from your master spreadsheet.
Or as in the example below, maybe you want to import the first verse of the lyrics from Iron Maiden’s classic song The Trooper. Well I don’t know, you might):
Be sure to grab the worksheet here with all the basic Google Sheets formulas in >>>
TODAY & WEEKDAY
TODAY()
WEEKDAY(date, [type])
Writing out dates in spreadsheets takes up too much time. It’s surprising how often you have to do it. Especially if you are as bad with dates as I am.
Enter TODAY & WEEKDAY to fully automate your date mate (sorry, couldn’t resist).
Adding in TODAY to a cells, pulls in, well, the today’s date…
This lets you easily build date ranges for reports. Just add – 7 at the end of the formula and you’ll get today’s date minus 7 days. Put this together and you’ve got fully automated date ranges, which auto update for reporting dashboards:
The Big Gun Formulas 💪💪💪
Alright. Now it’s time to get serious.
Super serious.
If you want to take your lazy marketing skills to the next level, it’s time for the big guns:
IF & IFERROR
=if(condition, value_if_true, value_if_false)
The IF formula is a powerful tool in the SEO utility belt. Basically, it checks if a condition is true or false.
You can get really advanced with IF statements, but let’s just keep it simple for now.
Let’s look at this hypothetical example, where I want to see keywords that have a search volume of 300 or over. The formula would look like this:
Ok but now we want to step it up a gear.
Using the above example again, now I only want to see keywords that will result in 100+ visitors per month, based on a CTR of 29% (again, completely hypothetical).
So we want to modify the IF formula with *0.29>=100 to look for that sweet 29% CTR:
IFERROR
=iferror(original_formula, value_if_error)
IFERROR allows you to set a default value should the formula result in an error (#VALUE!).
Let’s use the same example as before, but lets add in batman as a keyword and let’s say he has no search volume (sorry Batman!):
In this case, when the IFERROR finds a non numerical value in the Search Volume column (- in this case) it returns the error message ‘sorry bats’
You can choose your own message btw.
ARRAYFORMULA
ARRAYFORMULA(array_formula)
I didn’t use an ARRAYFORMULA for a long time. Mainly because I wasn’t really sure on how to use them and whether they were useful.
I was so wrong here.
The ARRAYFORMULA supercharged my laziness. How? Well it lets you write a formula once, and then apply that formula to an entire row or column.
You. Write. It. Once.
Say goodbye to copy and pasting formulas across a sheet. Not only is this super lazy efficient, but it also means that if a formula breaks, there’s only one to fix. Not the 1000’s of cells you just copy and pasted it into.
Here it is in action:
Using the IFERROR example from earlier, by adding the ARRAYFORMULA, we only have to write out the formula once.
So this:
=IFERROR(IF(B3*0.29>=100, "Yay","Nay"),"sorry bats")
Becomes this:
=ARRAYFORMULA(IFERROR(IF(B3:B7*0.29>=100, "Yay","Nay"),"sorry bats"))
The important thing to note here is that the ARRAY is applied to the whole range and just the cell. B3 becomes B3:B7
That’s all there is to it. Write your formula as you normally would, switch lone cells (A2) to ranges (A2:A) and wrap in the ARRAYFORMULA.
I really was an idiot for individually writing out formulas for so long.
VLOOKUP
=vlookup(string, range, index_key)
VLOOKUPS are the one fo the most fundamentals automation tools a marketer can (and should) master.
The formula allows you to search a range using a string and then return matching values from a specific cell in the range.
Let’s say I have a sheet with keywords and rankings. Then, in a seperate tab, I have the search volumes for these keywords
Using a VLOOKUP, I can pull these search volumes into my tracking sheet:
Pssst, don’t forget, we can also wrap our VLOOKUP in an ARRAYFORMULA so we don’t even have to drag the formula down:
Google Sheets + Marketing = 👍
Initially I just used sheets as a crude project management template and for a bit of keyword research. But over time I learnt that you could do lots of useful SEO & marketing stuff with sheets templates.
I’m still only scratching the surface of what is doable with just sheets and a willingness to learn.
80/20 Rule
Like learning a lot of things, Sheets really does highlight the 80/20 rule.
Also known as the Pareto principle it states that, for many events, roughly 80% of the effects come from 20% of the causes.
So you can do 80% of the things by knowing 20%. So with just a handful of basic formulas, you can do a lot. It’s just a matter of discovering how to work it.
Wrapping up
There you go, the top formulas for marketers wanting to up their Sheets game.
Have a play around, break things, and see what you can put together.
As you go further down the rabbit-hole, you’ll realise the importance of the basics and the world of opportunities just a formula away.
Don’t forget!
Wanna grab a copy of my formula cheat sheet? It’s got all the formulas used in this article, so you can just copy, paste and tweak for your own projects. You can grab it here: