Running Total in Excel — 6 Easy Methods + Examples (2026)

Month-to-date sales, account balance after each transaction, cumulative spend against budget — they are all the same calculation: a running total. Excel has at least six ways to build one, from a two-character tweak to a SUM formula up to a single dynamic-array function that fills the whole column. In 30 minutes you’ll learn them all: the classic expanding range =SUM($B$2:B2), the reference anchoring that makes it work, Table-safe structured references, SUMIF/SUMIFS conditional accumulations, the pivot table’s built-in Running Total, the 365-only SCAN method, and how to reset totals by month or category. Works in Excel 2010 through Microsoft 365.

Dr Waqas Rafique · Founder & CTO · PhD, Statistical Machine Learning
LinkedIn · About

What is a Running Total?

A running total (also called a cumulative sum) shows, at each row, the sum of every value from the start of the list down to that row. If January sells 120, February 95, and March 140, the running total reads 120, 215, 355 — each entry is “everything so far”. The final value always equals the grand total, which doubles as a built-in check.

The reason the calculation appears everywhere is that so far is how progress is actually judged. A bank statement is a running total of deposits and withdrawals. A sales dashboard’s “month-to-date” figure is a running total reset each month. Budget tracking compares cumulative spend against cumulative budget. Project burn-up charts plot a running total of completed work against scope. The raw per-period numbers bounce around; the running total turns them into a single rising line whose slope is the pace and whose height is the position.

Conceptually trivial, the running total is nonetheless a small masterclass in how Excel works, because each of the six methods in this guide exercises a different mechanism: mixed absolute/relative references (the classic formula), structured references (Tables), criteria-based accumulation (SUMIF/SUMIFS), built-in value calculations (pivot tables), functional iteration (SCAN/LAMBDA), and conditional restarts (group resets). Learn the running total six ways and you have quietly learned a large slice of practical Excel.

One sibling worth distinguishing: a running total accumulates forever, while a moving average looks at a fixed sliding window. Use the running total for “how much in total so far?”; use the moving average for “what is the recent level, smoothed?”. The expanding-range trick you learn in Step 1 is also the foundation of expanding-window variants of other functions — running maxima, running counts, cumulative averages — all built the same way.

Before You Start

Running totals are forgiving, but two preparation habits prevent nearly every problem later.

Sort by date (or sequence) first

A running total is meaningful only in order. If the transactions are shuffled, the cumulative column still adds up to the same grand total, but every intermediate value — the balance after each transaction — is wrong. Sort ascending by date before you build, and re-sort before reading if anyone has fiddled with the sheet.

Keep one row per event, values in one column

The examples assume dates in column A and amounts in column B from row 2 down, with the running total built in column C. Refunds or withdrawals should be negative amounts in the same column, not a separate column — the running total then naturally falls when money goes out, exactly like a bank balance.

Decide: spreadsheet formulas or pivot table?

If you need the cumulative value beside each transaction (a balance column), use formulas (Steps 1–4, 6–7). If you need cumulative values per period in a summary (cumulative monthly sales), the pivot-table method (Step 5) is faster and refreshes itself. Many workbooks legitimately use both.

1.Step 1 — The Classic Expanding-Range SUM

The canonical running total is one formula with a deliberate asymmetry. With amounts in B2:B13, go to C2 and enter:

In C2 it sums the trivially small range B2:B2 — just the first value. Now fill down. Because the start $B$2 is absolute (locked by the $ signs) and the end B2 is relative, the copies become:

C2:  =SUM($B$2:B2)    → first value
C3:  =SUM($B$2:B3)    → first two values
C4:  =SUM($B$2:B4)    → first three values
...
C13: =SUM($B$2:B13)   → everything = grand total

The range expands one row per row — the top stays pinned, the bottom slides with the formula. That is the whole trick, and it generalises: swap SUM for COUNT, MAX, or AVERAGE over the same expanding range and you get a running count, running maximum, or cumulative average.

