I posted recently about a technique to force a function to spill if it didn’t spill automatically. I have since learned of a much easier way.
Thanks to Wyn Hopkins MVP who shared this tip with me.
Below is the problem.
The range A7:A10 is a spill range – it is linked to the range above it.
The formula in cell B7 =N(A7#) should spill down to match the spill range. As you can see it doesn’t.
In a previous blog post I used BYCOL and LAMBDA to force a spill.
A better solution is to simply use the plus sign in front of A7# =N(+A7#) and it works.
Apparently, the plus sign forces Excel to treat it as a spill range.
Thanks again to Wyn Hopkins for sharing this “hack”.