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 master Google Sheets formulas, you only really need a handful.

9, to be exact.

I’m going to walk you through each of the 9 key Google Sheets formulas that you need to become a master.

I put together a Google Sheets formulas cheatsheet that you can use to follow along with this post. Grab the workbook through the link below.

I’m not asking for your email address (I know that’s annoying). But if you do get value from this post, I’m hoping you will check out my YouTube Channel where I have even better tutorials.

Let’s get into the need-to-know formulas!

This one confused me for a long time. But once I figured out how to use ARRAYFORMULA, I’ll never go back.

This is a true 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).

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().

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 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**

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

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

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:

- Stack ranges on top of each other, by separating them using ;
- 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 |

The big kahuna of Sheets functions. QUERY lets you combine all of them 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).

https://codingisforlosers.com/google-sheets-query-function/

** 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 monster post to it. Learn to master the Google Sheets query here.

I know that’s a lot of material to pick up off the bat, so feel free to grab the cheat sheet 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.

Get off the ground quickly, with customizable data pipeline Recipes for BigQuery.

GET COOKING