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:

We’ve been working on a new course, Dynamic Array formulas, and these videos help explain the topics discussed below:

What is an array formula? 3 basic array formula examples

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: This is an array formula that returns a single result. Working from the inside out, we first subtract the low temps from high temps: Each range contains 7 values, which we can expand into arrays like this: 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: The new array is returned directly to the MAX function which returns the largest value: 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: 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) forgets 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 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.

Videos

What is an array formula? 3 basic array formulas

Dave Bruns

Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.

title: “What Is An Array Formula " ShowToc: true date: “2022-12-09” author: “Steven Lopez”

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.

Dave Bruns

Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.