Office Insiders: SPILL behavior and "dynamic" arrays in the 2019 release. :eek:

These links describe a new feature coming in the 2019 release of Excel.

Dynamic arrays are discussed here. https://support.office.com/en-us/art...4-df571bd4f1b4


Please note:

Any new formulas that return more than 1 result will automatically spill. There's no need to Press Ctrl+Shift+Enter.

and this

Can be easily modified by changing the source cell, whereas CSE array formulas require that the entire range be edited simultaneously.

That apparently reflects MicroSoft's concept of how legacy array formulas are supposed to be committed ... whole range CSE.

Then here Dynamic arrays and spilled array behavior

is this quote:

Only the first cell in the spill area is editable. If you select another cell in the spill area, the formula will be visible in the formula bar, but the text is "ghosted", and can't be changed. If you need to update the formula, you should select the top-left cell in the array range, change it as needed, then Excel will automatically update the rest of the spill area for you when you press Enter.

Perhaps I am reading too much into all this, but it sounds like some of the most undesirable characteristics of whole range commitment CSE will become automatic (and not optional) with this "spilling" behavior.

Can any Office Insiders enlighten me?

I'm not sure this will be as bad as you think. The majority (?) of CSE formulae will return an error if not confirmed that way. Perhaps I'm reading too little into this?

It will only affect those with the newest versions of the software, so people still running legacy versions will still need to know how to use CSE.

Thank you Ali.

It will only affect those with the newest versions of the software, so people still running legacy versions will still need to know how to use CSE.

Yes my concern is that I have 365 and as I understand it will be upgraded automatically to 2019 version sometime in the near future.

The majority (?) of CSE formulae will return an error if not confirmed that way.

I guess I didn't clarify something well. By committing the whole range CSE I mean selecting the whole target range and then from edit mode committing CSE all at once. Reading MS instructions on committing CSE have always(?) included those instructions.

I find several reasons not to do it that way, and from what I gather I am not alone. My concern is that with the MS concept of how array formulas are committed they may not see the downsides ... that they think this is normal behavior.

Again maybe I am reading too much into this. I hope so.

Only the first cell in the spill area is editable. If you select another cell in the spill area, the formula will be visible in the formula bar, but the text is "ghosted", and can't be changed. If you need to update the formula, you should select the top-left cell in the array range, change it as needed, then Excel will automatically update the rest of the spill area for you when you press Enter.

Any way thank you for looking at this and commenting.

Yes my concern is that I have 365 and as I understand it will be upgraded automatically to 2019 version sometime in the near future.

Your 365 subscription will never morph into the 2019 standalone version! Certainly it will have the newest features and functions.

As for the downside, I am afraid I am not following you. Sorry!

Again Ali thank you.

I was hoping there might be someone Office Insider who had experience with these new spilling behaviors.

(I also think I needed to calm down before starting this thread. LOL )

LOL!!! It'll all be OK, Dave.

Mynda Treacy talked about it in one of her newsletters last year. Not sure if this is available to all, but I'll give it a go: https://www.myonlinetraininghub.com/...dynamic-arrays

You'll get a #SPILL! error if any data is going to be overwritten.

Thank you for the link Ali.

And yes she mentions

Previously we would have to select cells A1:A4 prior to entering the ROW formula and then press CTRL+SHIFT+ENTER to return the array. I think you’ll agree it’s now far simpler.

I am rather surprised that this is such common practice. What I am accustomed to is committing the array formula in one cell and then filling down as with any other formula. You can edit, delete, and insert rows at will.

Since as Mynda confirms:

Also notice in the formula bar above that there aren’t any curly braces around the formula to indicate it’s an array formula. And when I move my cell selection away from A1 you can see the formula bar now contains a ghosted formula, indicating I can’t modify it.

I am concerned that those of us fond of array formulas (and the fill down method above) are about to be "herded" into a box we are not going to like. There are a lot of us.

Perhaps I'll just have to wait and see. Or again maybe I'm reading too much into this.

Originally Posted by Norie

You'll get a #SPILL! error if any data is going to be overwritten.

Yes and thank you. I read that, too. It sounds like a promising feature.

There is a big difference between an array formula that returns one result, which is then copied down multiple cells, and one formula array entered into multiple cells to return multiple results.

I am concerned that those of us fond of array formulas (and the fill down method above) are about to be "herded" into a box we are not going to like. There are a lot of us.

That's often the price of progress, and not liking something is usually a symptom of having to get used to working a bit differently - you get there eventually and sometimes end up liking it. I tend to go with the flow.

By the way, I use the copy down method, too, but I have no problem with this concept: it seems similar to me to the current TRANSPOSE formula.

I agree with FlameRetired. If true, this will not be something I will consider an improvement; a restriction, more like.

I too have always preferred to commit an array formula to a single cell and then copy down. As well as the practical benefits mentioned by FlameRetired in post #8, I can also think of at least one other significant difference between the two practices: take the following construction for returning entries which match certain criteria (perhaps one of the most common requests on this forum):

=INDEX(B:B,SMALL(IF(A$1:A$10="X",ROW(B$1:B$10)),ROWS($1:1)))

works fine as a single-cell array formula and copied down, though not as the equivalent multi-cell version, since (at least as of now) the ROWS construction is not processed as desired.

This will work as a multi-cell version:

=INDEX(B:B,SMALL(IF(A$1:A$10="X",ROW(B$1:B$10)),ROW()))

though unqualified ROW() constructions are hardly rigorous (to put it mildly), even less so if we have to offset them with hard-coded constants, e.g. ROW()-7, etc. to account for the row in which the formula lies. (https://excelxor.com/2014/08/25/row-...er-generation/)

Regards

Neither of those formulas will spill since they only return one value. The spilling only occurs for a formula that returns multiple results - eg if you changed that last one to:

=INDEX(B:B,SMALL(IF(A$1:A$10="X",ROW(B$1:B$10)),ROW(A1:A10)))

it would spill to 10 rows.

Ah, ok, thanks. I'd misunderstood.

Cheers

Originally Posted by rorya

Neither of those formulas will spill since they only return one value. The spilling only occurs for a formula that returns multiple results - eg if you changed that last one to:

=INDEX(B:B,SMALL(IF(A$1:A$10="X",ROW(B$1:B$10)),ROW(A1:A10)))

it would spill to 10 rows.

Thank you Rory for that. It's beginning to sound like I have read too much into the description of this feature.