Cellwise Try it free

Excel guide · 2 min read

How to make a running total in Excel

Quick answerIn the first row type =SUM($B$2:B2) and fill it down. The start of the range is locked and the end moves, so each row adds everything from the top down to that row.

Sample data

Every result on this page was calculated from this sheet by the same spreadsheet engine the Cellwise app uses.

ABCDEF
1DateSalesRunning totalDepositWithdrawal
22026-10-0142042010000
32026-10-023808000250
42026-10-035101310500120
52026-10-042951605
62026-10-056402245

Method 1: SUM with a half-locked range

$B$2 is an absolute reference, so it never moves. B2 is relative, so it becomes B3, B4 and so on as you fill down. Each row's formula covers a range that grows by one row.

Type it in C2, then double-click the fill handle (the small square at the cell's bottom-right corner). In row 6 the formula has become =SUM($B$2:B6).

=SUM($B$2:B6)

Result: 2245

By 5 October the running total is 2,245.

Method 2: Previous total plus this row

Another common pattern is =C2+B3 in C3: the total so far plus today's amount. It is efficient on huge sheets, but it breaks if you delete a row (you get #REF!) and it cannot go in the first row next to a text header.

Avoid writing =C1+B2 in row 2: C1 holds the text "Running total", and adding text to a number gives #VALUE!.

=C2+B3

Result: 800

Returns 800, the first two days added together.

Running balance with money in and out

For a bank-style balance, add deposits and subtract withdrawals, both from locked-start ranges. Put this in G2 and fill down. Here it is shown for row 4.

=SUM($E$2:E4)-SUM($F$2:F4)

Result: 1130

After three rows the balance is 1,130.

Running total in a PivotTable

If your data is already in a PivotTable, you do not need a formula column.

  1. Right-click any value in the Sales column of the PivotTable.
  2. Choose Show Values As > Running Total In.
  3. Pick the Base field (usually Date) and click OK.

Excel 365: one formula for the whole column

In Excel 365 you can spill a running total from a single cell with SCAN: =SCAN(0,B2:B6,LAMBDA(total,x,total+x)). It starts at 0 and adds each value in turn. It only works in Excel 365 and Excel for the web.

Common mistakes

Every row shows the same total

Both ends are locked, like $B$2:$B$6. Only the first reference should have dollar signs: $B$2:B2.

The running total does not grow

Neither end is locked, so each row only sums itself. Lock the start with F4 so it reads $B$2.

New rows are not included

Convert the data to a table (Ctrl+T). Formulas in a table column fill down automatically for new rows.

FAQ

What is the formula for a running total in Excel?

=SUM($B$2:B2) in the first row, filled down. The locked start and moving end make it a cumulative sum.

How do I make a running total that resets each month?

Use SUMIFS with a locked-start range and a month condition, such as =SUMIFS($B$2:B2,$D$2:D2,D2), where column D holds the month.

How do I create a running count?

Use =COUNTIF($A$2:A2,A2) to count how many times each value has appeared so far. It is also a quick way to spot duplicates.

Does a running total update when I change a number?

Yes. Each cumulative formula recalculates, so changing day 2 updates every total below it. That is the main advantage over typing totals by hand.

How do I show a running total as a percentage?

Divide each running total by the grand total, like =C2/SUM($B$2:$B$6), and format as Percent. The last row reaches 100%, which is the basis of a Pareto chart.

Functions used

Related guides

Updated