All the Google Sheets Formulas You'll ever need, in one tutorial

Your head may be swimming with the literally thousands of combinations of Google Sheets formulas you could use to automate your work.

These functions offer a limitless possibility, but also a limitless waste of time spent learning them.

The good news: to master Google Sheets, you only really need to learn 10 formulas.

If you prefer working directly in a cheat Sheet, grab it here (also available at the end of the post).

Pick a formula directly from the menu below, or browse through each formula at your leisure.

At the bottom of the post, we’ll also walk through 10 ways for digital marketers to hack these formulas together at work (stuff like stripping apart URLs into component parts).

Table of Contents

  1. Arrayformula
  2. Split
  3. Index
  4. Left and Right
  5. If and Iferror
  6. Today and Weekday
  7. Text
  8. Combining Ranges with { }
  9. Query
  10. Formula Hacks for Digital Marketers

ARRAYFORMULA

This one confused me for a long time. But once I figured out how to use ARRAYFORMULA, I’ll never go back – because it’s an instrument of laziness.

It allows you to write a formula once, and apply it to an entire row or column.

No more copy and pasting across a sheet – and when that one arrayformula breaks, you only have one cell to check (instead of 1000 if you’re copy-pasting).

How it works
There’s one key to understanding ARRAYFORMULA: everything must be a range. You can’t just run a vlookup on cell A2 – you’ve got to pass the entire array (A2:A, or some section like A2:A6).

=ARRAYFORMULA( VLOOKUP( A2:A, data!$A:$C, 3, 0))

That’s really all there is to ARRAYFORMULA. You write a formula as you normally would (VLOOKUP in this case), rewrite any individual cells (A2) as ranges (A2:A), and wrap the entire thing in ARRAYFORMULA().

When to use it
Anytime you want to run the same formula across multiple cells. Think about arrayformula as a replacement for copy-paste within spreadsheets.

I find myself using it most often when I want to populate an entire column.

For example, using our sample Twitter data, what if we wanted to subtract the number of followers from number following, for the entire column?

Input

followers following
174,193 58,028
109,189 44,409
32,454 1,720
1,845 893
2,115 1,041

Formula

=ARRAYFORMULA(B5:B9 - C5:C9)

Output 

116,165
64,780
30,734
952
1,074

SPLIT

SPLIT helps you separate a string of text, separated by a character (like a comma), into separate cells.

The delimiter (separating character) can be any value – a space, a comma, a pipe (“”|””), a phrase (“”www””).

For example, let’s try splitting a tweet out into words:

Input

Knowing #SQL is a must for every #Analyst, even if they are not going to hack at databases.

Formula

=SPLIT(B3," ")

Output

Knowing #SQL is a must for every #Analyst, even if they are not going to hack at databases.

INDEX

If you ever want to pull a specific value from a range of cells – INDEX is your sniper.

You pass it a range, a row, and a column, and it returns the value present there.

To demonstrate, let’s take the 2 x 2 range below. What if we wanted to pull the value in the 2nd row and 2nd column?

Input

1 2
3 4

Formula

=INDEX( B3:C4, 2, 2)

Output

4

Now, let’s try pulling the value from the 2nd row and 1st column:

Formula

=INDEX( B3:C4, 2, 1)

Output

3

LEFT and RIGHT

LEFT and RIGHT allow you to slice off a segment of text from either side of a cell.

Each of them only takes two arguments:

