How to Use ARRAYFORMULA in Google Sheets
You use ARRAYFORMULA like any other function, but you never use it on its own. It always precedes another equation or command to tell the program it needs to use and, possibly, return multiple sets (arrays) of information. Here’s an example.
What Is Google Sheets ARRAYFORMULA?
The ARRAYFORMULA function in Google Sheets works alongside several other calculations to let you include more data points. Instead of a single number or cell, this command lets you include more information to your calculations and output more granular results. To make changes to the formula, click its text in the entry field. This notation includes all of Columns A and B except for the first row, which contains the headers. In other applications, you would use something like B:B to use an entire column or B2:B12 to include a specific range (in this case, Rows 2 through 12 of Column B). For example, if you try to multiply two ranges of cells together, Google Sheets will return an error because the multiplication function only knows how to find the product of two or more discreet numbers (for example, 4 times [the value in Cell A1]). Adding ARRAYFORMULA, however, tells Google to account for more information and use it differently from normal.
Uses of ARRAYFORMULA
The above example is just one way to use ARRAYFORMULA. It works with most functions in Google Sheets, and you don’t even have to export an array. For example, if you’re creating an expense report, you can set up an ARRAYFORMULA function to multiply the price of an item by the number you bought and then add all of the costs together. This function uses multiple equations to create a single, useful bit of information based on elements you can update in the future.
Why Not Fill Down?
In some cases, you can get the same results by entering a formula once and then dragging the bottom-right corner of the cell down or across to copy it to all of the rows or columns you highlight. If you have a lot of information you update regularly, however, ARRAYFORMULA will save you time. You don’t need to keep filling down as your data set outgrows the range you’ve copied; it will update automatically as you enter new items. The other major advantage of ARRAYFORMULA is if you ever need to update the formula, you won’t have to then copy it to every result field. For example, if you decide you want to add two arrays instead of multiplying them, you only need to change the one value in the ARRAYFORMULA box, and it will automatically populate the others. If you fill down, you’ll need to adjust each output field, which creates more work even if you use the fill down function again.
