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.

Forcing a Function to Spill in Excel

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”.