How to calculate a running total in Excel
A running total, or cumulative sum, shows the total so far on each row. The most reliable way to calculate it in Excel is an expanding range: =SUM($B$2:B2) in the first row, filled down. The dollar signs lock the start of the range, and the end moves down one row at a time.
This guide shows the expanding range, the shorter "previous total plus this value" formula, and a running total per category. It builds on how cell references work, which you can practice in the lessons at the end.
1. The expanding SUM range
In C2 type =SUM($B$2:B2) and fill it down. In C3 the formula becomes =SUM($B$2:B3), in C4 it becomes =SUM($B$2:B4), and so on. The first cell is absolute, which means locked, and the second is relative, which means it moves with the row.
Example: Running total in the third row
| A | B | C | |
|---|---|---|---|
| 1 | Day | Sales | Running total |
| 2 | 1 | 10 | 10 |
| 3 | 2 | 20 | 30 |
| 4 | 3 | 5 | 35 |
| 5 | 4 | 15 | |
| 6 | 5 | 30 |
The range grows to B2:B4, and 10 + 20 + 5 = 35.
2. Previous total plus this value
A shorter way is to add the value of this row to the total above it: =C3+B4. In the first row there is no total above it, so type =B2 there. This is easy to read, but if you delete one row in the middle, the chain can break, so the expanding range is safer in a sheet that changes.
Example: Add to the previous total
| A | B | C | |
|---|---|---|---|
| 1 | Day | Sales | Running total |
| 2 | 1 | 10 | 10 |
| 3 | 2 | 20 | 30 |
| 4 | 3 | 5 | 35 |
| 5 | 4 | 15 | |
| 6 | 5 | 30 |
The total so far in C3 is 30, and adding today's 5 gives 35.
3. A running total for each category
When the list mixes categories, use SUMIF with an expanding range, so each row adds only the values of its own category found so far. Lock the top of both ranges and let the bottom move.
Example: Running total for North
| A | B | C | |
|---|---|---|---|
| 1 | Region | Sales | Running total |
| 2 | North | 10 | |
| 3 | South | 20 | |
| 4 | North | 5 | |
| 5 | North | 15 | 30 |
| 6 | South | 30 |
Up to row 5, North appears in rows 2, 4 and 5 with 10, 5 and 15, which add up to 30.
4. Make it grow with the data
If you turn the data into an Excel table with Ctrl+T, a formula typed in the first row of a new column is filled down automatically, and new rows get the same formula. In Microsoft 365 you can also compute the whole column with one dynamic array formula, as covered in the dynamic arrays lesson.
Common mistakes
Forgetting the dollar signs. =SUM(B2:B2) filled down becomes =SUM(B3:B3), which is not a running total.
Fix: Lock the start of the range: =SUM($B$2:B2).
Typing the first row like the others. =C1+B2 adds the header text of C1 and returns #VALUE!.
Fix: In the first row, use =B2 or the expanding SUM, which handles it correctly.
Sorting after calculating. Sorting rows that hold a previous-total formula can change what each formula points at.
Fix: Sort first and then fill the formula, or use the expanding SUM, which survives sorting better.
Frequently asked questions
How do I make a running total that restarts each month?
Use the SUMIF version with the month as the category, or add a second condition with SUMIFS.
How do I show a running total in a pivot table?
Right-click a value, choose Show Values As, then Running Total In, and pick the field to run along.
Can I show it as a chart?
Yes. A line chart of the running total column shows how a figure builds up. See the guide on making a graph.
Related functions
- SUMSUM adds up numbers, cells and whole ranges in one formula.
- SUMIFSUMIF adds up the values that meet one condition, such as all sales of one region or all amounts above 100.
- SUMIFSSUMIFS adds up values that meet several conditions at once, such as paid orders from one region.
- SEQUENCESEQUENCE creates a list or grid of consecutive numbers that spills into neighbouring cells.
More on running totals
The guide gives the expanding range and the previous-total formula. This piece adds what to do when the rows are not in order, how to show only the latest running total, and how to build a running total with a single formula.
When the rows are not in date order
A running total adds the rows above it, so it means something only when the rows are in the right order. If the dates are scrambled, sort the table by date first. A date-based formula does not depend on the order: add all the sales up to and including the date of this row.
=SUMIFS($B$2:$B$100,$A$2:$A$100,"<="&A2)Show only the latest total
A report often needs just the final figure. That is the same as the plain =SUM(B2:B100), so a running total column should agree with it in its last row. Use this as a check: if the last running total differs from the sum of the column, a formula in the middle was overwritten or a row was inserted outside the range.
One formula for the whole column
In Microsoft 365, an expanding range can be written once for the whole column with a dynamic array formula, using SEQUENCE to build the row numbers and a lookup to take the first n values. It is harder to read, so use it only when you need a column that never has gaps or broken formulas. Most people are better served by the simple expanding SUM in a table, which fills itself down.
To chart the result, select the date column and the running total and insert a line chart, as in [the guide on making a graph](/guides/make-a-graph-in-excel). For the locked and moving parts of the range, see [how cell references work](/guides/cell-references).