Some older Excel functions don't spill when you give them a range as input. This article explains why this happens, which functions are affected, and how to fix it.
Table of Contents
- Dynamic Arrays and Spilling
- The spilling problem explained
- The simple solution
- List of affected functions
- Why the plus sign?
There are other reasons why a formula might not spill. This article focuses on one specific case: 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 cell 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. See below for a larger list of affected functions.
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
Note: In simple situations like those described above, diagnosing the spill problem and fixing it is fairly straightforward as long as you know about this issue and how to fix it. Where it gets more complicated is when you use a function that resists spilling with a range inside a larger formula. Then, function will return a #VALUE! error that blows up the entire formula, but it will not be so easy to understand what the problem is, since you aren't actually seeing a function fail to spill on a worksheet.
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 |
Credit: I put together this list using this page on bettersolutions.com.
I quickly tested all of the functions in the list above. Most of them have the spill problem, but there are a few exceptions, which I've marked with an asterisk (*) above. Note also that there are a number of functions derived from the ATP functions above that have the spill problem, including WORKDAY.INTL, NETWORKDAYS.INTL, ERF.PRECISE, and ERFC.PRECISE.
When testing these functions, the basic approach is to provide a range in place of any argument that would normally be provided as a single cell reference. For the above functions, the result is usually a #VALUE! error, with exceptions as noted. When you add the plus (+) sign before the range, the formula should lift and spill, and you should get more than one result, depending on the size of the range provided.
Why the plus sign?
Way back in the 1980s, Lotus 1-2-3 was the dominate spreadsheet application, and it allowed you to start a formula with a plus sign (+) instead of an equal sign (=). When Excel added Lotus compatibility in the 1990s, it accepted that syntax, so users migrating to Excel often typed +A1 or +SUM(B2:B10), which Excel quietly converted to =+A1 and =+SUM(B2:B10). In Excel, the extra "+" is ignored; it's just an identity operator. In Excel, the + operator has no effect on a single value, but it will coerce a range into an array, 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. 🙂 I also think it is a matter of convenience for people who use a 10-key (the numeric keypad on a computer keyboard). I ran into many variations of this comment: When I'm typing on the 10 key, I don't have to move my hand to start a formula with = .. I just hit + with my pinky and start typing. Excel puts the = in on it's own. We don't actually type "=+".
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.
