Table of Contents
- Dynamic Arrays and Spilling
- The spilling problem explained
- The simple solution
- List of affected functions
- Why the plus sign?
Note: There are other reasons that a formula might not spill. This article covers the case of a specific group of older functions that will not accept a range in place of a single value.
Dynamic Arrays and Spilling
In 2019, the Excel team began to introduce dynamic array formulas, a new way to work with data in Excel. In a nutshell, dynamic array formulas can process more than one value at the same time, then return multiple values to the worksheet in a behavior known as "spilling". Many newer dynamic array functions spill multiple values natively. Good examples are functions like FILTER, SORT, SEQUENCE, and UNIQUE. However, older Excel functions will also spill through a process called "lifting". When you give a range or array to a function not programmed to accept arrays natively, Excel will "lift" the function and call it multiple times, once for each value in the array. For example, the LEN function is designed to return the number of characters in a text string. If we give LEN the text "apple", it will return 5:
=LEN("apple") // returns 5
If we give LEN three text strings in an array, it returns an array with three counts, one for each text string:
=LEN({"apple";"banana";"pear"}) // returns {5;6;4}
You can see an example of this in the following worksheet, where the formula in cell D8 looks like this:
=LEN(B8:B10) // returns {5;6;4}
The main thing to understand is that lifting is a built-in behavior that happens automatically. When you give a function more than one value as an input, you will get back multiple results that spill onto the worksheet. It just works. Except when it doesn't 🙃
The spilling problem explained
As it turns out, some functions don't spill when you give them a range instead of a single cell reference. A good example is the EOMONTH function, which returns the last day of the month for any given date. If we give EOMONTH the date 23-Apr-2025, with an offset of 0, it will return the last day of that month, which is 30-Apr-2025. You can see this in the following worksheet, where the formula in cell D5 looks like this:
=EOMONTH(B5,0) // returns 30-Apr-2025
However, if we give EOMONTH a range of three dates, it refuses to spill and instead returns a #VALUE! error with a formula like this in cell D8:
=EOMONTH(B8:B10,0) // returns #VALUE!
We can see exactly the same problem with the EDATE function, which returns the date a given number of months before or after a given date. If we give EDATE the date 23-Apr-2025, with an offset of 1, it will return the date 23-May-2025. You can see this in the following worksheet, where the formula in cell D5 looks like this:
=EDATE(B5,1) // returns 23-May-2025
However, if we give EDATE a range of three dates, it refuses to spill and instead returns a #VALUE! error with a formula like this in cell D8:
=EDATE(B8:B10,1) // #VALUE! error
What's going on here? The problem is that some older functions like EOMONTH "resist" spilling when provided a range. This limitation comes from these functions expecting a single value instead of a range. The #VALUE! error is essentially reporting the range as an unexpected value. Other common functions that have this same limitation include EDATE, ISEVEN, ISODD, YEARFRAC, WORKDAY, and WORKDAY.INTL.
The simple solution
Although this seems like a difficult technical problem, the solution is actually quite simple. The trick is to add a plus sign (+) before the range. This forces Excel to evaluate the range before the function runs. The result is an array of values, which are then passed to the function so that lifting and spilling occur as expected. You can see this fix in the following worksheet, where the formula in cell D8 looks like this:
=EOMONTH(+B8:B10,0) // spills normally
We can do exactly the same thing with the EDATE function, and it will spill normally:
=EDATE(+B8:B10,1) // spills normally
List of affected functions
The origins of this problem are murky, and it is difficult to find good documentation for Excel functions from the era in which most of these functions were introduced. However, based on conversations with other Excel MVPs, I believe that functions with this spilling limitation all originate from the same development effort: the Analysis ToolPak, which was initially included in Excel 97 as a separate add-in, and later rolled into the core application in Excel 2007. Here is a list of the functions that started life in the Analysis ToolPak:
| Category | Functions |
|---|---|
| Financial | ACCRINT, ACCRINTM, AMORDEGRC, AMORLINC, COUPDAYBS, COUPDAYS, COUPDAYSNC, COUPNCD, COUPNUM, COUPPCD, CUMIPMT, CUMPRINC, DISC, DOLLARDE, DOLLARFR, DURATION, EFFECT, FVSCHEDULE, INTRATE, MDURATION, NOMINAL, ODDFPRICE, ODDFYIELD, ODDLPRICE, ODDLYIELD, PMT, PRICE, PRICEDISC, PRICEMAT, RECEIVED, TBILLEQ, TBILLPRICE, TBILLYIELD, XIRR, XNPV, YIELD, YIELDDISC, YIELDMAT |
| Engineering | BESSELI, BESSELJ, BESSELK, BESSELY, BIN2DEC, BIN2HEX, BIN2OCT, COMPLEX, CONVERT, DEC2BIN, DEC2HEX, DEC2OCT, DELTA, ERF, ERFC, GESTEP, HEX2BIN, HEX2DEC, HEX2OCT, IMABS, IMAGINARY, IMARGUMENT, IMCONJUGATE, IMCOS, IMDIV, IMEXP, IMLN, IMLOG10, IMLOG2, IMPOWER, IMPRODUCT, IMREAL, IMSIN, IMSQRT, IMSUB, IMSUM, OCT2BIN, OCT2DEC, OCT2HEX |
| Math & Trig | FACTDOUBLE, GCD, LCM, MROUND, MULTINOMIAL, QUOTIENT, RANDBETWEEN, SERIESSUM, SQRTPI |
| Date & Time | EDATE, EOMONTH, NETWORKDAYS, WEEKNUM, WORKDAY, YEARFRAC |
| Information | ISEVEN, ISODD |
| Text | ASC |
All of the functions listed above exhibit this spilling problem, along with WORKDAY.INTL and NETWORKDAYS.INTL. I have not tested all of the functions in the list. Let me know if you find exceptions or other functions outside of this list that have the problem.
Credit: I put together this list from this page on bettersolutions.com.
Why the plus sign?
Way back in the 1980s, Lotus 1-2-3 formulas began with a +. When Excel added Lotus compatibility in the 1990s, it accepted that syntax, so users migrating to Excel often typed =+A1 or =+SUM(B2:B10). The extra "+" is ignored - it's just an identity operator. In Excel, the + operator normally does nothing to a single value but also coerces ranges to arrays, which sidesteps the legacy scalar (i.e., single value) restriction that creates the spilling problem described in this article.
Researching this was interesting. It helped me understand why you occasionally see the + operator turn up in all kinds of simple formulas. I think the reason is that the people who wrote these formulas learned on Lotus 1-2-3. 🙂
Summary
Excel's dynamic array formulas were introduced in Excel 365 starting in 2019. A key feature of dynamic arrays is the ability to process multiple values and return multiple results through "spilling." While newer functions like FILTER and SORT spill natively, older functions use a process called "lifting," where Excel automatically calls the function multiple times for each value in an array.
However, certain older functions - primarily those originating from Excel's Analysis ToolPak - resist spilling when given a range as input. Functions like EOMONTH, EDATE, ISEVEN, ISODD, YEARFRAC, WORKDAY, and WORKDAY.INTL will return a #VALUE! error instead of spilling results because they expect single values rather than ranges.
The solution is simple: add a plus sign (+) before the range reference. For example, =EOMONTH(+A1:A5,1) works where =EOMONTH(A1:A5,1) fails. The + operator forces Excel to evaluate the range first, converting it to an array of values that can then be processed through normal lifting and spilling behavior.
This limitation affects dozens of functions across financial, engineering, math, date/time, and information categories - essentially all functions that were originally part of the Analysis ToolPak add-in.
