How to Calculate a Running Total in Excel

The classic way to build a running total in Excel is a SUM formula with one end of the range anchored and one end free, copied down the column. Excel 365 can produce the entire running total in a single spilled formula with SCAN and LAMBDA, and pivot tables offer a running total as a built-in value display option with no formula at all.

How do I create a running total with a SUM formula?

Anchor the start of the range with $ and leave the end unanchored, so copying the formula down grows the summed range by one row each time instead of shifting it.

Column B: values, C2 formula copied down C2:C20

C2: =SUM($B$2:B2)   → sums just B2
C3: =SUM($B$2:B3)   → sums B2:B3
C4: =SUM($B$2:B4)   → sums B2:B4
...

The $ before the first "B2" locks the start of the range.
The second reference has no $, so it grows to match
whatever row the formula is copied into.

This is the most portable method — it works in every Excel version back to the earliest ones, requires no special functions, and is easy for anyone opening the workbook later to understand at a glance.

How do I create a running total with SCAN and LAMBDA?

SCAN applies a LAMBDA accumulator function across an array and returns every intermediate result as a spilled array — one formula produces the entire running-total column instead of copying a formula down row by row.

=SCAN(0, B2:B20, LAMBDA(acc,val, acc+val))

0              starting value (initial accumulator)
B2:B20         the array to scan across
LAMBDA(acc,val,acc+val)  add each value to the running accumulator

Result: a single spilled column, B2:B20's running total,
with no copy-down needed. Editing B2:B20's length
automatically resizes the spill.

SCAN and LAMBDA are Excel 365 only — they are not available in Excel 2021, 2019, or earlier perpetual licenses. See the LAMBDA function guide for how the accumulator argument pattern works in more detail.

How do I create a running total in a pivot table?

Pivot tables have a built-in "Running Total In" display option that recalculates the running total whenever the underlying data or field layout changes, with no formula involved.

1. Build a pivot table with dates (or categories) in Rows
   and a numeric field in Values
2. Right-click the value field → Value Field Settings
3. Go to the "Show Values As" tab
4. Choose "Running Total In"
5. Pick the field to run the total across (usually the
   same field that's in Rows, e.g. Date)
6. OK

This is the best option when the running total needs to reset per group — for example a running total per region that restarts at zero for each new region — since the pivot's grouping handles that automatically, which a plain SUM formula does not do on its own.

How do I reset a running total per group?

A SUM formula resets per group by anchoring the range to the first row of each group instead of the very top of the whole table — this requires the data to already be sorted or grouped by that category.

Data sorted by Region, with a running total that
resets for each new region (column A = Region,
column B = Amount, column C = running total):

C2: =SUM($B$2:B2)
C3: =IF(A3=A2, C2+B3, B3)
' If this row's region matches the row above, add to
' the previous running total; otherwise start over at
' just this row's value

Copy C3's formula down through the rest of the table

SCAN can do the same reset logic by checking the row's category inside the LAMBDA, but the per-group IF pattern above is easier to read and debug for most spreadsheets.

Which method should I use?

Situation	Best method
Need it to work on any Excel version	Anchored SUM formula
Want one formula instead of copying down rows	SCAN + LAMBDA (365 only)
Data already lives in a pivot table	Running Total In (Value Field Settings)
Running total should reset per category	Pivot table, or SUM formula with an IF check on the category

See the pivot table tutorial for the broader pivot workflow, and the percentage change formula for turning a running total into a period-over-period growth rate.

What are common mistakes with running totals?

• Forgetting to anchor the start of the range: without $, copying the formula down shifts both ends of the range instead of just growing it
• Building a running total on unsorted data expecting per-group resets: the IF-based reset pattern only works if rows for the same category are contiguous
• Using SCAN on a pre-365 workbook: the formula returns a #NAME? error since SCAN and LAMBDA don't exist in older Excel
• Inserting rows inside the running-total range without checking the anchor: a new row above the anchored start cell can shift what "start" means