Create a Dynamic Running Total in Excel Tables
Question details
The user needs to create running and reducing totals that update automatically as data is added or grouped.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- A source range contains values that need cumulative totals, with formulas that continue working when new rows are added.
- Observed behavior
- The desired totals should fill down automatically in a table or spill dynamically from an array formula.
Confirm that the source range has clear headers and that the values used for accumulation are numeric. Decide whether you need one continuous running total or separate totals grouped by a product or category.
Use an Excel table with a calculated running-total column
Use this approach for a simple running total that should automatically fill down and expand when new records are added.
Excel tables use structured references, so a calculated-column formula can propagate to existing rows and new rows automatically.
Select the source range, open the Insert tab, choose Table, confirm that My table has headers is selected, and click OK.
In the first cell of a new column such as Running Total, enter =SUM(INDEX([Amount],1):[@Amount]) and press Enter. Replace Amount with the name of your numeric table column.
Enter a new record in the first blank row directly below the table. Excel should extend the table and copy the running-total formula into the new row.
Generate grouped running and reducing totals with dynamic arrays
Use this method when totals must spill into a result range or reset for each product, category, or other group.
Create Auto-Expanding Running Totals in WPS Spreadsheets
WPS Spreadsheets can create running totals with table-style calculated columns and supported dynamic-array formulas. Use a structured table for automatic formula expansion or build a spilled calculation for grouped data, then save the result in a familiar Excel-compatible format.
- 1. Create the data table: Open WPS Spreadsheets, select the source range, and use the Insert tab to create a table with headers.
- 2. Enter the running-total formula: Add a Running Total column and enter a structured-reference formula such as =SUM(INDEX([Amount],1):[@Amount]), replacing Amount with the numeric column name.
- 3. Build a dynamic result when needed: For supported versions, select an empty output cell and enter a SCAN formula such as =SCAN(0,B2:B11,LAMBDA(total,value,total+value)).
- 4. Check and save the output: Confirm that the totals update when values or rows change, then choose File > Save As and select Excel 2007-365 Workbook (*.xlsx) when Excel compatibility is required.
Frequently Asked Questions
How do I make an Excel running total expand when I add rows?
Select the source range, choose Insert > Table, and enter the running-total formula in the calculated column. Excel copies the formula into new rows added directly below the table.
What formula creates a basic running total in a table?
If the numeric table column is named Amount, enter =SUM(INDEX([Amount],1):[@Amount]) in the Running Total column. Replace Amount with your actual column name.
How can I reset a running total for each product?
Filter or group the amount range by product, then calculate each group separately with functions such as FILTER, UNIQUE, LET, SCAN, and VSTACK. A table with a helper column is an alternative for older Excel versions.
Why does my dynamic running-total formula show a #SPILL! error?
The formula's output area contains existing content or merged cells. Select the warning icon, clear the cells blocking the spill range, and enter the formula again.
Can I use SCAN and REDUCE in older Excel versions?
SCAN and REDUCE require newer Excel releases with dynamic-array support. For older versions, use a table and a structured-reference formula copied through the calculated column.
