How to stop auto complete AND spill! error of formulas in following rows/columns?

[Cris11Ask]

I'm trying to type a formula just in one cell and when I hit enter it automatically fills the rows next to them and I don't know how to stop that. If I try to delete or put another formula on the next column I want to use, I get the error Spill!.

I've tried:

- Advanced>Editing> unchecking: Enable AutoComplete for cell values

- Advanced> Editing> unchecking:  Extend data range format and formulas

- Advanced> Formulas> unchecking: enable multithreated calculation

- Advanced> Trust Center> Disable all applications Add-ins (may impair functionality)

- Options> Formulas> Workbook calculations> manual instead of automatic

- Options> Formulas> unchecking: Formula autocomplete

- Options> Proofing> autocorrect options>Fill formulas in tables to create calculated columns

Please help as I'm not sure what else to try

[#2]

@Cris11Ask 

It's not a bug, it's a feature:

https://support.office.com/en-us/article/dynamic-array-formulas-and-spilled-array-behavior-205c6b06-...

https://support.office.com/en-us/article/Dynamic-array-formulas-vs-legacy-CSE-array-formulas-ca421f1...

https://support.office.com/en-us/article/Guidelines-and-examples-of-array-formulas-7D94A64E-3FF3-468...

[#3]

@Cris11Ask In addition to @Detlef Lewin 's comment, I took the liberty to correct two small errors in your sheet. The named ranges for both "Seasonal_adjustments" and "Ad_expenditures" pointed to a range from column C to G, rather than D to G. This caused the dynamic array formula in D20, to produce correct numbers but in the wrong quarters. Wrapped the formula in D20 in "ROUND(...............,0)" and all seem to work just fine.

[#4]

@Detlef Lewin 

Thank you! do you know if there's a way to turn the feature off by any chance?

[#5]

@Riny_van_Eekelen 

Thank you! it's nice to know this trick for the future!

Sadly I'll be taken off points if I add stuff to my formulas so I was hoping to deactivate the automatic feature. Any ideas suggestions would be super helpful.

[#6]

@Cris11Ask Put an @ in front of the formula (after the =). But you'll disable a great new feature.

[#7]

@Cris11Ask 

Nope, you can't switch dynamic arrays off, if only to shift on older versions which doesn't support them. It's better not to rely on default behaviour of pre-DA Excel, but say to Excel directly what exactly you'd like to calculate. Bit more efforts, but much more powerful solutions.

For example, here you may say to Excel directly which columns you'd like to calculate as

=Sales_parameter_1*INDEX(Seasonal_adjustment,1,{2,3,4,5})*SQRT(Sales_parameter_2+INDEX(Ad_expenditures,1,{2,3,4,5}))