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

What is an Array Formula - Without the Array Formula :: Excel Glossary :: 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:

What is an Array Formula - Multi Cell Formula :: Excel Glossary :: PerfectXL

Then we enter the array formula in selected cell D2.

What is an Array Formula - Multi Cell Formula :: Excel Glossary :: 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. 

What is an Array Formula - Multi Cell Formula :: Excel Glossary :: PerfectXL

As you can see, the array 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

This 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:

What is an Array Formula - Single Cell Formula :: Excel Glossary :: 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 array formula ‘=SUM(B2:B9*C2:C9)’.

What is an Array Formula - Single Cell Formula :: Excel Glossary :: 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.

What is an Array Formula - Single Cell Formula :: Excel Glossary :: 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 array formulas and marks them as a potential risk.

Read our guideline about the use of array functions

Try PerfectXL Now

 

About PerfectXL

PerfectXL provides full automatic clarity about all risks, mistakes and other problems in your spreadsheets, plus an extensive improvement plan. You don’t have to be an expert yourself and you don’t need much time to fix your file.

Learn about the features

 

Array Functions are Past Times

Long ago, Excel was not as clever and advanced as today, but the so-called array functions have been around for a long time. However, people do not know them, and do not understand them well. 
About array functions

Subscribe to our Excel Guidelines