Written by Coursera Staff • Updated on

Explore how to use the ARRAYFORMULA function in Google Sheets, discover example formulas to try, and learn what common errors to avoid.

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.

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

Open your data in Google Sheets.

Select the cell for the ARRAYFORMULA function.

Start the formula shell.

Specify the function, ranges, and values.

Close the formula and press 'Enter.'

Adjust the formula as needed.

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

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

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

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.

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.

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.

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.

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.

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.

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.

Updated on

Written by:### Coursera Staff

C

Editorial Team

Coursera’s editorial team is comprised of highly experienced professional editors, writers, and fact...

This content has been made available for informational purposes only. Learners are advised to conduct additional research to ensure that courses and other credentials pursued meet their personal, professional, and financial goals.