Google Sheets ARRAYFORMULA is one of the key advanced formulas you should have up your sleeve if you spend a lot of time working in spreadsheets. Unlike normal formulas, array formula is expandable — automatically incorporating new information into the calculation when new data is added.
Making spreadsheets scalable
Arrayformula offers two helpful functions. Whereas a normal formula gives you a single value, Google Sheets array formula can output a whole range. An array formula also allows you to use arrays in non-array functions. It will save you time and help your spreadsheets work more efficiently. You just enter the formula in one cell and it will expand down the entire range. To explain how array formula works, let’s look at how we usually use formulas.
We often tend to use structurally similar formulas throughout the length of a column in a data range. In doing so, we take advantage of “relative referencing”. This means Google Sheets automatically adjusts the formula if you copy and paste it into subsequent rows.
Let’s look at a basic example. This dataset is a list of students and their test scores. I want to calculate the totals in column F:
The total score for Alfred is calculated using a simple formula “=B2+C2+D2+E2”. I copy-paste this formula into the cells below to repeat the same calculation for each student. So, for Angela, the formula automatically becomes “=B3+C3+D3+E3”. For Bob it’s “=B4+C4+D4+E4”, and so on.
Though this approach might seem convenient, there are some inherent problems
- If you’ve got a huge dataset, you will end up with a lot of formulas. This could bloat the spreadsheet and slow it down.
- If you need to make any changes to the formula, you will have to repeat it for every formula in the sheet.
- What if a new student, Charlotte, joins the class? If you include a new row for her (row 7, see below) Google Sheets does not copy the formula automatically. In effect, this approach is not dynamic enough.
How ARRAYFORMULA solves these problems
- As opposed to a bunch of similar formulas that individually calculate values, you can use one single ARRAYFORMULA that processes the data in a batch.
- Since this is one single formula, you can make changes in just one place and it will be applied across the entire data range.
- Unlike individual formulas, ARRAYFORMULA is dynamic. When a new row is introduced, you don’t need to change or add new formulas.
Handy shortcut: When you enter your formula, just hit Ctrl+Shift+Enter and Google Sheets will automatically add ARRAYFORMULA( to the start of the formula.
- array_formula – this parameter can either be
- a range
- a mathematical expression using one cell range or multiple ranges of the same size, or
- a function that returns a result greater than one cell
How to use Google Sheets ARRAYFORMULA
Building on the previous example, in order to calculate the totals, you can use the ARRAYFORMULA as shown below. Notice that, unlike before where I added individual cells, I am now adding the ranges. And I type this into the very first total cell, F2. All of the following Total cells should be clear of any values or
To quickly fix the zeroes at the end, just change the formula in one place (i.e. cell F2). It will affect the calculations across the whole range, as shown below.
Notice that even the expression used in the condition within the IF formula is a range (A2
Now, let’s include a row for Charlotte beneth row 7 to see if the ARRAYFORMULA automatically calculates the total for her.
As you can see, it does!
A final note about Arrayformula:
Remember that the two arrays must be the same size, e.g. C4:C10*D4:D10, so that Google Sheets can carry out the calculation.
Are you looking to constrain an array result to a specific size? Learn about how to use the ARRAY_CONSTRAIN formula in Google Sheets.
Editor’s note: This is a revised version of a previous post that has been updated for accuracy and comprehensiveness.