Google Sheets Formulas for Marketing

Get to grips with the Google Sheets formulas to help automate your work, hack together SEO tools and level up your marketing.

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.

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

2018 10 27 15 49 39

RIGHT

2018 10 27 15 51 12

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.

2018 10 27 15 57 49

TRIM

=TRIM(string)

The TRIM formula spits out text from a cell with the leading and trailing spaces removed.

2018 10 27 16 08 30

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.

2018 10 27 16 24 27

It also works with text. Just observe the very official ‘Four Barrys and a Steve Test’:

2018 10 27 16 26 35

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:

2018 10 27 16 33 50


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’:

2018 10 27 16 14 48

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:

2018 10 27 16 20 09

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:

2018 10 27 16 29 26

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):

2018 10 27 17 13 41

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…

2018 10 27 19 42 07

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:

2018 10 27 19 45 34


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:

2018 10 27 16 43 56

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:

2018 10 27 17 00 22

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!):

2018 10 27 17 03 41

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:

2018 10 27 19 31 12

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:

2018 10 27 20 14 09

Pssst, don’t forget, we can also wrap our VLOOKUP in an ARRAYFORMULA so we don’t even have to drag the formula down:

2018 10 27 20 16 37

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:

Cheat sheet

Leave a Comment

Want access to *cool* new sheets templates?

Join the newsletter right now.

Sign up to get notified when new Google Sheets templates + tools get added to the directory.

We send x2 emails per month. No spam. Dancing not a requirement.