An alternative you will meet in older workbooks is the simple chain: C2 is =B2, then C3 down is =C1+B2-style (=C2+B3 etc.). It calculates faster on enormous sheets, but it is fragile — sorting, inserting, or deleting rows scrambles or #REF!s the chain. Prefer the expanding range as your default; Step 8 covers exactly when the chain bites.

2.Step 2 — Absolute vs Relative References, Properly Understood

The expanding range only works if you understand why it works, because one misplaced $ silently breaks it. When Excel copies a formula, every reference adjusts by the distance copied — unless the $ signs forbid it. The four anchoring states of a reference:

The running total needs the start fully locked and the end fully relative: $B$2:B2. Get it wrong and the symptoms are distinctive: $B$2:$B$2 (both locked) shows the same value in every row; B2:B2 (both relative) shows each row’s own value, not a total — the range slides instead of expanding.

Editing tip: click into the formula bar, place the cursor on a reference, and tap F4 (Fn+F4 on many laptops, Cmd+T on Mac) to cycle through the four states instead of typing $ signs by hand. If you only memorise one anchoring pattern in your Excel life, make it this one — the same locked-start/relative-end idea powers expanding criteria ranges in Step 4, cumulative percentage columns, and Pareto charts.

3.Step 3 — Running Totals in Excel Tables

If your data is an Excel Table (Ctrl+T) — and for transaction lists it should be — the rules change. Tables use structured references like [@Amount], and a pasted $B$2:B2 formula can behave oddly because Table formulas auto-fill identically into every row. The Table-native expanding range is:

Decoded: INDEX([Amount],1) returns the first cell of the Amount column — the pinned start — and [@Amount] is the Amount cell in this row — the sliding end. The range between them expands exactly like $B$2:B2, but in a form the Table understands. Type it once in the first row of a “Running Total” column and the Table instantly fills the whole column as a calculated column.

The payoff is automation: add a new transaction at the bottom and the Table extends itself, the running-total formula appears in the new row, and any chart built on the Table grows too. No dragging, no “did someone forget to fill down?”. For recurring data like sales logs and expense registers this is the most maintenance-free of the formula methods.

4.Step 4 — Conditional Running Totals with SUMIF and SUMIFS

Real lists are mixed: several categories interleaved, dates that need cut-offs. Conditional running totals apply the expanding-range trick to criteria ranges as well.

Per-category running total

With categories in A and amounts in B, this in C2 (filled down) keeps a separate accumulation for every category, even when the rows alternate:

Read it as: among the rows from the top down to here ($A$2:A2), find those whose category matches this row’s category (A2), and sum their amounts over the matching expanding range ($B$2:B2). Each category’s total grows only on its own rows — row by row you can watch “Food” and “Travel” accumulate independently in one column.

Date-based (to-date) running total

When rows might not be perfectly sorted, accumulate by date rather than position:

This sums every amount whose date is on or before the current row’s date — a true “as of this date” figure that is correct even if the rows are shuffled. Add more criteria pairs for combinations: category and to-date gives a per-category month-to-date in one formula. The same machinery drives budget vs actual tracking, where cumulative actuals meet cumulative budget.

5.Step 5 — Pivot Table Running Totals (Show Values As)

For summaries — cumulative sales by month, cumulative sign-ups by week — skip formulas entirely: pivot tables have running totals built in.

1. Build a pivot (Insert → PivotTable) with your date field in Rows (grouped to Months if needed) and the value field in Values.
2. Drag the same value field into Values a second time, so you have “Sum of Sales” and “Sum of Sales2”.
3. Right-click any number in the second column → Show Values As → Running Total In… → choose the base field (the date/month field). OK.

The second column now shows the cumulative figure beside the per-month one, and both refresh when the source data changes. Two related options in the same menu are worth knowing: % Running Total In shows cumulative progress as a percentage of the grand total (perfect for “we’re 64% of the way to target”), and within a pivot that has an outer grouping (Year above Month), the running total automatically restarts at each year — the group-reset behaviour of Step 7, for free.

The limitation mirrors the strength: the cumulative number exists only inside the pivot’s summary, not next to each raw transaction. Need a balance per row? Formulas. Need a cumulative summary that maintains itself? Pivot. Full pivot mechanics — grouping, slicers, calculated fields — are in the pivot table in Excel guide.

