Dynamic arrays in excel

Question

Hi,

In an excel file I have a sheet with a few rows of data.

In a separate sheet I have a row for each row of data, such row includes numerous columns with complex formulas and calculations.

I am trying to make the number of rows in the calculation sheet increase / decrease dynamically based on the number of rows in the data sheet.

I was trying to find a way to "wrap" the existing formulas with Let, Map and Lambda. However I find this impossible to do since the dynamica array doesn't pull down formulas the same way dragging a formula down would do. Even if the references in the formula are not fixed (without $ signs) the reference is always to the first row.

I know this is solvable by refering to ranges, e.g.- instead of writing E4 you should write E4:E100 but it is not practical for me to change all the formulas accordingly, it is much more easy to just keep on dragging the formulas down manually.

Has anyone encounter such issue? found a solution? can confirm there is no solution?

Thx, david

Answer 1

Hi, I have attached a screen shot of a simple example. Say the formula I would normally drag down is G5*H5 and I am trying automate the number of rows I want it dragged down based on Column A . I have used Formulatext in the bottom of each column. In column B I tried to wrap the formula but it will always return 42. Columns C and F do work but the acutal formula I have is too complicated to write it in thses ways, hope this is clear, Rgds, David

Answer 2

Hi @David Netser

Thank you for posting your question in the Microsoft Q&A forum.    

Regarding your issue, to better understand the structure and formulas involved, could you please share a simplified copy of the workbook (with sensitive data removed) for me via Private Message?

If sharing the full file isn’t possible, a small sample or screenshot that reproduces the issue would also be helpful.

Hope to hear back from you soon.  

The initial response may not fully address your needs, but it will help me better understand your situation. Once I get the information, I will try my best to find the next steps.  

---

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 3

Dynamic arrays do not “auto-fill” row-relative references the same way as dragging a formula down. A spilled formula always evaluates from its own cell context, so simple references like =E4 will not automatically become E5, E6, etc. across the spill range.

To make a calculation sheet resize automatically with the data sheet, formulas must be written to work with ranges/arrays rather than single-row references. There is no supported way to take an existing set of row-by-row formulas and have LET/MAP/LAMBDA spill them while preserving the old relative-row behavior without rewriting them.

Supported approaches from the documentation:

1. Use dynamic array formulas that spill
   • Any formula that returns multiple results will spill automatically into neighboring cells and resize as data is added or removed.
   • Example pattern: instead of =E4*F4 copied down, use something like =E4:E100*F4:F100 entered once and let it spill.
   • This is exactly the “range” style you mentioned; it is the required pattern for dynamic arrays.
2. Use Excel Tables and structured references (recommended)
   • Convert the data sheet to a Table and use structured references in formulas. Tables automatically expand and contract as rows are added or removed, and formulas in the table expand with them.
   • This avoids manually changing ranges and is the recommended way to “allow for extra data” without performance issues.
3. Use dynamic named ranges (INDEX/COUNTA) feeding array formulas
   • Define a dynamic range with INDEX/COUNTA, for example:
     =Sheet1!$A$1:INDEX(Sheet1!$A:$A,COUNTA(Sheet1!$A:$A)+ROW(Sheet1!$A$1)-1,1)
   • Use that name in array formulas so they automatically expand/contract with the data.
   • This still requires formulas that operate on ranges, not single cells.

Given the current Excel behavior, there is no built-in way for dynamic arrays to “pull down” an existing row-based formula and keep its row-relative references without rewriting it into an array/range-based form or using Tables. For a fully dynamic row count, formulas must be refactored to work on arrays.

---

References:

• Guidelines and examples of array formulas
• Dynamic array formulas vs. legacy CSE array formulas
• Dynamic array formulas and spilled array behavior
• Excel performance: Tips for optimizing performance obstructions
• Create an array formula