How to Use ARRAYFORMULA in Google Sheets GA S REGULAR Menu Lifewire Tech for Humans Newsletter! Search Close GO Software & Apps > Google Apps

How to Use ARRAYFORMULA in Google Sheets

Quickly process data in batches

By Evan Killham Evan Killham Writer University of Nebraska Evan Killham has been writing about tech and pop culture since 2008. His work has appeared in publications that include Fandom, VentureBeat, and ScreenRant. lifewire's editorial guidelines Updated on March 7, 2021 Tweet Share Email Tweet Share Email Google Apps Sheets Docs Slides

What to Know

The ARRAYFORMULA allows you to multiply a range of cells (an array) instead of two cells.An example of an ARRAYFORMULA: =ArrayFormula(SUM(C3:C9 * F3:F9)).Input arrays must be the same size; the two cell ranges must have an equal number of data points. 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. For this example, ARRAYFORMULA will collate first and last names from two columns in a Google Sheet. 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)
Press Enter to run the formula. The result will replace the formula text. 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. 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. Press Enter to run the formula and fill the rest of the cells. As you add more entries, the column with the ARRAYFORMULA will update. 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. 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. Google Sheets can only use ARRAYFORMULA with arrays of the same size (i.e., containing the same number of data points).

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. Was this page helpful? Thanks for letting us know! Get the Latest Tech News Delivered Every Day Subscribe Tell us why! Other Not enough details Hard to understand Submit More from Lifewire How to Highlight and Find Duplicates in Google Sheets How to Remove Duplicates in Google Sheets How to Use Google Sheets How to Use the Excel INDEX Function How to Sum Columns or Rows in Google Sheets Learn How to Remove Extra Spaces From Excel Using TRIM How to Wrap Text in Google Sheets How to Use the Round Function in Excel How to Calculate Percentage in Excel How to Use the IF-THEN Function in Excel Perform Multiple Calculations With Excel Array Formulas Relative, Absolute, and Mixed Cell References in Excel and Sheets How to Use the Concatenate Function in Google Sheets How to Use Functions in Google Sheets How to Calculate Weighted Averages in Excel With SUMPRODUCT Excel Math: How to Add, Subtract, Divide, and Multiply Newsletter Sign Up Newsletter Sign Up Newsletter Sign Up Newsletter Sign Up Newsletter Sign Up By clicking “Accept All Cookies”, you agree to the storing of cookies on your device to enhance site navigation, analyze site usage, and assist in our marketing efforts. Cookies Settings Accept All Cookies