Microsoft Excel: Generate a Dynamic Table from Variable-Length Data
Question details
The user needs to expand each source section into multiple output rows and calculate the bottom and top values dynamically as the number of sections changes.
- Product
- Microsoft 365 Excel
- Device & OS
- not provided
- Scenario
- A source table contains a name, starting bottom value, portion size, and variable number of portions. The user wants one output row for every portion without manually rebuilding the table.
- Observed behavior
- The required output row count changes according to the portion count in each source row, so a fixed-size table does not update automatically.
Confirm the source columns and identify the fields for the name, starting bottom value, portion size, and number of portions. If you plan to use a dynamic array formula, check that the Excel version supports LET, FILTER, REDUCE, SCAN, and VSTACK.
Use a dynamic array formula to expand every source section
Use this approach in Microsoft 365 Excel when the source data and output must update automatically from a single formula.
Place the formula in the first cell of an empty output area. The formula filters blank or zero-count records, creates the required sequence for each section, calculates each bottom and top boundary, and stacks the results into one spilled table.
In the example below, columns A, B, D, and E represent the name, starting bottom, portion size, and number of portions. Change the ranges and column indexes to match your worksheet.
Place the source records in columns A:E, with the name in column A, the starting bottom value in column B, the portion size in column D, and the number of portions in column E. Leave an empty area for the results.
Select the first output cell and enter =LET(data,FILTER(A2:E1000,(A2:A1000<>"")*(E2:E1000>0)),REDUCE({"Name","Bottom","Top"},SEQUENCE(ROWS(data)),LAMBDA(acc,r,LET(n,INDEX(data,r,5),name,INDEX(data,r,1),bottom,INDEX(data,r,2),size,INDEX(data,r,4),k,SEQUENCE(n),VSTACK(acc,HSTACK(IF(k,name),bottom+size*(k-1),bottom+size*k))))))). Press Enter to let the result spill into the cells below.
Verify that each source record produces the number of rows specified by its portion count and that the Bottom and Top columns increase by the portion size. Extend the A2:E1000 ranges if the source table can exceed 999 rows.
Load the source table with Power Query and refresh it
Use Power Query when you prefer a refreshable transformation instead of a worksheet formula, especially for larger input tables.
Generate the expanded table with a VBA macro
Use VBA when dynamic array functions are unavailable or when you need a button-driven worksheet process.
Build an expanding data table in WPS Spreadsheet
WPS Spreadsheet can create a dynamic output table from variable-length source sections using compatible formulas, or you can use a repeatable worksheet workflow to calculate each portion's boundaries. Open the source workbook, place the expansion formula in a clear output area, and save the result in a familiar Excel-compatible format.
- 1. Open the workbook: Launch WPS Office, select Spreadsheet, and open the Excel .xlsx file containing the source table.
- 2. Check the source columns: Confirm the name, starting bottom value, portion size, and number-of-portions columns, then reserve an empty area for the generated output.
- 3. Enter the expansion formula: Select the first output cell and enter a compatible dynamic-array formula using LET, FILTER, REDUCE, SEQUENCE, and VSTACK to generate one row per portion. Use the formula bar to edit the source ranges and column indexes.
- 4. Verify and save the table: Confirm that each section produces the expected number of rows and that its Bottom and Top values are correct. Choose Menu > Save As and select Excel Workbook (*.xlsx) when sharing the file.
Frequently Asked Questions
Which Excel columns does the dynamic formula use?
The example formula assumes the name is in column A, the starting bottom value is in column B, the portion size is in column D, and the number of portions is in column E. Change the INDEX column numbers and source ranges if your layout differs.
Why does the formula return a spill error?
A spill error usually means cells in the intended output area are not empty. Select the spill range, remove existing values or merged cells, and enter the formula again.
Can blank or zero-portion rows be excluded?
Yes. The FILTER condition in the example keeps rows where the name is not blank and the portion count is greater than zero, so blank and all-zero records are excluded.
What can I use if REDUCE or VSTACK is unavailable?
Use Power Query to create and expand a sequence for each source row, or run a VBA macro that loops through each portion. These alternatives do not require the newest dynamic array functions.
How do I update the generated table after changing the source?
A dynamic array formula recalculates automatically when the source changes. For Power Query, right-click the output table and choose Refresh. For VBA, run the generation macro again.
