How to Use the Array Formula in Google Sheets

With array formulas, you can perform advanced operations that are impossible with standard formulas. You might use an array to compute a singular value from a string of operations or go line by line to output a new collection of values. Google Sheets offers a convenient array formula with the syntax ARRAYFORMULA(array_formula).

By the end of this tutorial, you will understand this syntax, how to use the ARRAYFORMULA tool for different purposes, and try examples with your own data.

Steps for using the array formula in Google Sheets

Here's a summary of the steps you'll follow to use this powerful tool:

1. Open your data in Google Sheets.

2. Select the cell for the ARRAYFORMULA function.

3. Start the formula shell.

4. Specify the function, ranges, and values.

5. Close the formula and press 'Enter.'

6. Adjust the formula as needed.

Let's take a look at each step in detail so you can get started.

1. Open your data in Google Sheets.

Begin by opening Google Sheets in your web browser with the data you want to use. 

2. Select the cell for the ARRAYFORMULA function.

Select the cell where you want the result of your ARRAYFORMULA to appear. Make sure no text already exists in this cell.

3. Start the formula shell.

To begin the formula shell (i.e. the formula’s starting point), type an equal ( ‘=’ ) symbol in the selected cell, followed by ARRAYFORMULA and open parenthesis ( ‘(’ ) to indicate you will be creating a function. 

4. Specify the function, ranges, and values.

Inside the ARRAYFORMULA function parenthesis, you can specify the function or operation you want to perform on the array. After the function or operation, you will separate the arguments with commas. Specify the ranges or individual values you want to include in the array.

In the below example, we will multiply the values of each row of the two columns together to create a third column.

Note: When selecting a function, you can use SUM to calculate the total of numerical values, AVERAGE to find the mean, or CONCATENATE to combine text values. If you’re using CONCATENATE, you’ll need to type the text or cell references you want to combine. For example, to combine Value 1 and Value 2, you would type =CONCATENATE(‘Value 1’, ‘Value 2’) to display Value 1Value 2 as the result.

5. Close the formula and press 'Enter.'

Close the formula by adding a closing parenthesis ) at the end. The complete formula should look something like this: =ARRAYFORMULA(G2:G14*H2:H14)

Press Enter to apply the ARRAYFORMULA to the selected cell.

6. Adjust the formula as needed.

If you want to modify the ARRAYFORMULA function or apply it to different ranges of values, you can edit the formula in the cell. You can do this by clicking on the cell containing the formula, making the necessary changes, such as using the keyboard to select a new range of cells or turning on 'Range selection mode,' and pressing Enter to update the result.

If you see a suggested correction, a 'Formula Correction' box will appear after you type your formula for a given cell range. You can choose to Accept or Dismiss this suggestion.

In the screenshot below, we have altered the formula to include only rows 2:10 rather than rows 2:14.

Explore additional ARRAYFORMULA functions

Taking time to experiment with the ARRAYFORMULA function may help you learn how to use the function for your particular data set. For example, if you want to concatenate the values in the two columns (link them together), you could type the following expressions:

=ARRAYFORMULA(CONCATENATE(G2:G14, H2:H14)) or =ARRAYFORMULA(G2:G14 & H2:H14)

In this example, these formulas would create a separate column where the two values in each row are combined into a single string, as shown below. 

Additional ARRAYFORMULA examples to explore include:

• =ARRAYFORMULA(SUM(G2:G14,H2:H14)): This would return one singular value that is the sum of all the values in cells G2 through G14 and H2 through H14.

• =ARRAYFORMULA(AVERAGE(G2:G14,H2:H14)): This would return one singular value that is the average of all the values in cells G2 through G14 and H2 through H14.

Common errors to avoid

If you are going to export your Google Sheet, it is important to note that you cannot export the  ARRAYFORMULA function. Other potential errors may include syntax errors, misspecifying the operations you would like to perform, or incorrect formatting. These typically lead to parse errors in Google Sheets.

Learn more on Coursera

For an immersive data analysis experience, consider completing the Google Data Analytics Professional Certificate on Coursera. With this Professional Certificate, you can learn powerful data management, visualization, and analysis tools that can set you up to begin an entry-level position in data analytics.