Currently, dynamic array formulas can only be evaluated as a single, indivisible block. When a data point changes within a large spilled table, Excel forces the entire array to recalculate and repaint. For performance-sensitive UDFs handling large datasets (e.g., simulations, massive lookups, or data feeds), this behavior introduces severe bottlenecks:
- Massive CPU overhead: redundant calculations across thousands of unchanged cells.
- UI lag: the screen visibly stutters or freezes during frequent updates.
- Regression to workarounds: developers must abandon clean dynamic arrays and revert to legacy CSE arrays or complex VBA macros just to keep the workbook responsive.
I propose introducing support for partial or delta updates within dynamic array spill ranges. Excel's calculation engine should allow a UDF to target and update specific (row, column) indices in place, without marking the entire parent formula block as dirty or triggering a full-range UI repaint.
Why does it matter? Dynamic arrays are the future of Excel, but full-range recalculation is a major bottleneck for heavy data processing. This enhancement would unlock massive performance gains, keeping Excel highly competitive for large-scale, complex modeling.