How to Create a Dynamic One-Cell Summary Table in Excel
Question details
The user wants to build a budget summary that groups suppliers by category and calculates monthly totals dynamically from a single formula using dynamic-array functions.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Creating an automated, expandable budget summary without relying on manual pivot tables or dragging traditional formulas.
- Observed behavior
- The user needs a single-cell formula that spills the calculated summary matrix dynamically as new rows are added to the source table.
Ensure your source data is formatted as an official Table (using Ctrl+T) to enable dynamic ranges, and verify that your spreadsheet software supports modern dynamic array functions like LET, UNIQUE, and LAMBDA.
Use LET, UNIQUE, and MAKEARRAY Functions
This is the most structured approach for creating a dynamic summary matrix that calculates monthly totals by category in a single spilling cell.
This method leverages dynamic arrays to extract unique categories and column headers, then fills the summary matrix using MAKEARRAY and SUMIFS.
By converting your data into an official Table (e.g., Table1), any new rows added will automatically update the spilled summary table without needing formula adjustments.
Select your source data and press Ctrl+T to format it as a Table. Go to the Table Design tab and rename it to 'Table1'.
Click the single empty cell where you want the top-left corner of your summary table to start.
Enter the formula: =LET(types, UNIQUE(Table1[Type]), months, Table1[[#Headers],[Jan]:[Dec]], body, MAKEARRAY(ROWS(types), COLUMNS(months), LAMBDA(r, c, SUMIFS(INDEX(Table1, 0, MATCH(INDEX(months, 1, c), Table1[#Headers], 0)), Table1[Type], INDEX(types, r)))), HSTACK(types, body))
Press Enter to allow the formula to calculate and spill dynamically across the necessary rows and columns.
Build Summary with REDUCE and VSTACK
If MAKEARRAY is unavailable or you need a different customized stacking behavior, REDUCE paired with LAMBDA and VSTACK can stack category summaries iteratively.
Create Dynamic Summary Tables Easily in WPS Spreadsheet
WPS Spreadsheet fully supports advanced dynamic array functions like LET, UNIQUE, FILTER, and MAKEARRAY. You can build powerful one-cell spilling formulas seamlessly, just as you would in Microsoft Excel.
- 1. Open WPS Spreadsheet: Launch WPS Office, open Spreadsheet, and load your budget data workbook.
- 2. Format as Table: Highlight your data range and go to Insert > Table to create a structured table reference.
- 3. Apply Array Formula: Type your dynamic array formula (e.g., using UNIQUE, MAKEARRAY, and LET) into a single destination cell.
- 4. Generate Results instantly: Press Enter to instantly generate a spilling summary table that updates when new rows are added.
Frequently Asked Questions
Why is my dynamic array formula returning a #SPILL! error?
A #SPILL! error occurs when the formula's output range is blocked by existing data or formatting. Clear any text, values, or merged cells in the adjacent area where the formula needs to expand, and the summary table will generate correctly.
Do these dynamic array functions work in older versions of Excel?
Functions like LET, UNIQUE, VSTACK, and MAKEARRAY are only available in Microsoft 365, Excel 2021, and modern alternatives like WPS Office. Older versions (such as Excel 2016 or 2019) do not support the dynamic spilling array calculation engine.
How do I make the formula include newly added columns automatically?
By referencing structured table column arrays (like Table1[[#Headers],[Jan]:[Dec]]), the formula automatically scales. If you insert new month columns strictly within that specified range in the source table, the dynamic output will update instantly.
