How to Use ARRAYFORMULA in Google Sheets

This article explains how to use ARRAYFORMULA in Google Sheets so that you can input more data points into your calculations.

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.

1. For this example, ARRAYFORMULA will collate first and last names from two columns in a Google Sheet.

   

2. Normally, to collate the names in the second row into the third column, you'd use an "&" formula. In this case,you use two ampersands in the formula, like this:

   =(B2&", "&A2)
   
   

   

3. Press Enter to run the formula. The result will replace the formula text.

   

4. To apply the formula to the entire column automatically, you'll add ARRAYFORMULA and make some small changes to the argument. Like any other formula in Google Sheets, ARRAYFORMULA goes after the equals sign but before the argument.

   To make changes to the formula, click its text in the entry field.

   

5. Just adding the ARRAYFORMULA command won't fill the rest of the column because you need to tell Google Sheets to use all of the data. To do so, use the proper notation. In Sheets (and other spreadsheet programs), you use a colon (:) to define a range. In this example, the ranges are B2:B and A2:A.

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

   Your input arrays must be the same size. For example, if you ran ARRAYFUNCTION on a range of three cells in Column A and two cells in Column B, one result would come back as an error, but the valid arguments would still run.

   

6. Press Enter to run the formula and fill the rest of the cells.

   

7. As you add more entries, the column with the ARRAYFORMULA will update.

   

8. If some of your data changes, updating it will also modify the result.

   In this example, cells in Column C next to empty cells in Columns A and B contain commas because they were part of the original collation formula. They won't necessarily appear for other functions.

   

9. To modify the results, you just need to return to the cell with the ARRAYFORMULA function and change it. The rest of the results will update automatically.

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.

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.