Tomaz Vajngerl
Dynamic arrays are a new type of arrays, that can update when the source data changes. An example of such an array is UNIQUE, which produces a different sized array depending what the input data and if you change the data, the array size can change as well.
For example:
If we have values [1, 4, 6], we can write a formula in a cell =UNIQUE(C6:C8), which will result in only unique values – [1,4,6] (all are unique). If we change a value 6 to 4, the array will dynamically change to [1,4] (just the unique values).
To make the dynamic array behavior possible, the dynamic array will produce a #SPILL! error, if the output cells already have a value set (output is blocked).
For example:
We continue from the previous example and have an array [1,4] (result from the =UNIQUE(C6:C8) formula). If we write a string (“blocker”) into the next cell from the output, and now change the value 4 to 2, we will get an #SPILL! error. This happens because the result from the dynamic array formula =UNIQUE(C6:C8) changed from 2 values [1,4] to 3 values [1,4,2], but because the next cell is blocked, we can’t write the output to the cells, so we result in an error instead. Once the blocker is removed (deleted), the dynamic array will automatically write the output to the cells.
Until now, the dynamic arrays formulas were implemented, however not the dynamic part of the dynamic array formulas, which means the output didn’t change and stayed the same when we changed the input values.
With this we also changed how other array formulas behave and made that the same as dynamic array formulas. There is now no need to hit Ctrl+Shift+Enter when inserting an array formula, but it will produce multiple output values when by default (and write an #SPILL! error when that is not possible). If there is a need to only output one value from the array (the case when the array is entered without using Ctrl+Shift+Enter), it is possible to do so with the newly introduced @ operator (implicit intersection operator). For example =@(A1:A5)
Operator # (spill operator) was also added. This makes it easier to refer to the dynamic array, by just referencing the first output cell of the dynamic array formula. This is done by writing the dynamic array’s first (origin) cell and suffix that with # character (i.e. C5#).
For example:
From the previous example, we can refer to the output of the =UNIQUE(C6:C8) formula instead of D6:D8 just as D6#, and use that with another dynamic formula (in this case SORT). The advantage of this is that when the output changes dynamically, we don’t need to change the formula to take the new output range into account (C6:C8), but the output will be dynamically adjust as well.
