Introduction
What is an array formula anyway?
In simple terms, an array formula is a formula that works with an array of values, rather than a single value. Array formulas can return a single result or multiple results.
That sounds simple enough, and indeed many array formulas are not complex. However, because some array formulas need to be entered in a special way, and some don't, array formulas live mostly in the geeky realm of super users.
In fact, in the world of Excel formulas, the term "array formula" may be responsible for more confusion than just about any other concept.
With the introduction of Dynamic Arrays in Excel 365, array formulas are going to become a lot more common, because they are now much easier to use and understand:
- No need for control + shift + enter
- Formulas that return multiple results will spill
Related videos
We've been working on a new course, Dynamic Array formulas, and these videos help explain the topics discussed below:
Basic array formula example
In the example below, we want to find the maximum change in temperature over seven days:
The formula in F5 is:
=MAX(C5:C11-D5:D11)
This is an array formula that returns a single result.
Working from the inside out, we first subtract the low temps from high temps:
C5:C11-D5:D11 // array operation
Each range contains 7 values, which we can expand into arrays like this:
{86;84;89;87;82;85;88}-{69;65;57;62;70;59;59}
This is called an array operation. We are working with multiple values, and the result after subtraction is a new array with 7 values, where each value represents the change in temperature on the given day:
{17;19;32;25;12;26;29} // new array
The new array is returned directly to the MAX function which returns the largest value:
=MAX({17;19;32;25;12;26;29}) // returns 32
You can see that this array formula is actually quite simple!
Traditional Excel - complication and danger
The problem arises when we enter the formula. In "Traditional Excel" (currently, every version of Excel except Office 365), this formula must be entered with control + shift + enter. When entered this way, Excel will display curly braces in the formula bar like this:
{=MAX(C5:C11-D5:D11)}
These curly braces tell you that Excel is handling the formula as an array formula. In other words, Excel is "letting you" work with multiple values.
To most users, that's pretty strange and confusing. But it gets worse.
If you (or someone else) forget to enter the formula with control + shift + enter, the same exact formula may return an incorrect result.
For example, the formula above without control + shift + enter will return 17, the change in temperature on Monday. This will be a "silent failure" – no warning will occur. The formula will simply stop working correctly.
Obviously, formulas that return incorrect results are bad news :)
Dynamic Excel - simplicity and clarity
The great thing about the Dynamic Array version of Excel, is that array formulas just work. You don't have to use control + shift + enter with any array formula.
Even better, a formula that returns multiple values will spill these values onto the worksheet. This makes array formulas much easier to understand because it's obvious when a formula is returning more than one value.
In contrast, the same formulas in previous versions of Excel will display only one result in a single cell, no matter how many values are actually returned.
The bottom line is that working with array formulas in Excel is now easier and more intuitive than ever. You can now use array formulas whenever you like, without worrying about fancy syntax requirements.
