Spill function

Question

HI all

In newer versions of Excel, the 'spill' function arrived which I find really annoying but I believe there is no way to switch this function off (or am I wrong?)

I have however edited formulas in the past and included the @ sign somewhere to cancel out the function but can't remember where it goes?  Can anybody help??

Thank you.

Louise

Answer 1

This feature is called "Array Evaluation" or "AE" as opposed to the classic "Implicit Intersection Evaluation" or "IIE". AE was introduced when Dynamic Arrays were added about a year ago, Here is a description from the perspective of VBA:

    https://docs.microsoft.com/en-us/office/vba/excel/concepts/cells-and-ranges/range-formula-vs-formula2

If you put an AT sign (@) in front of the entire formula but after the equal sign, it disables AE and applies IIE to the entire formula. Putting the AT sign in front of a specific range reference tells Excel to use IIE for only that range reference.

Kevin

Answer 2

Hi.  If you put this in C3, it will return the value in A3:

It actually is a great feature... in my opinion.  

=@A1:A5

Answer 3

Hi and thank you for your response.  This helps and has worked, thanks again.  Louise