What is an array formula?

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.

Let’s look at an example.

Here we have some historical stock price data for Microsoft.

If we give the Close prices to theMAX 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.

Now, you’re able to see that this array formula returns a single result.

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.