In this article, you will learn what the ARRAYFORMULA function is and how to use it.
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:
See examples in the later section and learn how it works more specifically.
Faster way with a shortcut
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.
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.
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.
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.
Are you learning this formula to build a financial model or financial KPI dashboard, or conduct financial analysis? If so, LiveFlow may help you to automate manual workflows and keep numbers updated in real time. For instance, you can easily compare your Budget versus Actual without any manual work. Furthermore, you can access other templates here, from the simple Income Statement and Operational KPI templates to Multi-Currency Consolidated Income Statement, Balance Sheet, and Cash Flow templates. You can also customize these templates as you want without breaking the automated data inflow.
To learn more about LiveFlow, book a demo.
Learn how to do this step-by-step in the video below 👇