Google Sheets Formulas
none
ARRAYFORMULA Function in Google Sheets: Explained
SHARE THIS Article
In this article, you will learn what the ARRAYFORMULA function is and how to use it.
What is the ARRAYFORMULA function in Google Sheets?
Google’s explanation about the function of the ARRAYFORMULA is as follows: the ARRAYFORMULA Enables the display of values returned from an array formula into multiple rows and/or columns and the use of non-array functions with arrays.
What does this definition mean? The ARRAYFORMULA has the following two features:
The ARRAYFORMULA returns values across cells rather than a single value.
The ARRAYFORMULA allows you to input arrays in formulas that generally don’t accept arrays as an argument or don’t work correctly with arrays (non-array functions).
See examples in the later section and learn how it works more specifically.
Save hours formatting your next financial spreadsheet!
100+ templates, all free to use.
Free Spreadsheet Templates
How to insert the ARRAYFORMULA function in Google Sheets
Standard way
Type “=ARRAYFORMULA” or navigate to the “Insert” tab (or “Functions” icon) → “Function” → “Google” → “ARRAYFORMULA”.
Insert a formula you want to run in the ARRAYFORMULA
Press the “Enter” key.
Faster way with a shortcut
Insert a formula(s) you want to run
Press “Ctrl”+”Shift”+”Enter” (for Windows) or “⌘”+”Shift”+”Enter” (for Mac), by which the ARRAYFORMULA encloses the inserted formula(s).
Press the “Enter” key.
The general syntax is as follows:
Array_formula: A range, mathematical expression using a cell or multiple ranges of the same size, or a function that returns a result larger than one cell. If you insert ranges, they must be the same size.
How to use the ARRAYFORMULA in Google Sheets
The ARRAYFORMULA returns values across cells rather than a single value.
Imagine you have a data set, as shown in the picture below. You want to calculate the sales amount of each product and the total sales.
Level up your Google Sheets skills with our FREE LiveFlow Academy
Basic and intermediate classes live now! Earn your certificate today.
Enroll in LiveFlow Academy
As you want to reduce the number of copies and pastes, instead of conducting multiplication for the first row and copying and pasting it for the other products, you enter the formula, as shown in the screenshot below.
You expect the formula to calculate each sales amount automatically. However, it doesn’t work as you wish because the basic operators, such as “+” and “*”, do not accept ranges as input.
You can utilize the ARRAYFORMULA here - Press “Ctrl”+”Shift”+”Enter” (for Windows), or “⌘”+”Shift”+”Enter” (for Mac) and push the “Enter” button. Now you have the ARRAYFORMULA, which automatically spreads its results into the other cells.
The ARRAYFORMULA allows non-array formulas to be used with arrays.
This time, you think it is excellent to get the total result from a formula expression in a cell. So, you enter the formula “=SUM(C16:C20*D16:D20)”, as shown in the following screenshot. However, it doesn’t act as expected because the SUM formula can’t accept multiple arrays as a single argument.
Combining the ARRAYFORMULA and SUM formulas helps you get the result directly. With the SUM function in a cell, Press “Ctrl”+”Shift”+”Enter” (for Windows) or “⌘”+”Shift”+”Enter” (for Mac) and push the “Enter” button.
Learn how LiveFlow can save you hours a month on financial reporting!
Just 30 minutes can change the way your business operates forever.
Book a Demo
The ARRAYFORMULA operates a multiplication for the first row by picking up a value in the first range and a corresponding value from the second range (e.g., 20*90=1800 for the first row).
It automatically repeats the process for other values in the selected area, as you saw in the first example beforementioned. Then, the SUM formula aggregates the amounts as usual because the SUM function can recognize them as proper inputs.
Supercharge your financial reporting