Office 365 Still Requires Ctrl+Shift+Enter to Spill ! Why?

[fairfun]

Hi,
I wrote a formula on the attached sheet to extract every 3rd row from the list, however, when I enter the formula with Enter, it gives me value error, however, when I enter the formula with Ctrl+Shift+Enter; data spills correctly.

I thought there was no need for Ctrl Shift Enter on office 365. Anyone can explain the reason why still I need CSE?



Thanks,

[Keebellah]

Hi, welcome to the forum.
I looked at your file but all the formulas are Array formulas and they NEED TO BE VERIFIED (COMPLETED) with CTRL SHIFT ENTER

[fairfun]

Hi Keebellah,
My understanding from the Microsoft’s guideline is that, there is no need for Office 365 users to enter array formulas with Ctrl Shift Enter. Sorry I cannot attach a website link as I am a new member. Microsoft says,

"Beginning with the September 2018 update for Microsoft 365, any formula that can return multiple results will automatically spill them either down, or across into neighboring cells. This change in behavior is also accompanied by several new dynamic array functions. Dynamic array formulas, whether they’re using existing functions or the dynamic array functions, only need to be input into a single cell, then confirmed by pressing Enter. Earlier, legacy array formulas require first selecting the entire output range, then confirming the formula with Ctrl+Shift+Enter. They’re commonly referred to as CSE formulas."

This is confirmed by many reputable excel platforms, for instance an exceljet article says;

“When any formula returns multiple results, these results will spill into multiple cells on the worksheet. This includes older functions not originally designed to work with dynamic arrays.”
“In Dynamic Excel, there is no need to enter array formulas with control + shift + enter. When a formula is created, Excel checks if the formula might return multiple values. If so, it will automatically be saved as a dynamic array formula, but you will not see curly braces.”

Or, an article on excelcampus.com says;

“Goodbye Ctrl+Shift+Enter
The goal of this new functionality is to eventually replace array formulas that we input with Ctrl+Shift+Enter (CSE). Don't worry, that will be a long goodbye.”

“Existing Functions Can Spill
You might be wondering about other existing array functions in Excel. Well, those functions will spill too.”

So, my question still stands, why I still need to use Ctrl+Shif+Enter for the attached example despite the fact that I am on Office 365?
Thanks

[Keebellah]

Thanks for the update, I'm no 365 user and this new to me.
Can't help you then but, I learned something new form you, thank you for the explanation

[AliGW]

Read this again, particularly the last line:

“Goodbye Ctrl+Shift+Enter
The goal of this new functionality is to eventually replace array formulas that we input with Ctrl+Shift+Enter (CSE). Don't worry, that will be a long goodbye.”

The clue is in the words “eventually” and “long goodbye”. We are in a phased period at the moment whilst the new dynamic arrays are being gradually rolled out to users. That’s why some old array formulae will work without CSE and some won’t.

[fairfun]

Many thanks for the reply Ali,
What about Microsoft clearly saying, "Dynamic array formulas, whether they’re using existing functions or the dynamic array functions, only need to be input into a single cell, then confirmed by pressing Enter"

Even what you are suggesting is valid, both offset function and index functions can spill already on my workbooks. So there is no function used on the example that cannot spill "yet". So your answer still needs to clarify why formula did not spill on this specific example.

Regards,

[AliGW]

I cannot answer your question fully - you need to be asking Microsoft directly. Have you done that? All I can tell you, from my own observations (and I'm on the insider programme, so completely up-to-date), is that at the moment, sometimes you need CSE, sometimes not. You just have to take each case as it comes.

If you want clarification, ask Microsoft, not me.

[Keebellah]

Agree for 200%
Hope you find the answer(s)