Patch Lady – what is Excel spill?

Topic

New Reply

So today on two different spreadsheets at the office I experienced “Spill” It’s hard to describe Spill. I’m sure to someone, somewhere this works.  Bu
[See the full post at: Patch Lady – what is Excel spill?]

Susan Bradley Patch Lady/Prudent patcher

4 users thanked author for this post.

GreatAndPowerfulTech, lurks about, woody, Kirsty

Reply | Quote

Replies

Hi Susan,

From what I’m reading, this Spill feature has been around a few years, though as you say maybe it’s just rolling into Enterprise versions.

I guess my question is this: What’s changed lately that caused you/others to experience a new problem with it?

Did you at one point code formulae that generate multiple results, then more recently something got broken about it? Or did behavior of existing coded formulae change? Or are there now gotchas in the UI that trigger the new behavior that you clicked on for other reasons before?

I’m just trying to discern what to watch out for.

FWIW, I have a few spreadsheets I have developed for accounting – relatively simple things for sure – and haven’t seen any recent problems. I used them in earnest just a few days ago at the beginning of the month. My copy of Excel DOES show that it has received the Dynamic Arrays update (when queried via the What’s New icon).

-Noel

Reply | Quote

As a user of Office 365, I started getting the #SPILL error message on spreadsheets I had been using for years. I also saw the ‘Single’ and @ functions in old formulas where I was using the format A:A * B:B, or something similar. (This formerly gave the product of the cell in the row in column A and cell in the row in column B. A1*B1, A2*B2, …)

Noel, you are right in suspecting the Dynamic Array update. Excel has been updated to be pickier on formulas that are array formulas and not array formulas.  The old formula format A:A*B:B is an implicit array formula, which is no longer allowed. Using that format produces a #SPILL error. I presume there are other formerly allowed implicit array formulas that now give the same error.

In old spreadsheets, the formula format usually gets automatically replaced by @A:A * @B:B. Sometimes the ‘@” is the ‘Single’ function in old formulas. But when ‘Single’ is manually entered, it is changed to the ‘@’.

This drove me nuts for a week, while I researched the issue.

Hope this sends you in the right direction.

-Dave

Reply | Quote

https://exceljet.net/formula/how-to-fix-the-spill-error

1 user thanked author for this post.

hansR

Reply | Quote