What is an array formula in Excel?

An array formula allows you to perform multiple calculations at once, or, it can perform one or more calculations multiple times within a selected cell range.

The values referred to in these formulas can appear as values in a row, in a column, or in a matrix (rows and columns). To explain the array formula more thoroughly, it is easier to show you some examples.

Without the array formula

In the example below you’ll find a very simplistic sales sheet with different fruits (A2:A9), the amount sold per day (B2:B9) and the price per product (C2:C9). The calculation of the revenue per fruit is now performed in each cell of the revenue column (D2:D9).

This calculation can be done faster using a multi cell array formula.

Multi cell array formula

The multi cell array formula will return multiple results within a row or column, using a single formula. In order to calculate all values of the column ‘Revenue per fruit’ in the example above at once, we start by selecting the cell range in which we want to publish the results and we press F2 to select the first cell in the range:

Then we enter the array formula in selected cell D2:

BUT HOLD YOUR HORSES! When you press ‘Enter’ after writing the formula, Excel will only calculate the result of the current row! This type of formula is also called a CSE formula, because it will only execute correctly when using CTRL+SHIFT+ENTER.

As you can see, the formula calculated all the values of the array at once. Excel added braces { } to the formula in the formula bar, to indicate that it is an array function.

Single cell array formula

A single cell array formula will return one result within one cell, while performing one or multiple calculations.

In our example, the total fruit revenue can of course be calculated using the SUM function on the values in the ‘Revenue per fruit’ column:

But when using the array function, we don’t need the intermediate results to calculate the grand total. We simply select the cell in which we want to publish the total fruit revenue and we enter the formula ‘=SUM(B2:B9*C2:C9)’:

Again, remember to click CTRL+SHIFT+ENTER to reveal your results! Only pressing Enter will result in a standard Excel error: ‘#VALUE!’. After executing the function correctly, the grand total will show in the designated cell and again, braces { } will appear around the initial formula to indicate it concerns an array formula now.

Array functions are risky

We consider array functions in Excel a risk, because it makes cooperation with one spreadsheet very error prone. Your co-worker might not be familiar with these functions and change something without pressing CTRL+SHIFT+ENTER… That’s why PerfectXL detects all formulas of this type and marks them as a potential risk.