Hi - Dave here.
Ever wonder why some Excel functions stubbornly refuse to spill when you give them a range of data? This quirky behavior affects dozens of Excel functions.
You can see an example below, where the formula in cell D8 is:
=EOMONTH(B8:B10,0)
This seems like a simple formula. What's the problem?
[
Download the workbook and read the full explanation]
It turns out that some older functions don't spill when you give them a range instead of a single cell reference. The good news? There's an incredibly simple fix that most Excel users don't know about: just add a plus sign (+) before your range reference. That's it!
I used this hack in last week's
formula example. Click the link above to read the full explanation and find out which functions are affected. Then download the worksheet and try it out yourself.
Note: you will need Excel 2021 or newer to see spilling in action.
Excel formulas
We maintain a list of over 1000 working formulas
here.
If you need more structure, we also offer
video training.
Have a great weekend!
Dave
The Exceljet newsletter is free and sent weekly on Fridays. Each week, I take a detailed look at how to solve a specific problem in Excel. You can sign up on our home page.