Create a Running Total in an Excel Column

If you’re using a pivot table, there are built in features that lets you show a running total, or a percent running total.

runningtotalyears03

If you’re using worksheet formulas, instead of a pivot table, there’s nothing built in that will automatically create a running total for you. Fortunately, with a simple SUM formula, you can calculate the running total in each row, to see how your bank account is doing.

Use the SUM Function

In this example, there are withdrawal and deposit amounts in cells C2:C6. We could use the SUM function to total the amounts in column C, and that would give us the current total.

sumcolumn

Instead, we’ll use the SUM function in each row, to calculate the running total. We’ll be able to see the total after each withdrawal and deposit.

The formula in cell D2 is: =SUM(C$2:C2)

sumrunningtotal01

That $ sign locks the reference to row 2, at the start of the sum, so it doesn’t change when the formula is copied down to cell D6.

The ending cell – C2 – is a relative reference, so the ending point changes for each row. For example, in cell D6, the formula shows C6 as the ending cell for the SUM formula.

sumrunningtotal02

Watch the Video

To see the steps for creating a running total on a worksheet, please watch this short video.

YouTube: How to Show a Running Total on Excel Worksheet

_______________________

You may also like...

10 Responses

  1. BHUSHAN says:

    i am fan of your professional mails. its very helpful for all of us.

    i am also want Tobe excel expert. let me know how it will be possible

    Thanx

    Bhushan

  2. Toan says:

    It is simple but intersting. Thanks!

  3. SAM says:

    Thank you for your simplicity in explaining the excel worksheet. I am not an accountant nor do I wish to be one. My family has accumulated a lot of medical bills and I need a way of keeping track of the deductible. Thank you so very much. There are other videos other there, but they assume you are familiar with all the spread sheet jargon and can follow. Again, thank you so very much!!!!!

  4. Mark Kamoski says:

    Does not work with Excel 2013. When putting the formula into the 1st cell, as shown in the example, Excel complains that there is a circular reference. Then, after entering the formula as shown above, Excel just fills all columns with a zero. Not working.

  5. duncan bradford says:

    I want to create a running total, as you did, for an account, so it keeps an automatic running total in a column that stops at the last entry, and automatically continues with each entry that is made. When I do what you have done, and autofill the column so it is always ready, it repeats its last entry in every cell beneath [to where I stopped the autofill].
    I am used to Appleworks spreadsheets, where, when you fill down, [command + D], if the next row is blank the running total only runs as far down as your last entry, and then only fills the cell corresponding to the next entry when it is made.
    It seems that with Excel, to get this clean result one has to drag the fill one cell further down each time to avoid having the ‘tail’ of identical entries. Is this true? Or how can I get Excel to only respond to actual entries. It seems a retrograde and counter-intuitive step after 20 years of using something simpler!
    I appreciate that Excel is far more capable for exotic tasks, but for everyday use it seems about as stroppy as ‘Word’.

    • David Plumley says:

      That is what I am trying to do! For my company’s payroll, we employees send in a seven column spreadsheet every month. Only column C has dollar amounts and a running total as you describe is the only calculation on the worksheet. That is, it would be best. I usually hit Autosum a few times as I go along instead. The running total doesn’t move down as I add entries, usually 50 to 70 rows on the whole sheet. May switch to Apple Numbers.

  6. Jodi says:

    Thank you! This was extremely helpful (both the written explanation and video). It was exactly what I was looking for and took me less than 5 minutes to set up. Much appreciated.

  7. christine says:

    Why do the values in my column of running total have “$” in front of them? They are not an amount of money and I don’t want it there. I used =SUM(D$6:D6)). I hope you can assist

Leave a Reply to SAM Cancel reply

Your email address will not be published. Required fields are marked *