What is an array formula?

In this video, we'll answer the question: What is an array formula? 

In the world of Excel formulas, the term "array formula" is probably responsible for more confusion than just about any other concept.

This is because the definition of an array formula has become mixed up with the requirement to enter some array formulas in a special way, with control + shift + enter.

Thankfully, in the dynamic array version of Excel, we can leave this confusion behind forever, since there is no need to handle array formulas differently from other formulas.

So, what is an array formula?

An array formula is a formula that works with an array of values, rather than a single value. 

What distinguishes an array formula is some kind of array operation that touches or manipulates the values. This can be a math operation, concatenation, a comparison, or even feeding an array into another function.

There are two kinds of array formulas: those that return a single result and those that return multiple results.

Let's look at an example. Here we have some historical stock price data for Microsoft.

If we give the Close prices to the MAX function, we get the highest close price. 

This is not an array formula because there is no array operation. MAX simply returns the largest value.

However, if we calculate the max gain, by subtracting open prices from close prices, this becomes an array formula, because we are performing an array operation inside the MAX function.

If I inspect this operation with F9, you can see we end up with a new array, and MAX again returns the largest value.

Now, you can see that this array formula returns a single result.

However, if I remove the MAX function, the formula returns multiple results that spill onto the worksheet, where each value is the gain or loss for a given date.

This is now an array formula that returns multiple results.

This simple example also shows off one of the best features in Dynamic Excel.

When a formula returns multiple results, you'll see these results spill onto the worksheet. This behavior is intuitive, and makes it much easier to understand what a formula is actually doing.

In previous versions of Excel, the same formula will display one value in a single cell.