Calculate a running total of a column of cells in Excel
If you have a column of numbers and you want to calculate a running total of the numbers alongside, you can use the SUM() formula combined with a clever use of absolute and relative references.
Imagine your spreadsheet has values in cells A2 through to B20. You want to put a running total of sales (column B) in column C:
As you can see, column C shows the cumulative running total of the sales in column B.
To reproduce this you would do the following:
- In cell C2, enter the following formula:
- This will put the value of B2 into C2. Remember that $C$2 is an absolute reference. This means that anywhere you copy this formula, it will always refer back to C2.
- Then, copy this formula and paste it into cell C3. The formula should now look like this:
- This will put the sum of A2 and A3 into B3.
- Note that, as promised, $B$2 hasn't changed even though you have copied the cell from one place to another.
- On the other hand, B2 was a relative reference in the previous cell, so it changed from B2 to B3 when you copied and pasted the formula from C2 to C3.
- Once you've verified that this works as described, you can then copy the formula into each cell from C2 to C20. The final cell, C20, will contain the total of all the numbers in cells B2:B20.
Note that there are some scenarios where a formula like this is more than you need. Check out this comment to see another way to do it that allows to calculate a running balance based on two columns of numbers.
Finally, a common requirement is to calculate a running total based on data between two dates. Click the link to see now to do this.