Convert Dynamic Arrays into Tables
The new Dynamic Arrays make possible to automatically fill down formulas. Tables can also fill down formulas. Microsoft Excel Team could make both features be compatible so we could convert Dynamic Arrays into Tables making them expand and shrink according to the main formula.
Harlan Grove commented
Thing is dynamic arrays can also spill into a potentially variable number of columns as well as rows. There's no obvious or perhaps even sensible way to handling column indexing which doesn't require numeric column indexing. Why should rows get special treatment?
To some extent, what's really needed is a sensible SEQUENCE function like the one in Google Sheets. It'd also be handy to have back-to-front indexing like in Icon and SNOBOL languages so that index -1 referred to the LAST item in an array, -2 to the 2nd to last item, etc.
Kevin Osborn commented
I think there may be an issue with the phrasing of this request. I just got access to dynamic array functions and the problem is this.
Before dynamic arrays we all created formulas to populate a column within a table with a list of data (many times unique) and lets call this ColA within Table1. Then we would create other columns via formulas based on [ColA] so [ColC] = [@ColA] * [@ColB] for a simple example. Structured reference made this awesome when tables have many columns. Then elsewhere in the spreadsheet we could use structured reference to look up values from Table1 (e.g. =SUMIFS(Table1[ColC],Table1[ColD]&"<>") ).
Now with array formulas we can create a unique list A2 = SORT(UNIQUE(Table2[ColX])) and it will spill down column A. Then other columns (e.g. column B) we can create a formula B2 = IF(A2# = "Red",1,0) and this too will spill. So we have kind of created a 2 dimensional structure (i.e. a table) but we have no ability inside nor outside this "array formula created 2 dimensional structure" to use structure reference (i.e. column name) so we have to go back to remembering columns by column letter and we lose autocomplete that goes along with structured reference.
So kudos for array formulas but what it looks like I will be doing until this is addressed is creating a helper column outside my table with the key information (i.e. the SORT(UNIQUE(***)) ) and then inside ColA of Table1 is the formula = Z2, = Z3, =Z4 etc.
Give this some serious thought Microsoft. I realize you'll need to limit a table to 1 dynamic array formula per table or the dynamic arrays could conflict one another in the spill range. Without this you have seriously limited the usefulness of dynamic arrays.
RONALD DINOSO commented
I like this idea! Earlier I submitted an idea similar to this where Dynamic Arrays inherit the same number/text formatting as the source column. I found your suggestion because of how Tables incorporate formatting of rows above the new records along with automatically propagating formulas to the next row(s).
it'll be great!!,,, like and alternative for Query but with functions
Table structured references are a more well-defined way to model information.
It is very natural that dynamic arrays should be able to be used in tables and expand and contract the number of rows.
Tables are also an invaluable asset to work with automation with Microsoft Flow, it should be possible to combine dynamic arrays with them and leverage the result in Microsoft Flow automation.
Umang Kejriwal commented
Table has an option of total row which would be very useful for filtered data. since number of rows of a filtered data is not constant, the total row can automatically move up and down which is very useful.
Ed Hansberry commented
The biggest shortcoming to the new Dynamic Array functions is the inability to use them in a table.
Eder Rodrigues de Souza commented
It will help a lot if this happens.
Cristiano Galvão commented
Hi Guido. Yes, dynamic arrays are awesome. I published a video about them too: https://youtu.be/J7XW9CPq1rU
HackSlash, I mean the opposite: dynamic arrays to table.
I want to be able to use dynamic arrays with tables, not convert them to tables.
I find this a great idea. I've seen nice vids/demos of MVP on dynamic array functions, but I was also a bit disappointed when they announced the incompatibility with tables.
Somehow it seems natural, since tables can resize, that they would resize based on the array spill result.
Do you mean convert tables to Dynamic Arrays? It looks like Dynamic Arrays can do everything that tables can do plus the source data can expand just like the table itself.