How to Use the Array Formula in Google Sheets

Key takeaways

The array formula function in Google Sheets gives you the ability to perform advanced operations you would otherwise be unable to complete.

• Using Google Sheets array formula requires just a few steps, including selecting the cell, starting the formula shell, adding specifics, closing the formula, pressing enter, and adjusting the formula if you want to apply it to different ranges or change the function.

• Some of the functions you can perform with ARRAYFORMULA include calculating total values, finding the mean, and combining text values. 

• You can learn more about the various Google Sheets functions to organize and manage your data more effectively. 

Gain a better understanding of this syntax, how to use the ARRAYFORMULA tool for different purposes, and try examples with your own data. If you’re ready to begin moving beyond spreadsheets and learn more about the skills and practices you'll need to become familiar with to work in data analysis, consider enrolling in the Google Data Analytics Professional Certificate. In approximately six months, you'll have an opportunity to go from beginner to career-ready, all while earning a career credential you can add to your resume. 

Reasons to use Google Sheets array formula

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

Steps for using the array formula in Google Sheets

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.

1. Open your data in Google Sheets.

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

What is the difference between sum and the array formula?

The array formula function lets you perform multiple calculations on values within that array, ultimately giving you values across cells. Sum, on the other hand, provides a single value by aggregating the specified cells.

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.

Read more: Google Sheets vs. Excel: What’s the Difference? 

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.

Do more with the array formula function in Google Sheets

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.

How to apply array formula in Google Sheets: 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.

Explore our free data analysis resources 

Sharpen your spreadsheet skills and learn more about careers you can apply them to with our Career Resource Hub, where you can take quizzes and assess your skill set. You can also check out other resources, including the following:

• Read an issue of Career Chat: Analytical thinking: What it is and why it matters more than ever

• Watch a YouTube video: Career Spotlight: Data Analyst

• Check out a tutorial: How to Import XML to Google Sheets

Ready to take things up a notch? Consider subscribing to Coursera Plus. With your monthly or annual subscription, you’ll gain access to more than 10,000 programs from over 350 world-class universities and organizations. 

Build job-ready skills with Coursera Plus

Start 7-day free trial

• 
• 
• 
• 

Start 7-day free trial