#### Want to become great at Excel?

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

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

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