5 Google Sheets Functions for Automation

Most of us use Google Sheets (or Excel) every day. Probably without even thinking. How many times have you opened a sheet today? I'll bet it's more than you realised when you stop and think about it.

That's why Google Sheets is where I start when I think about automation.

And a lot of the time, it doesn't get more complicated than a few well planned and written functions.

So I'm going to share the 5 that I use the most. I bet you'll be surprised at how simple some of them are. But the simplest solution is usually the best. And once you know the basics, you can combine what you've learned to make super smart sheets.

If you're ready to be the go-to "Google Sheets" person in your office, read on...

Function #1: VLOOKUP

Yes, the humble VLOOKUP. Sure, you hear a lot these days about HLOOKUP, XLOOKUP or the plain LOOKUP. But for me, you can't beat the original.

Everyone knows what it's doing and how it works. And that's a big win - remember you're not the only one who's going to be using your sheets.

If you're mapping in an extra column from another data source you really can't beat it. And as a bonus tip - if you need to lookup based on more than column:

• Create a combined field in your lookup table using the & symbol (ie =A1&B1&C1)
• Use the same symbol in your VLOOKUP: =VLOOKUP(G1&H1&I1, 'Sheet1'!D1:E100, 2, 0)

Function #2: SUMIFS

I LOVE SUMIFS. And all its sister functions which work the same way (COUNTIFS, COUNTUNIQUEIFS, MAXIFS, MINIFS).

It's so versatile. That comes from being able to add "operators" to the values.

For example, if you want to add all the numbers in Column A, but only if Column B contains "Red", you could write: =SUMIFS(A:A, B:B, "Red"). The asterisks are the "operators". They tell the function to count all rows that contain "Red", not just the ones that are exactly "Red".

Asterisk actually means "any or no text". So Red means: Any text which contains "Red", plus any number of (or no) characters either side. So if you wanted to filter for values which began with "Red" you could write: =SUMIFS(A:A, B:B, "Red*"). Note how the asterisk is now only on one side this time.

And * is far from the only operator you can use. Greater than (>) and less than (<) work for both numbers and dates. I group data by month using > and < all the time.

A great use of SUMIFS is with dropdown fields. Create a dropdown field (using Data Validation) and include that value as a condition. When someone changes the dropdown, your calculation will automatically update. Everyone loves a dynamic sheet that's super easy to use!

Function #3: INDEX/SPLIT

This one is a bit of a cheat, it's really two functions. But with my background in digital marketing, the land of the naming convention, I can't not include them.

We've all used reports with a super long campaign name in the first column. You know the type. Loads of pipes/underscores/hyphens separating a mixture of useful, and not-at-all-useful sections.

Combining INDEX and SPLIT means we can fetch the useful bits and ignore the rest! And without using Text To Columns.

SPLIT is pretty simple - you give it your long name and the character that splits it up (the "delimiter"). On its own, it works the same as Text To Columns and spreads the name across your sheet.

That's where INDEX comes in. INDEX takes the output of the SPLIT, and lets us choose which part we want: =INDEX(SPLIT(A1, " | "), 1, 3)

Here, we're splitting the value in A1 using a pipe as the delimiter. Then we're taking the first row of that result and the third column.

Think of INDEX as expecting a mini spreadsheet as its input. You just tell it which row and column you want. In cases like this, there will only ever be one "row" because we're splitting one name into columns. So all you need to do is choose the column!

Function #4: ARRAYFORMULA

Using ARRAYFORMULAs in Google Sheets is an absolute must. How many times have you seen calculations go wrong because someone forgot to copy their VLOOKUP all the way down!?

ARRAYFORMULA means you can breathe easy when it comes to things like that. You only need to write your VLOOKUP once, and never worry about it again.

My top tip for using ARRAYFORMULA is to combine it with an IF and IFERROR:

Function #5: QUERY

QUERY is the most advanced function on this page by a long way. Which is why I've left it until last - only the real spreadsheet enthusiasts have made it this far...

QUERY takes part of your spreadsheet as an input and lets you write SQL-like queries to manipulate your data. SQL is the language of databases, designed for data handling and analysis.

The structure of a very basic QUERY is: SELECT columns WHERE conditions. But you can go a lot further than that.

In the SELECT clause, you can add functions like SUM, AVERAGE, COUNT. And in the WHERE clause you can use conditions like CONTAINS, as well as equal/greater/less than.

I use QUERY when I have a big table with a lot of columns that I don't need. Being able to pull in only the columns I want, re-order them, and sum up any numerical values is a huge win.

Frankly, given the choice between using a Google Sheets pivot table and a QUERY - I'm taking the QUERY every day of the week. It's that good.

And if you want to learn more about these (and other) functions and how to use them, get in touch today!