LEFT(text, # of characters) or RIGHT(text, # of characters).

Let’s take a date string, for example:

Mon Aug 22 22:58:54 +0000 2016

Using LEFT or RIGHT, you could pull out any of the individual date values: the day, the month, the date, the hour or the year.

Let’s start with pulling just the year:

Input

Mon Aug 22 22:58:54 +0000 2016

Formula

=RIGHT( B5, 4)

Output

2016

What about pulling the month? We could combine LEFT and RIGHT:

Input

Mon Aug 22 22:58:54 +0000 2016

Formula

=RIGHT( LEFT( B9, 7), 3)

Output

Aug

IF and IFERROR

If you’ve used spreadsheets before, you’ve likely written an IF function.

They let you embed logic into your formulas – and keep formulas in your sheet from running when they don’t need to:

=IF(A2="", do nothing, run this function)

This simple IF statement keeps the function from running if A2 is blank – if there’s nothing for the formula to process, no reason to run it!

This keeps your sheets clean and fast – running a bunch of formulas unnecessarily will slow things down.

Input

Blank cell

Formula

=IF( B5="", "Don't run the function", "Run the function")

Output

Don’t run the function

IF has a cousin, IFERROR, that further helps bulletproof our sheets.

There are many functions, like VLOOKUP or QUERY, that will throw error messages when they fail:

Error – Did not find value in VLOOKUP evaluation.

These make your sheets ugly, and may cause other calculations downstream to fail – so I use IFERROR to set the output in the event of an error.

Input

name score
steve 78

Formula

=IFERROR( VLOOKUP( "lisa", B11:C12, 2, 0), "No result")

Output

No result

Both IF and IFERROR become really powerful when used with ARRAYFORMULA, since they allow you to keep entire columns or rows clean with one formula.

Working with Dates

Making dates update automatically in your spreadsheets is *the biggest* consistent timesaver I’ve found.

There’s one key function that is the keystone for all date automation: TODAY.

TODAY returns, er, today’s date, which lets you easily build date ranges. Say we wanted to pull the last 7 days for a report:

Formula

Start date End date
=TODAY() - 7
=TODAY()

Output (as of publishing)

7/16/2017 7/23/2017

What if we wanted to pull the current month to date?

That gets a little tricker. We’ll have to use the DATE function, which accepts a (year, month, day) and creates a date value.

If we input DATE(2016, 1, 1) we’d get January 1st, 2016. But how do we get the current month?

That’s where handy functions called DAY, MONTH and YEAR come in. Inputting MONTH(TODAY()) will pull today’s month (same for DAY and YEAR).

So, putting together DATE, MONTH, YEAR and TODAY, we can find the first day in this month:

Formula

Start date End date
=DATE(YEAR(TODAY()), MONTH(TODAY()), 1)
=TODAY()

Output (as of publishing)

7/1/2017 7/23/2017

What would you guess the WEEKDAY function does? That’s right – it pulls the weekday that a date falls on.

But instead of telling you Saturday or Sunday, it returns a number (1 = Sunday).

Say you wanted to pull sales numbers for only Sundays, WEEKDAY is your best friend.

TEXT

TEXT can take any value and reformat it: a number into currency, a date string into MM/DD format, and so on. It’s your formatting swiss army knife.

You set it up as TEXT(text to format, format to display), and it accepts the folllowing formats:

"$0.00"
Currency
"YYYY-MM-DD"
Dates
"#"
Integer
"0.000"
Floating numbers

It’s very useful to avoid having to manually format numbers. For example, if you wanted the number 48 to appear as currency ($48.00):

Input

48

Formula

=TEXT ( B5 , "$0.00" )

Output

$48.00

I use it most often in combination with QUERY – becuase when comparing dates ( AND A > date ‘yyyy-mm-dd’ ), your date has to be correctly formatted.

Let’s try formatting 1/1/2017 as yyyy-mm-dd for use in a QUERY function.

Input

1/1/2017

Formula

=TEXT ( B11 , "yyyy-mm-dd" )

Output

2017-01-01

Combining Data Ranges

This is not a formula at all, but it’s been *so helpful* in building spreadsheet templates.

If you have two datasets you want to combine, you can nest them in { } to form one data range.

There are two ways to do this:

1. Stack ranges on top of each other, by separating them using ;
2. Combine ranges next to each other, by separating them using ,

Let’s try stacking them first:

Input

range 1
1 2

 

range 2
3 4

Formula

={B4:C4 ; E4:F4}

Output

1 2
3 4

Now let’s try combining them next to each other using a comma as the separator:

Formula

={B4:C4 , E4:F4}

Output

1 2 3 4

QUERY

The big kahuna of Sheets functions.  QUERY lets you combine all of them into one powerful ball of Google Sheets formula magic.

It’s a bit complex to cover in this space, so we devoted an entire monster post to it.  Learn to master the Google Sheets query here.

Formulas in Action (for Digital Marketers)

Before we dive into these examples, as a reminder you can grab the accompanying Google Sheets cheat sheet from the bottom of the post.

1) Importing a Sitemap into Google Sheets

Formula

=IMPORTXML("http://codingisforlosers.com/post-sitemap.xml","//*[local-name() ='url']/*[local-name() ='loc']")

Output

http://codingisforlosers.com/blockspring-google-analytics-template-tutorial/

http://codingisforlosers.com/acquisition-channel-report-blockspring-google-analytics/

Etc for the remaining pages.

2) Import meta title from a URL

Formula

=IMPORTXML("http://codingisforlosers.com/", "//title/text()")

Output

Spreadsheet Automation Templates – Coding is for Losers

3) Import meta description from a URL

Formula

=IMPORTXML("http://codingisforlosers.com/", "/html/head/meta[@name='description']/@content")

Output

Access a library of spreadsheet dashboard and automation templates, using services like Blockspring and Zapier to automate your data analysis work.

4) Extract root domain from a URL

Formula

=REGEXEXTRACT("http://codingisforlosers.com","^(?:https?:\/\/)?(?:www\.)?([^\/]+)")

Output

codingisforlosers.com

5) Extract protocol + root domain from URL

Formula

=INDEX(SPLIT("http://codingisforlosers.com/supermetrics","/"),1)&"//"&INDEX(SPLIT("http://codingisforlosers.com/supermetrics","/"),2)

Output

http://codingisforlosers.com

6) Extract path from URL

Formula

=REGEXEXTRACT("http://codingisforlosers.com/blockspring-vs-supermetrics","http://[^/]+/(.*)|https://[^/]+/(.*)")

Output

blockspring-vs-supermetrics

7) Extract the first URL from a paragraph of text

Formula

=INDEX(REGEXEXTRACT("i love http://codingisforlosers.com", "\b(([\w-]+://?|www[.])[^\s()<>]+(?:([\w\d]+)|([^[:punct:]\s]|/)))"),1)

Output

http://codingisforlosers.com

8) Extract email from a paragraph

Formula

=REGEXEXTRACT("reach out to help@codingisforlosers.com for help","[A-z0-9._%+-]+@[A-z0-9.-]+.")

Output

help@codingisforlosers.com

9) Extract hashtags from a paragraph

Formula

=REGEXREPLACE("hey there from #losershq", "((^|\s)[^#]\S*)|([^#\w\s]\S*)", "")

Output

#losershq

10) Strip punctuation and hashtags from a paragraph

Formula

=REGEXREPLACE(LOWER("HEY THERE, what's up @losershq?"),"[^a-zA-Z]"," ")

Output

hey there what s up losershq

That’s all!

I know that’s a lot of material to pick up off the bat, so feel free to grab the cheat sheet below and learn at your leisure.

If you’re ready to dive wayyy deeper, also check out all of the Coding is for Losers courses on mastering Google Sheets.