Using ARRAYFORMULA in Google Sheets

In this previous post we discussed how to use IFERROR, referring to this little spreadsheet:

Click image to enlarge

Click image to enlarge

In the example given I copied code from D2 to D3 and D4; the code in each of these cells referenced the B- and C-column cells of the same row.

There is, however, a means to specify an array of values to use with a formula or function, instead of using the value from a single cell. To do so, wrap your expression with ARRAYFORMULA. So in D2 I could instead write:

=ARRAYFORMULA(IFERROR(B2:B4/C2:C4, "An error occurred"))

and leave cells D3 and D4 empty.

ARRAYFORMULA then applies the calculation expressed in the IFERROR function to the specified range of cells, outputting the results to the equivalent range of cells starting with D2:

Click image to enlarge

Click image to enlarge

As a further quick timesaving tip, pressing Ctrl+Shift+Enter while editing a formula will automatically add ARRAYFORMULA( to the beginning.


Leave a Reply

Your email address will not be published. Required fields are marked *