6.Step 6 — The Modern Way: SCAN + LAMBDA (Excel 365)

Microsoft 365’s dynamic arrays collapse the entire exercise into one formula that spills. In C2 — just the one cell — enter:

SCAN walks along B2:B13 carrying an accumulator acc that starts at 0. For each value v it computes acc+v, keeps that intermediate result, and passes it forward. The output is the full sequence of intermediate sums — the running total — spilled down twelve cells from C2. There is nothing to fill, nothing to anchor, and exactly one formula that can possibly be wrong.

The robustness advantages are real: inserting rows inside B2:B13 just works (the range stretches); no cell-by-cell drift can creep in; and the formula reads as a statement of intent. Make it fully self-extending by pointing it at a Table column — =SCAN(0,tblSales[Amount],LAMBDA(a,v,a+v)) — so new transactions lengthen the spill automatically.

And because the accumulator can carry anything, SCAN generalises beyond sums: LAMBDA(a,v,MAX(a,v)) gives a running maximum, LAMBDA(a,v,a*v) a running product (compound growth!), and a starting balance is just a different seed: =SCAN(5000,B2:B13,LAMBDA(a,v,a+v)) runs a bank balance from an opening £5,000.

7.Step 7 — Resetting the Running Total by Group

Month-to-date, quarter-to-date, per-project burn — many running totals must restart at zero when a new group begins. On data sorted by group, the pattern is a single IF comparing each row’s group to the one above:

If this row’s group (A2) matches the previous row’s (A1), continue accumulating (C1+B2); if it differs — including the very first data row, where A1 is the header — restart at this row’s own value (B2). Enter it in C2 and fill down.

For monthly resets on a raw date column, compare months rather than whole cells:

TEXT(date,"yyyymm") renders each date as “202606”-style year-month, so the comparison is true within a month and false across the boundary — the total quietly drops back to the first value of each new month: the month-to-date column every sales dashboard needs.

Unsorted data? Use the Step 4 conditional method instead — =SUMIFS($B$2:B2,$A$2:A2,A2) with the month (or a TEXT helper column) as the criterion resets correctly regardless of row order, at the cost of a little more calculation.

8.Step 8 — Pitfalls: Where Running Totals Go Wrong

Four mistakes account for nearly every broken cumulative column in the wild.

Sorting after building a chained total

The =C1+B2 chain depends on physical row order. Sort the data and every formula still adds “the cell above” — which is now a different transaction — producing garbage that still looks like a running total. If a sheet gets sorted or filtered, use the expanding range, SUMIFS-by-date, or SCAN.

Including the running total in its own range

Point =SUM($B$2:B2) at column B and put it in column C — never sum a range that contains the formula’s own column, or you create a circular reference (Excel warns) or, worse with whole-column ranges like B:B, a subtly wrong total.

Accumulating over hidden/filtered rows unintentionally

SUM ignores filtering — hidden rows still count. If you want the running total of visible rows only, you need SUBTOTAL(9,…) over the expanding range, and you must accept it recalculates as filters change. Decide which behaviour you mean; both are defensible, but mixing them up misstates balances.

Charting raw and cumulative on one axis

A cumulative line quickly dwarfs the per-period bars, flattening them into invisibility. Plot the running total on a secondary axis (Chart → right-click series → Format Data Series → Secondary Axis), or give it its own chart. The combined column-plus-cumulative-line chart, done properly, is the classic Pareto/progress visual.

Try It: Running-Total Playground

This is the whole tutorial in one sandbox. The table is a list of transactions across two categories — edit any amount and everything recalculates. Switch methods to see the same data accumulated three ways: the plain expanding SUM, the month-reset version from Step 7 (watch the total snap back to zero at each month boundary — marked with a pink rule), and the per-category SUMIF from Step 4. The chart plots the cumulative line(s) live, exactly as a chart on your sheet would.

Advanced Running-Total Tips

Running balance with an opening figure

