Excel glossary

What is an Array Formula?

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 find different fruits in (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 can be done faster using a multi cell array formula.

Repetitive calculation without array formula // PerfectXL

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:

Select a cell range to perform an multi cell array formula // PerfectXL

Then we enter the array formula in selected cell D2:

Enter the array formula in selected cell // PerfectXL

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.

Result of a multi cell array formula // PerfectXL

As you can see, the formula calculated all the values of the array at once. Excel added braces { } to the formula 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:

Revenue calculation with SUM function // PerfectXL

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)’:

Example of a single cell array formula // PerfectXL

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

Result of a single cell array formula in Excel // PerfectXL

Array Functions are Risky

We consider array functions a risk, because it makes cooperation with one spreadsheet very error prone. Your co-worker might not be familiar with array 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.