News, tips, advice, support for Windows, Office, PCs & more. Tech help. No bull. We're community supported by donations from our Plus Members, and proud of it
Home icon Home icon Home icon Email icon RSS icon
  • Patch Lady – what is Excel spill?

    Posted on Susan Bradley Comment on the AskWoody Lounge

    Home Forums AskWoody blog Patch Lady – what is Excel spill?

    Viewing 3 reply threads
    • Author
      Posts
      • #2295561 Reply
        Susan Bradley
        AskWoody MVP

        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

        4 users thanked author for this post.
      • #2295600 Reply
        Noel Carboni
        AskWoody_MVP

        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

      • #2295641 Reply
        dchallis
        AskWoody Plus

        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

      • #2295706 Reply
        slatus
        AskWoody Plus

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

        1 user thanked author for this post.
    Viewing 3 reply threads

    Please follow the -Lounge Rules- no personal attacks, no swearing, and politics/religion are relegated to the Rants forum.

    Reply To: Patch Lady – what is Excel spill?

    You can use BBCodes to format your content.
    Your account can't use Advanced BBCodes, they will be stripped before saving.