Google Sheets Function: ARRAYFORMULA

The ARRAYFORMULA function allows displaying values from an array formula across multiple rows and/or columns, and to use with arrays functions that do not support them.

The examples below should help you to better understand the utility of this very special function and how to use it.

Usage:

=ARRAYFORMULA(array_formula)


Simple usage example

For a simple addition (and without a function), you must enter the following formula =A2+B2:

google sheets simple addition arrayformula

You then need to copy the formula down:

google sheets formula copydown arrayformula

In this case, there is a formula in each of the 8 cells.

Well, the ARRAYFORMULA function allows displaying the same results but by entering the formula once and in a single cell!

To do this, enter the ARRAYFORMULA function then A2+B2 and replace the cell references with the entire ranges A2:A9+B2:B9.

The formula here is:

=ARRAYFORMULA(A2:A9+B2:B9)
google sheets function arrayformula example

The formula (entered only in cell C2) will then return the result of the addition for each pair of numbers:

google sheets function arrayformula result

Usage example with a function

In the following example, the IF function checks if the quantity has been entered and then calculates the total amount (this example is a copy of that from the IF function):

=IF(B2<>"";B2*C2;"")
google sheets function if non empty

To replace this column of formulas with an ARRAYFORMULA, copy the IF function into the ARRAYFORMULA and replace the cell references with references to the cell ranges (for example, instead of B2, enter the range B2 to B7).

The formula then becomes:

=ARRAYFORMULA(IF(B2:B7<>"";B2:B7*C2:C7;""))
google sheets function arrayformula if

The formula (entered only in cell D2) will therefore return the result for the 6 lines:

google sheets function arrayformula if condition
If needed, you can copy the Google Sheets document (or view the document) with these examples.
To automatically add the ARRAYFORMULA function to the current formula, press Ctrl + Shift + Enter.