Introduction of an Array object to rework the way in which multi-cell array formulas are used.
Tables and pivot tables have revolutionised the way in which data analysis is performed. Change of similar magnitude is needed for building computation-heavy models (financial, engineering etc.)
The array object should be nameable (default Array1 etc.), it should be visible on the worksheet, it should be possible to link it to index rows just as a form control may be linked to a cell.
Any formula inserted into an array object should be treated as an array formula and will automatically be extended to the boundaries of the array object. Any array reference within the formula will automatically take data from the corresponding cells in all referenced arrays.
The array object should have a resize handle. Changing the size of an index row (or the array object itself) will automatically change the size of all array objects that are linked to it (subject to checks on overwriting other occupied cells).
Example of use:
1. Write formula = price * volume
2. Convert the cell to an array object.
3. Rename the array to 'revenue'
4. Link it to the time-line as an index so that it becomes a row range, visible on the sheet to ensure the object matches the intended data range.
(5. If needed link to 'product' stream as an index so that the revenue array becomes 2-dimensional)
Note: For the type of models I develop this would remove the need for direct cell referencing and largely remove the need for relative referencing, both of which are the source of errors meaning few workbooks (20%?) are error-free.
Peter Bartholomew commented
If you wish to see an independent assessment of some models build using Names and Array formulas only go to
Despite the simplicity of the approach, changing the array dimensions to accommodate further time periods (for example) is still a pain and the arrays have to be adjusted one by one. The insertion of additional rows through an array formula is trapped as an error despite there being no ambiguity inherent in the process.
If one could simply use a resize handle (as is done for Tables), all the tables linked to a given index could be automatically resized, so removing a significant source of error.
This will never be implemented by popular demand. It is up to Microsoft to show leadership as they did for the implementation of Tables in Excel 2007 or the more recent Get and Transform that is aimed at supporting data analysts, as opposed to modellers.