Quite often we want to see a running total or running balance column for our data. There are a few ways we can do this, the technique differs depending on whether your data is in an Excel Table or not.
Watch the Video
Download the Workbook
Enter your email address below to download the sample workbook.
Let’s take the table below; say we want to add a running total to column G.
Excel Running Total Formula
Use a simple = + calculation in column G that goes as follows:
In cell G3 =E3
In cell G4 =G3+E4
In cell G5 =G4+E5
And so on....
You can Auto Fill from G4 to the bottom as your formula remains the same from this point on.
The problem with this approach is that you can’t add or delete rows without breaking the formulas.
The easy way to fix this is to enter the formula again and copy it down. But since we’re all busy, and if you’re constantly adding to or changing your table, option 2 is a more efficient solution.
Option 2 (preferred non-tables):
Use a SUM formula with a combination of absolute and relative references.
Putting our running balance SUM formula in column I it would read:
Row 3 =SUM($E$3:E3)
Notice the combination of both absolute and relative references in the SUM range.
Now when we copy the formula down the column the relative reference will automatically increase by one row.
Row 4 =SUM($E$3:E4)
Row 5 =SUM($E$3:E5)....and so on.
Benefits of this approach:
1) The formula on the first row isn’t different to the rest, so you won’t make a mistake and copy the wrong formula into new cells.
2) When you insert or delete rows the formula automatically updates itself, and you only have to copy the formula from the row above/below into the new cells.
Option 3 (for tables):
Click here for the tutorial on running totals for Excel Tables, or watch the video at the top of the page.