How to use Google Sheets ARRAYFORMULA

google-sheets-arrayformula

Written by Valentine Schelstraete

Nov 6, 2020

Google Sheets ARRAYFORMULA is one of the key advanced functions you should have up your sleeve if you spend a lot of time working in spreadsheets. Unlike standard functions, array formula is expandable — automatically incorporating new information into the calculation when new data is added.

Array formulas make your spreadsheets scalable

Array formula offers two helpful benefits. 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 ARRAYFORMULA works, let’s look at how we usually use functions.

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

array-formula-image-1

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 in this method:

  • 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 functions 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 anything or add new formulas.

Syntax

ARRAYFORMULA(array_formula)

Handy shortcut: After you enter a (non-array) function, just hit Ctrl+Shift+Enter and Google Sheets will automatically add ARRAYFORMULA() around it, quickly converting it into an array 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

As you can probably tell, ARRAYFORMULA is very open-ended, which allows it to be used for a wide variety of purposes and in combination with many other functions. This is the benefit of ARRAYFORMULA – it can be used to do almost anything to a large data set, all from one function!

How to use Google Sheets ARRAYFORMULA

Example 1: Basic 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 functions so that ARRAYFORMULA gives the correct results without errors.

The equation that I’ve typed here is =ARRAYFORMULA(B2:B+C2:C+D2:D+E2:E).

arrayformula-google-sheets-2

Example 2: IF statement with ARRAYFORMULA

To quickly fix the zeroes at the end, just change the formula in one place (i.e. cell F2). This will affect the calculations across the whole range, as shown below.

I’ve added an IF statement that tells the function to leave the Total column blank if there is no name in column A, by typing =ARRAYFORMULA(IF(A2:A<>””,B2:B+C2:C+D2:D+E2:E,””)).

Notice that even the expression used in the condition within the IF formula is a range (A2:A).

arrayformula-google-sheets-3

Now, let’s include a row for Charlotte beneth row 7 to see if the ARRAYFORMULA automatically calculates the total for her.

arrayformula-google-sheets-4

As you can see, it does!

This is a significant benefit of using ARRAYFORMULA. When you have a dataset that changes often, using ARRAYFORMULA will let you make your changes without having to adjust any of the equations in the spreadsheet.

Example 3: Horizontal Arrays

So far, the examples that I’ve shown have all been vertical arrays, that go through the data top to bottom to calculate a value. The ARRAYFORMULA can also be used just as easily for horizontal arrays, which allows you to use it no matter the format of your data.

In the example below, I’ve calculated the same Totals for the test scores, but in a horizontal format. I do this by typing =ArrayFormula(B2:2+B3:3+B4:4+B5:5).

arrayformula-google-sheets-5

Note that instead of telling the function to go down the list (e.g. from A2:A, which means starting at A2 and going down through column A), I’ve told it to go across the data (e.g. from B2:2, which means starting at B2 and going across through row 2).

By making this simple change, the ARRAYFORMULA is now working horizontally. And just like in the previous example, to get rid of the zeros in the blank columns, I can add an if statement to the function: =ARRAYFORMULA(IF(B6:6<>””, B2:2+B3:3+B4:4+B5:5,””)).

Example 4: Multidimensional Arrays

You’ve now seen the ARRAYFORMULA used in both vertical and horizontal orientations, but what about a table that uses both of these?

As you can see in the example below, this can be done very easily with ARRAYFORMULA. Here I’ve created a multiplication table by typing =ArrayFormula(B1:J1*A2:A10) to multiply column A by row 1.

arrayformula-google-sheets-6

Example 5: Using ARRAYFORMULA with text

You’ve now seen lots of uses of ARRAYFORMULA with numbers. But remember, ARRAYFORMULA is extremely versatile, which means you can use it to work with text as well.

In the example below, I’ve added two columns that show the first and last names of some of the students from the previous examples. In the third column, I’d like to combine these to get their full names. By typing =ArrayFormula(A2:A5&” “&B2:B5), I can achieve this very efficiently.

arrayformula-google-sheets-7

A final note about array formula:

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

You may also like…

Share This