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
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.
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.
Hi, Mynda.
Before Excel, I used Microsoft’s Multiplan, which used relative references, now Excel calls them “R1C1 reference style” in the formula options. These allow a very simple solution. First, define a variable, say “CellAbove”, as =R[-1]C, which means one row above this cell and in the same column. (You can switch to R1C1 reference style, define the variable, switch back to A1 reference style, and it will still work.)
This works especially well in Excel (structured reference) tables. If the total column is called “Total”, then in the running total column, use this formula:
=SUM(CellAbove,@Total)
Nice tip, Eric! Thanks for sharing 🙂
Thanks for the downloads, I look forward to learning from them.
I have an immediate problem. I created a simple spreadsheet for recording donations to our annual non-profit fund raising drive. I put in a column to a running total. Rather than having to scroll down to read the last entry, is there a way I can put just one cell at the top (maybe over to the right) to reflect the running balance. This would save scrolling down if someone just wants to check the latest total.
Thanks,
Therese
Hi Therese,
This tutorial explains how to write a formula that returns the last value in a column.
Mynda
Hi
Can you tell me please what would the formula be if instead of adding I3 to E4 giving the total of 5440 in I5 you wanted to add I3 to C4+D4+E4 making a total of 5834 and continue to add the SUM of columns C+D+E.
Hi Alister,
It’s difficult to visualise your scenario, can you please post your question on our Excel forum and upload a sample file so we can see what you’re trying to accomplish.
Mynda
Can you provide a formula for a running cumulative balance that would have as its input either a negative number (e.g., a withdrawal amount) or a positive number (e.g., a deposit amount)? each row would only have one input (negative or positive number).
Thank you.
Hi Jim,
Sure, you can modify the formula like so:
Assuming column F contains your other values.
Mynda
My situation is different, for running totals in that it needs to be continuous. Say Jan 2015 to Dec 2015, 12 month period for the total. As soon as one would enter a figure for Jan 2016, the Jan 2015 amount would drop and the new running total would be Feb 2015 to Jan 2016. Thank you bunches Mynda & Phil….Mort in Dallas, TX
Hi Morton,
Please upload a sample file on Help Desk (create a new ticket) , or by mail.
Cheers,
Catalin
Thanks Catalin – this may(?) benefit some. Let me see how to upload a simple lil file. At the present time, I’m simply trying to get the data into a worksheet via a UserForm, then worry about data manipulation. See? When I get to data manipulation, will post, OK? Best Sunday wishes to all, Mort in Dallas, TX
Ok, I’ll wait then 🙂
Catalin
Pretty new to Excel and I cannot figure out why the SUM of my #s in a column does not go in the SUM column.
Hi Pat,
That sounds odd. Are you able to show us the Excel workbook so we can take a look. You can send it to us via our Help Desk.
Cheers,
Mynda
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.
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.
I’ve been doing it the old way and hate having to update the formulas when I insert/delete rows. Thanks!
🙂 You’re welcome, DS.
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
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.
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
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.
excellent help thank you so much
You’re welcome, Vicki-Lee.