Forcing a Spill with the Magic Plus Sign

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

Please note: I reserve the right to delete comments that are offensive or off-topic.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.