Bank-style balances are a running total seeded with an opening balance. Either add the opening cell to the classic formula — =$E$1+SUM($B$2:B2) — or seed SCAN directly: =SCAN(OpeningBalance,B2:B13,LAMBDA(a,v,a+v)). Deposits positive, withdrawals negative, and the column reproduces the statement.

Cumulative percentage and Pareto analysis

Divide the running total by the grand total — =SUM($B$2:B2)/SUM($B$2:$B$13), formatted as % — on data sorted descending and you have a Pareto column: the row where it crosses 80% reveals the “vital few” customers or products driving most of the value.

Running totals over dates that don’t exist yet

For dashboards comparing cumulative actuals against a full-period plan, build the date spine for the whole period and let SUMIFS-by-date fill actuals only up to today: =IF(A2>TODAY(),"",SUMIFS(…)) keeps the actuals line from flat-lining at zero across the future.

Performance on very large sheets

The expanding range is O(n²) — row 50,000 sums 50,000 cells. On six-figure row counts, the chained =C1+B2 (kept safe inside a never-sorted Table) or a single SCAN spill recalculates dramatically faster. For million-row data, do the accumulation in Power Query or a pivot instead.

From cumulative column to live dashboard

A running total is usually step one of a progress dashboard someone wants to see. Upload the sheet to DataHub Pro and the cumulative line, the month-to-date figures, and a forecast of where the line is heading are built for you — one live link instead of a screenshot that is stale by lunchtime.

Worked Example: A Month of Café Takings

A café owner logs daily takings in column B and wants three things: the cash position through the month, a month-to-date figure for the accountant, and a split between food and drink. One small sheet, three methods.

The data (12 rows, two categories): 28 May to 8 June, amounts between £80 and £220, each tagged Food or Drink in column A… exactly the playground table above.

1. Cash position — expanding SUM. =SUM($C$2:C2) beside the amounts gives the cumulative takings after each day. The final cell reads £1,705 — and cross-checking it against =SUM(C2:C13) confirms the column is built correctly (the last running total must equal the grand total; if it doesn’t, an anchor is wrong somewhere).

2. Month-to-date — the reset. The accountant wants June only. =IF(TEXT(A2,"yyyymm")=TEXT(A1,"yyyymm"),D1+C2,C2) restarts at the 1 June row: May finishes at £612, then the column drops to £145 and climbs again — the June-to-date figure is always the bottom visible value, no manual range-fiddling each month.

3. Food vs drink — SUMIF. =SUMIF($B$2:B2,B2,$C$2:C2) tracks each category’s own accumulation through the interleaved rows. By 8 June: Food £1,010, Drink £695 — numbers that drove a real decision (extend food service an extra hour) because the cumulative gap was visible weeks before the monthly P&L would have shown it.

Total build time: under five minutes. The same three formulas scale unchanged from a 12-row café log to a 12,000-row sales ledger — which is the quiet beauty of the running total: the formula never gets harder, only the data gets longer.

Common Errors & Fixes

Every row shows the same value

Both ends of the range are anchored: =SUM($B$2:$B$2). Free the second reference ($B$2:B2) so the range can expand, then re-fill the column.

Each row shows only its own value

Neither end is anchored: =SUM(B2:B2) filled down becomes =SUM(B3:B3)… a sliding single-cell range. Lock the start: $B$2.

#REF! cascading down the column

A row was deleted under a chained =C1+B2-style total — every formula below the deletion lost its parent. Rebuild with the expanding range, which has no row-to-row dependency to sever.

Circular reference warning

The SUM range includes the running-total column itself (commonly from whole-column references like =SUM($B:$B) placed in column B, or a mis-dragged range). Keep raw values and cumulative formulas in strictly separate columns and reference only the raw one.

#SPILL! from the SCAN formula

Something occupies the cells where the array wants to spill — often an old filled-down formula. Clear everything below the SCAN cell in that column and it will spill cleanly.

The running total ignores my filter

By design: SUM counts hidden rows. If you want visible-rows-only accumulation, switch to =SUBTOTAL(9,$B$2:B2) — and be aware the numbers now change whenever the filter does.