Spill is hijacking my formulas - how to disable it?

I figured out how this works:

Old way was make a formula using a named range:

formula:                          =2 * xinput.

The new version of Excel would automatically return an array, and fill down an array (really a vector, but I think Excel calls what is actually a vector, an array).  This "fill down" is part of the "upgrade", or hijacking, however you want to look at it.

New way to get around this problem: 

make formula:             =@2*xinput.

Then select and copy the rows downwards to Fill-Down the range with copies of this formula. The "@" sign collapses a returned array down to a single value.

You will now be able to delete or modify any single row of the newly copied down region.

See this link:

https://support.office.com/en-us/article/implicit-intersection-operator-ce3be07b-0101-4450-a24e-c1c999be2b34

Best regards,

John

I generated a simple file:

Row 1 = headings "xinput" and "youtput"

Rows 2,3,4,5 of column 1:  the integers 1,2,3,4

I named  the two columns as defined names:   "xinput " and "youtput".

I made the formula:   youtput = 2 * yinput.

Excel jumps and hijacks this, with this problem:

Say I want to edit the 2nd ouput cell in the defined named_region: youtput.

I want to delete the formula and write some comment text in there.

This is a nice way to comment on defined named regions and break them up for readibility.

I can't do this: I can't delete or edit the formula in only ONE row of the defined name region without causing a spill error.

There does not appear to be any way to turn this off.

Here is the one drive link for the Excel file.

https://1drv.ms/x/s!Ak\_lgS4OczbklQmOtbfiPWtM1Q4f?e=Vro0qH

Thanks to the original poster for using the word hijack. I was at a loss to describe the phenomenon.

thanks for your time,

John

I certainly appreciate your time to reply so thank you for that. I have checked both the function help and its error pages before asking but as you also know, they don't offer any answer on this question. Neither does the linked video.

There are two fundamental issues here, actually:

1. Why is a new behavior being introduced without asking first and why is there no opt-in?
2. Even so, why there is no clear and easy way to opt-out of it? From what I'm seeing, there is no option to disable this at all.

This is some really dangerous stuff. I'm losing the ability to contemplate and reason on what I want to do and instead I am to delegate my thinking to Excel. That produces incorrect results by the way. With no way for me to fix it.

Please don't hold it against me if I don't share any sheets. Apart from being a personal thing, the formulas in them are so brain dead simple that they cannot be blamed for this (they were repeated but each with one simple change). Also, in principle, what's in a formula is conceptually not related to this hijacking behavior, so I don't want to turn the discussion that way.

Thanks again. I'll try and write up a feedback to Microsoft on this, perhaps someone will read it.

"It's an improvement".

.

Other people have been complaining about this recent "improvement".  If your formula can return more than one value it will cause this error.

.

We would have to see an example worksheet to make specific comments about your formulas. 

.

Here is the generic MS support answer to this error code

#SPILL! errors in Excelhttps://support.office.com/en-us/article/-spill-errors-in-excel-ffe0f555-b479-4a17-a6e2-ef9cc9ad4023

#SPILL errors are returned when a formula returns multiple results, and Excel cannot return the results to the grid. For more details on these error types, see the following help topics:

(part of above link) #SPILL! error - Spill range isn't blankhttps://support.office.com/en-us/article/-spill-error-spill-range-isn-t-blank-182c7141-7ff5-4dc2-ba38-7a81b7bf51c7

This error occurs when the spill range for a spilled array formula isn't blank.

.

.

#SPILL! error - Spill range isn't blankhttps://support.office.com/en-us/article/-spill-error-spill-range-isn-t-blank-182c7141-7ff5-4dc2-ba38-7a81b7bf51c7

This error occurs when the spill range for a spilled array formula isn't blank.

.

Upload Example - Trouble Shooting - Share Personal OneDrive File (not Business OneDrive)

.

Trouble shooting problems in files can be like a visit to the dentist, a long, slow painful process of us trying to “extract” the clues needed to recognize the problem in a back and forth flow of questions and answers so that we can come up with a / “the” solution.

.

Often it is faster and easier for everyone if we have a “sample file” get "hands on", to look at, and to “play with”.

.

This next link provides some tips on setting up a sample file and specific instructions for uploading and "sharing" it for us to access:

.

https://answers.microsoft.com/en-us/windows/forum/windows_other-winapps/trouble-shooting-share-onedrive-file/a231a097-bcbf-4e34-ad6c-a33118baf471

.

Includes links to macros to randomize text in Word and numbers in Excel

.

**************************************

.

The first 2 minutes of this video gives an example of What I mean by a “simple” example. Use short simple names and quantities, and just 2 or 3 rows per sample data you want calculations done on.  You want simple numbers so you can do the math checks in your head.

Col- **Stacking Columns of Data (Unpivot, Split Columns by delimiter, and everything!)****(PowerQuery)******2017 04 16

We’ve got headers and a large number of paired columns that need to be condensed down to just 2 columns. 

.

**************************************

.