19 July 2021

This article looks at using the ARRAYFORMULA function in Google Sheets to make your spreadsheet more robust. It covers how to use it, how to break it, and how it differs from the Excel equivalent.

Creating a Google Sheets spreadsheet for your personal use is one thing. You built it, you'll maintain it, so it can be as beautifully complex and as idiosyncratic as you are.

But creating a spreadsheet for a group of other people to use is something else entirely. And it's a fairly common scenario with Google Sheets - after all, one of the benefits of Sheets (and the entire Google Docs suite) is how easy it is to share and collaborate with others.

Your group may well have mixed abilities, so your spreadsheet will need to be simple (both to update and interpret) and robust. That is, you want to minimise the chance of user error leading to incorrect results - especially if those incorrect results may not get noticed straight away.

One way to make your Google Sheets spreadsheet more robust is through considered use of the ARRAYFORMULA function. You can use a single ARRAYFORMULA to perform the same operation on a range of cells. This removes the need for each and every cell in that range to contain a formula. That in turn reduces the chance of a spreadsheet user accidentally changing the formula in a cell (or a formula being missing entirely).

For example, let's say you create a spreadsheet for a company that sells two products, widgets and sprockets. The spreadsheet itself lists each sales rep along with the number of widgets and sprockets they have sold, then calculates each rep's total item sales. The formula here in column D is as simple as it gets, just:

=B2+C2

Or if you want to be fancy:

=SUM(B2:C2)

Of course, you will need an equivalent formula in every cell in column D for the spreadsheet to work as intended:

What happens when the sales manager comes to add another sales rep (let's call him Brian) to the spreadsheet? Now, before you handed the spreadsheet over, you could have done the sensible thing and dragged the formula down all of column D. This would mean the formula is there waiting in cell D5, ready to calculate Brian's total sales.

But the sales manager does something unexpected and inserts a new (perfectly blank) row between rows 3 and 4, in order to insert Brian between Berta and Carlos and maintain the alphabetical order of the sales reps. This means there is no formula in the new cell D4.

And because there is no formula, our manager just works out Brian's total sales in her head and manually enters that number into the spreadsheet. Big deal, right? The spreadsheet is still accurate.

Later though, Brian sells one more widget and updates the spreadsheet himself - without noticing that his total sales figure doesn't change as a result:

Later still, the sales manager looks at each rep's total sales and sees that Brian is below target by just a single item. Brian misses out on his bonus that year, his son doesn't get that new bike for Christmas, and all because a formula was missing from a single cell.

A more robust alternative would be to use the ARRAYFORMULA function in cell D2 (and only D2). Let's look at how...

Inserting an ARRAYFORMULA in Google Sheets is straightforward. Type in your formula as usual, and hit Ctrl+Shift+Enter (instead of just Enter). Your formula will automatically be converted into an array formula. Doing this with our previous formula gives us the following:

=ARRAYFORMULA(B2+C2)

But hang on, that only applies the formula to cell D2, which isn't much use. We need to tweak the cell references within the formula so they reference the entire column from row 2 downwards. This give us:

=ARRAYFORMULA(B2:B+C2:C)

And voila, this instantly fills in the values for the rest of column D. In other words, we can see the total sales for each sales rep without having to put a formula in each cell:

If the sales manager comes along now and adds a blank row anywhere within the range, it will still be covered by the array formula.

What if our troublesome manager insists on manually adding a value to a cell in column D (despite this column now being calculated automatically)? Well, it will interrupt the array and cause all of the totals to disappear:

This may sound disastrous, but it reduces the chance of the problem going unnoticed and decisions then being made on incorrect data. Spreadsheets will always break, so let's at least make them break in obvious and dramatic ways.

And the error message here is actually helpful: not only does it explain the problem, it gives the reference for the offending cell. Simple remove the value from that cell, and the array result will be expanded over the entire range again.

Have I converted you over to using the ARRAYFORMULA function in your next Google Sheets project? If you are passing it over to other users, you may want to tell them that their new spreadsheet contains array formulas - and briefly explain the benefits.

You may also want to indicate on the spreadsheet which columns they *shouldn't* fill in manually, otherwise they will be very surprised the first time that they inevitably do so and all their calculated totals disappear.

Formulas in Google Sheets are not case sensitive, so you can happily use uppercase =ARRAYFORMULA or camelcase =ArrayFormula (as in the above screengrabs), or indeed any other variation of case you like.

When you use Ctr+Shift+Enter to add an array formula, Google Sheets will default to the camelcase version. But if you start typing your formula and then add an array formula from the dropdown list of suggestions, it will be in uppercase. Go figure!

Although array formulas also exist in Excel, they have several major differences to those in Google Sheets. First, Excel indicates array formulas using curly brackets at the start and end rather than with an ARRAYFORMULA function. So an array formula like this in Google Sheets:

=ARRAYFORMULA(B2:B3+C2:C3)

would look like this in Excel:

{=B2:B3+C2:C3}

Note that you can't type in the curly brackets yourself - you must use Ctrl+Shift+Enter to add your array formula. In fact, array formulas in Excel are sometimes known as CSE formulas (CSE standing for, of course, **C**tr+**S**hift+**E**nter).

And most significantly, array formulas in Excel don't accept what's called an 'infinite range'. So an array formula like this in Google Sheets, where entire columns are referenced:

=ARRAYFORMULA(B2:B+C2:C)

...is simply not possible in Excel. That makes array formulas much more powerful and versatile in Google Sheets, at least in my opinion. And hopefully it also explains why array formulas in Google Sheets warranted a dedicated article.

Subscribe

0 Comments

Copyright © James Clark

0

What do you think? Leave a commentx