Create an Excel Multi-Section Summary with One Dynamic Array Formula
Question details
Create a structured Excel summary from source values using one dynamic-array formula instead of multiple PivotTables, hidden rows, and manually maintained calculations.
- Product
- Microsoft Excel for Microsoft 365 or Excel for the web
- Device & OS
- not provided
- Scenario
- The source data is in B2:C5, with section names in column B and values in column C. The goal is to generate headings, totals, and percentages in a single spilled report.
- Observed behavior
- The desired result is a dynamically updated summary that expands or recalculates when the source values change.
Confirm that your Excel version supports dynamic-array functions, including LET, REDUCE, VSTACK, and HSTACK. Leave the cells below and to the right of the formula cell empty so the spilled result has room to expand.
Use LET and REDUCE to build the summary from B2:C5
Use this option when you need one formula to create a category summary with values and percentages of the total.
This formula treats column B as the section or category column and column C as the numeric value column. It uses INDEX to define the source columns, REDUCE to process each unique section, and VSTACK and HSTACK to assemble the final report.
Click an empty cell where the report should begin, such as E2. Make sure the intended spill range is clear.
Enter the following formula and press Enter: =LET(src,B2:C5,grp,INDEX(src,,1),amt,INDEX(src,,2),sections,UNIQUE(FILTER(grp,grp<>"")),grand,SUM(amt),REDUCE({"Section","Value","% of Total"},sections,LAMBDA(acc,s,LET(sectionTotal,SUMIF(grp,s,amt),VSTACK(acc,HSTACK(s,sectionTotal,TEXT(sectionTotal/grand,"0.0%")))))))
Excel spills a three-column report containing the section name, its total value, and its percentage of the grand total. Update values in B2:C5 to recalculate the report automatically.
Resolve dynamic-array errors before creating the report
Use this option if the formula returns #SPILL!, #NAME?, or an incorrect percentage result.
Create a Spilled Section Summary in WPS Spreadsheets
WPS Spreadsheets can help you build a compact, formula-driven summary instead of maintaining multiple report areas. Enter a dynamic-array formula in an empty report cell, then let the result spill into rows for each section, its total, and its percentage.
- 1. Open the source workbook: Open the XLSX file in WPS Spreadsheets and confirm that section labels are in column B and numeric values are in column C.
- 2. Choose an empty output cell: Click an empty starting cell, such as E2, and ensure the cells below and to the right are not occupied.
- 3. Enter the summary formula: Type the LET-based dynamic-array formula in the output cell, using B2:C5 or your actual source range, then press Enter.
- 4. Format the result columns: Select the value and percentage columns in the spilled output, then use Home > Number Format to apply Number and Percentage formatting as needed.
Frequently Asked Questions
Which Excel versions support LET, REDUCE, VSTACK, and HSTACK?
These functions are available in supported Microsoft 365 desktop builds and Excel for the web. If a function returns #NAME?, update Excel or use a version that includes dynamic-array functions.
Why does my dynamic-array summary show a #SPILL! error?
A #SPILL! error means another value, merged cell, or table boundary is blocking the output range. Select the formula cell, identify the blocked cells from the warning icon, and clear or move the obstruction.
How can I change the source range from B2:C5 to a larger range?
Edit the src reference at the start of the formula. For example, change src,B2:C5 to src,B2:C100, then confirm that the first source column contains section names and the second contains values.
Why are the summary percentages incorrect?
Check that the values in column C are numeric and that the source range does not include unwanted blanks, text values, or an extra total row. The formula calculates each section total divided by SUM of the value column.
Can I add this dynamic summary beside an existing PivotTable?
Yes. Place the formula in an empty area outside the PivotTable and leave enough blank cells for the result to spill. Do not place the formula inside a PivotTable output range.
