Quite often we want to see a running total or running balance column for our data.  There’s a couple of ways we can do this, both are dead easy.

Let’s take the table below; say we want to add a running total to column G.

Excel running balance formula

Running Total option 1:

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.

Excel running total formula

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.

Running Total Option 2 (preferred):

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.

Excel running total with absolute and relative references

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.

Download the workbook to practice here.

Share the knowlege with your friends and colleagues. Click the icons below for Twitter, Facebook, Stumbleupon and many more.

Share This

Please share this or leave a comment and I'll make sure you get a personal reply.

Leave a Comment

Current day month ye@r *

{ 10 comments… read them below or add one }

Cherie Daniel May 16, 2013 at 2:21 am

I want to make a spreadsheet that works like a checkbook. A column for date, one for description, one for add, one for subt, and one for balance. The balance column should pick up the balance brought forward and continue on from that point, adding or subtracting according to the column the figure is placed in. That way I will have a running balance of the check book that I know is accurate. It’s too easy to make a mistake when entered manually. Can you make one for me. I don’t have any other needs for excell so don’t want to take an entire course. Thanks for your feedback.

Reply

Mynda Treacy May 16, 2013 at 8:04 pm

Hi Cherie,

From what you’ve described you don’t need me to create a workbook for you. Just put your column headers in, use the running total formula in the above tutorial and start entering your data. Job done.

Kind regards,

Mynda.

Reply

DS August 20, 2012 at 1:28 pm

I’ve been doing it the old way and hate having to update the formulas when I insert/delete rows. Thanks!

Reply

Mynda Treacy August 20, 2012 at 6:49 pm

:) You’re welcome, DS.

Reply

Jeff June 28, 2012 at 6:21 am

This great, how would I do the same thing for a Pivot Table that contains a column of numbers that I want to have a running total of? By creating a measure…not sure of the calculation though

Reply

Mynda Treacy June 29, 2012 at 10:53 pm

Hi Jeff,

If you have Excel 2010 you can go into your Value Field Settings and on the Show Values As tab choose ‘Running Total In’ from the list.

Kind regards,

Mynda.

Reply

Steve B May 20, 2012 at 7:39 am

I created a checkbook in excel 2010 and i was able to figure out the running balance which basically adds, subtracts accordingly and it gives me a running balance. My question is, is there a way to display that running balance (considering it keeps changing rows with each new entry) in another cell? I am trying to create a cell near the top of the worksheet that also displays my current balance and well as within the worksheet. Any thoughts? Thanks you

Reply

Mynda Treacy May 20, 2012 at 9:46 pm

Hi Steve,

Let’s say your running balance is in column C and you want to find the last value in the column. You could use this formula in cell A1, or any near the top:

=INDEX(C1:C1000,MATCH(9.99999999999999E+307,C1:C100))

I hope that’s what you were after. If not let me know.

Kind regards,

Mynda.

Reply

Vicki-Lee Harry January 19, 2012 at 11:03 am

excellent help thank you so much

Reply

Mynda Treacy January 19, 2012 at 9:02 pm

You’re welcome, Vicki-Lee.

Reply

Previous post:

Next post: