Just Enough Google Sheets Formulas

There's endless combinations of Google Sheets formulas you could use to automate your work.

Trying to master them all would be a complete waste of your time.

Why?

To become proficieint in Google Sheets, you only really need a handful of formulas.

9, to be exact. Make a copy here of the workbook we'll use throughout the post.

Let's dive in!

• 1. ARRAYFORMULA
• 2. SPLIT
• 3. INDEX
• 4. LEFT and RIGHT
• 5. IF and IFERROR
• 6. Working with Dates
• 7. TEXT
• 8. Combining Data Ranges
• 9. QUERY
• That’s all!

1. ARRAYFORMULA

ARRAYFORMULA is a true instrument of laziness. It allows you to write a formula once, and apply it to an entire row or column (an entire "range").

No more copy and pasting formulas 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), and the formula will apply to every cell in the range.

=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

Formula =ARRAYFORMULA(B5:B9 - C5:C9)

Output 

2. 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 become a data engineer.

Formula =SPLIT(B3," ")  

Output

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

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

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

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

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.

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

=TODAY() - 7

=TODAY()

Output (as of publishing)

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

=DATE(YEAR(TODAY()), MONTH(TODAY()), 1)

=TODAY()

Output (as of publishing)

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, for example, sales numbers for only Sundays, WEEKDAY is your best friend.

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

"YYYY-MM-DD"

"#"

"0.000"

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

We 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

8. Combining Data Ranges

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

If you have two datasets living in separate tabs, that 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

Formula ={B4:C4 ; E4:F4}

Output

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

Formula ={B4:C4 , E4:F4}

Output

9. QUERY

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

Say we're looking at Twitter data, and we want to answer the question:

Who tweeted the word 'dashboard'?

Our next question will probably be:

Who tweeted 'dashboard' the most this week? Last week?

Using QUERY lets us explore all of those questions from roughly the same formula, without having to string formulas together (like you'd have to do with FILTER, REGEXMATCH and others).

If you've ever used SQL, then you'll recognize Google queries.

It’s a bit complex to cover in this space, so we devoted an entire exhaustive post to the QUERY function.

That’s all!

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