How to Make a Running Total in Excel
By ExcelReady TeamUpdated 6 min read
The short answer
To make a running total in Excel, type =SUM($B$2:B2) in the first row next to your numbers and fill it down. The dollar signs lock the start of the range at B2, while the end moves down one row at a time, so each row adds everything from the top to itself. The last row equals the grand total.
Key takeaways
- =SUM($B$2:B2), filled down, is the standard running total: a locked start and a moving end.
- The alternative =C2+B3 (previous total plus this row) works, but breaks if a row in the middle is deleted.
- For a running total per category, use =SUMIF($A$2:A2,A2,$B$2:B2).
- A running balance adds money in and subtracts money out: =D2+B3-C3.
- Pivot tables have a built-in Running Total In option under Show Values As.
On this page+
- What is a running total?
- How to make a running total in Excel with SUM
- Why does my running total give the wrong numbers?
- Running total with "previous plus current"
- How to make a running total by category
- How to make a running balance
- Running totals in pivot tables and newer Excel
- Running totals in job tests
- Practise it
A running total in Excel shows the total so far on every row: January's sales, then January plus February, and so on. The standard formula is =SUM($B$2:B2), filled down the column. This guide is for beginners: you'll build one step by step, see why the dollar sign matters, and learn the variations for categories, bank balances and pivot tables.
What is a running total?#
A running total (or cumulative sum) adds each new value to the total of all the values above it. The last row always equals the grand total of the column.
| A | B | C | |
|---|---|---|---|
| 1 | Month | Sales | Running total |
| 2 | Jan | 1,200 | 1,200 |
| 3 | Feb | 950 | 2,150 |
| 4 | Mar | 1,430 | 3,580 |
| 5 | Apr | 1,100 | 4,680 |
| 6 | May | 1,675 | 6,355 |
| 7 | Jun | 1,320 | 7,675 |
Running totals answer questions like "when did we pass 5,000 in sales?" (May) or "how much of the annual budget have we spent by June?".
How to make a running total in Excel with SUM#
The most reliable method is SUM with a range whose start is locked and whose end moves.
Step 1: Write the formula in the first row#
Click C2 and type:
=SUM($B$2:B2)The range $B$2:B2 is just one cell for now, so the result is 1,200.
Step 2: Fill it down#
Select C2:C7 and press Ctrl+D (Windows) or ⌘D (Mac). You can also double-click the small square at the bottom right of C2 (the fill handle).
Step 3: Check how the range grows#
Click C4. The formula is now =SUM($B$2:B4). The $B$2 part stayed the same because the dollar signs make it absolute, while B4 moved down because it is relative. Each row adds from B2 to itself, so C7 shows 7,675, the same as =SUM(B2:B7).
To add the dollar signs quickly, click on B2 in the formula bar and press F4 on Windows or ⌘T on a Mac. Our lesson on relative vs absolute references shows exactly how references change when you copy them.
Why does my running total give the wrong numbers?#
Almost every broken running total comes from the dollar signs.
| Formula in C2 | What happens when filled down | Result in C7 |
|---|---|---|
=SUM($B$2:B2) | Start locked, end moves | 7,675 (correct) |
=SUM(B2:B2) | Both ends move, so each row adds only itself | 1,320 (wrong) |
=SUM($B$2:$B$2) | Both ends locked, every row shows 1,200 | 1,200 (wrong) |
If your running total just repeats column B, the start is not locked. If every row shows the same number, the end is locked too. A quick check: the last running total must equal the SUM of the whole column. For other reasons a total can come out wrong, such as numbers stored as text, see why your SUM total is wrong.
Running total with "previous plus current"#
The other common method adds the current value to the running total above it.
C2: =B2
C3: =C2+B3Fill C3 down to C7. Each row takes the total from the row above and adds its own sale, giving the same 7,675.
Watch the first row: if you put =C1+B2 in C2, you get #VALUE!, because C1 is the header text "Running total" and Excel can't add text to a number. Start with =B2.
This method has one weakness: if you delete a row in the middle, the row below it loses its reference and shows #REF!. The SUM method doesn't have that problem, which is why it's usually the better choice.
How to make a running total by category#
When the list mixes categories, use SUMIF with the same "locked start, moving end" idea. Here are expense claims:
| A | B | C | |
|---|---|---|---|
| 1 | Type | Amount | Running total by type |
| 2 | Travel | 120 | 120 |
| 3 | Meals | 45 | 45 |
| 4 | Travel | 80 | 200 |
| 5 | Office | 30 | 30 |
| 6 | Meals | 25 | 70 |
| 7 | Travel | 60 | 260 |
In C2, type and fill down:
=SUMIF($A$2:A2,A2,$B$2:B2)SUMIF looks only at the rows from the top down to the current row, and adds the amounts whose type matches this row's type. Row 7 adds the three Travel rows: 120 + 80 + 60 = 260. The same trick gives a running count: =COUNTIF($A$2:A2,A2) numbers each Travel claim 1, 2, 3.
How to make a running balance#
A running balance adds money in and subtracts money out. With In in column B and Out in column C:
D2: =B2-C2
D3: =D2+B3-C3Fill D3 down. Each row starts from the previous balance, adds deposits and takes away payments. With 500 in, then 120 out, then 250 in, then 90 out, the balances are 500, 380, 630 and 540. You can also write it with SUM: =SUM($B$2:B3)-SUM($C$2:C3), which survives deleted rows.
Running balances come up in cash books, budget trackers and petty cash sheets, all common tasks in the Excel skills for finance jobs.
Running totals in pivot tables and newer Excel#
Pivot tables can show a running total without any formula. Click a value in the pivot table, right-click and choose Show Values As > Running Total In, then pick the base field, such as Month. This works in Excel for Windows and Mac.
In Microsoft 365, you can also make a running total that spills in one cell with SCAN and LAMBDA: =SCAN(0,B2:B7,LAMBDA(total,x,total+x)). It's neat, but the SUM method works in every version and is what most tests expect.
Running totals in job tests#
Running totals are a favourite in tests and interviews because they check whether you really understand absolute references. A typical task: "add a cumulative sales column and say in which month the target was reached". See our list of Excel interview questions for others like it, and the Excel skills for admin jobs for the everyday reports where they appear. If the task asks for a weighted figure rather than a cumulative one, the weighted average guide and the SUMPRODUCT guide cover that.
Practise it#
Try the live exercise on this page to finish the running total. Then practise absolute references in a live spreadsheet to master the dollar sign, and use the SUM and AutoSum lesson to get quicker at writing and filling SUM formulas. The SUM function guide has the full reference.
Try it yourself
This is a live spreadsheet. Type the formula in the green cell and press Enter.
Column C shows a running total of Sales. In C7, finish it: use SUM with the start locked at $B$2 and the end at B7.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Running total | |||
| 2 | ||||
| 3 | ||||
| 4 | ||||
| 5 | ||||
| 6 | ||||
| 7 | ||||
| 8 | ||||
| 9 | ||||
| 10 |
Tip: Copy the pattern from C6: the range starts at $B$2 and ends on the current row.
Practise this in a lesson
Frequently asked questions
What is a running total in Excel?
A running total, also called a cumulative sum, shows the total so far on each row. Each row adds its own value to everything above it, so the last row equals the grand total.
Why does my running total formula give the same number on every row?
Both ends of the range are locked, as in =SUM($B$2:$B$2). Only the start should have dollar signs: =SUM($B$2:B2).
Why does my running total show #VALUE!?
You probably used =C1+B2 in the first row, and C1 holds the header text. In the first row, use =B2 or =SUM($B$2:B2) instead.
How do I make a running total that restarts for each category?
Use =SUMIF($A$2:A2,A2,$B$2:B2). It adds only the rows above (and including) the current row whose category matches the current row.
Does a running total work in an Excel table?
Yes. The formula =SUM($B$2:B2) works in a table column. Check the last rows after adding new data, because tables fill formulas down automatically and you want each row to keep its own moving end.