Forcing a Function to Spill in Excel

Dynamic arrays can spill. The means a single formula populates a range. Most Excel functions can refer to and use a spill range. This causes the formulas to spill to match the spill range. There are some functions that don’t work with spill ranges. Here is a technique that forces a function to work with a spill range.

In the image below you can see that the N function does not spill down to match the spill range. The formula in cell A6 creates a spill range that’s spills down the column.

The N function converts text to zero and leaves numbers unchanged. I have covered it in the blog posts below.

By referencing A6# the N function should spill down the column to match the spill range, but it doesn’t.

To force it to spill down to match the spill range you can use the BYROW function.

The BYROW function splits up a vertical spill range into separate rows to allow you to process them. Each row is passed to a variable in a LAMBDA function (I have used x as the variable) to enable a calculation.

The required formula is.


There is also a BYCOL function if you need to work with a horizontal spill range. It splits a horizontal spill range into separate columns.

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.

2 thoughts on “Forcing a Function to Spill in Excel

  1. So if I got it right, the purpose of BYROW with LAMBDA is to make non-spillable functions repeat themselves so they effectively DO spill.

    That’s useful – thanks

    • Hi Bob

      There is an easier way I have found out just use the + sign in front of the Spill range reference eg =N(+A6#) and it will spill. Apparently this forces it it to be recognised as a spill range.

      I will do an update soon.