May
27

Excel Cumulative Sum - easy way to calculate running total

This short tutorial shows how a usual Excel Sum formula with a clever use of absolute and relative cell references can quickly calculate a running total in your worksheet.

A running total, or cumulative sum, is the summation of numbers that is updated every time a new number is added to the sequence.

This technique is very common in everyday use, for example to calculate the current score in games, show year-to-date or month-to-date sales, or compute your bank balance after each withdrawal and deposit. The following examples show the fastest way to calculate running total in Excel and plot a cumulative graph.

How to calculate running total (cumulative sum) in Excel

To calculate a running total in Excel, you can use the SUM function combined with a clever use of absolute and relative cells references.

For example, to calculate the cumulative sum for numbers in column B beginning in cell B2, enter the following formula in C2 and then copy it down to other cells:

=SUM($B$2:B2)

In your running total formula, the first reference should always be an absolute reference with the $ sign ($B$2). Because an absolute reference never changes no matter where the formula moves, it will always refer back to B2. The second reference without the $ sign (B2) is relative and it adjusts based on the relative position of the cell where the formula is copied. For more information about Excel cell references, please see Why use dollar sign ($) in Excel formulas.

So, when our Sum formula is copied to B3, it becomes SUM($B$2:B3), and returns the total of values in cells B2 to B3.  In cell B4, the formula turns into SUM($B$2:B$), and totals numbers in cells B2 to B4, and so on:
Excel Cumulative Sum formula

In a similar manner, you can use the Excel SUM function to find the cumulative sum for your bank balance. For this, enter deposits as positive numbers, and withdrawals as negative numbers in some column (column C in this example). And then, to show the running total, enter the following  formula in column D:

=SUM($B$C:C2)
Calculating running total for the bank balance

Strictly speaking, the above screenshot shows not exactly a cumulative sum, which implies summation, but some sort of "running total and running difference"  Anyway, who cares about the right word if you've got the desired result, right? :)

At first sight, our Excel Cumulative Sum formula looks perfect, but it does have one significant drawback. When you copy the formula down a column, you will notice that the cumulative totals in the rows below the last cell with a value in column C all show the same number:
The cumulative totals in the rows below the last cell with a value in the dependent column all show the same number.

To fix this, we can improve our running total formula a bit further by embedding it in the IF function:

=IF(C2="","",SUM($C$2:C2))

The formula instructs Excel to do the following: if cell B2 is blank, then return an empty string (blank cell), otherwise apply the cumulative total formula.

Now, you can copy the formula to as many cells as you want, and the formula cells will look empty until you enter a number in the corresponding row in column C. As soon as you do this, the calculated cumulative sum will appear next to each amount:
An improved Excel cumulative sum formula

How to make a cumulative graph in Excel

As soon as you've calculated the running total using the Sum formula, making a cumulative chart in Excel is a matter of minutes.

  1. Select your data, including the Cumulative Sum column, and create a 2-D clustered column chart by clicking the corresponding button on the Insert tab, in the Charts group:
    Create a 2-D clustered column chart.
  2. In the newly created chart, click the Cumulative Sum data series (orange bars in this example), and right click to select Change Series Chart Type... from the context menu.
    Select Change Series Chart Type... from the context menu.
  3. If you are using a recent version of Excel 2013 or Excel 2016, select the Combo chart type, and click on the first icon (Clustered Column - Line) at the top of Change Chart Type dialog:
    In Excel 2013 or Excel 2016, select the Combo chart type.

    Or, you can highlight the Custom Combination icon, and choose the line type you want for the Cumulative Sum data series (Line with Markers in this example):

    Choose the line type you want for the Cumulative Sum data series.

    In Excel 2010 and earlier, simply select the desired line type for the Cumulative Sum series, which you've selected on the previous step:

    In Excel 2010 and earlier, select the desired line type for the Cumulative Sum series.

  4. Click OK, and evaluate your Excel cumulative chart:
    Excel cumulative chart
  5. Optionally, you can right-click the Cumulative Sum line in the chart, and select Add Data Labels from the context menu:
    Add data labels to the Cumulative Sum line.

As the result, your Excel cumulative graph will look similar to this:
Excel cumulative graph with data labels

To embellish your Excel cumulative chart further, you can customize the chart and axes titles, modify the chart legend, choose other chart style and colors, etc. For the detailed instructions, please see our Excel charts tutorial.

This is how you do a running total in Excel. If you are curious to learn a few more Sum formulas, check out the following examples:

I thank you for reading and hope to see you again soon!

25 Responses to "Excel Cumulative Sum - easy way to calculate running total"

  1. saurabh says:

    Thank u so much for great information
    Keep it up

    Regards
    Saurabh

  2. Samantha says:

    I am not skilled in excel whatsoever. Sorry. What I'm needing is to make Column A + Column B + Column C = Column D, and make it average out the sums from Column D in Column E

  3. Uche Uche says:

    I have been having trouble understanding exactly how to work out this cumulative sum until I met it in your web. Its all clear to me now. Thanks.

  4. Christy says:

    How do I calculate sales for many different products and total per state- soo all sales for each state not broken out by product

  5. Jamie says:

    Is there a way to run the IF formula on multiple cells? Example you have =IF(C2="","",SUM($C$2:C2)) to return a blank cell unless something is in C2. What if I have a column c2, d2 and e2, and want it to return a value if only 1 of those columns is filled it?

  6. Peter Bungay says:

    I have an spread sheet in excel 10 with 23 sheets and a summary sheet.

    I have a running balance set up on the 23 sheets using tables referencing system called structured references. The formula in the balance column is =SUM(INDEX([Debit],1):[(@Debit])-SUM(INDEX([Credit],1):[2Credit]).
    This produces a running balance for each sheet.

    I can get data into the summary sheet by using =[Sheet Name]![Cell]but that only references the first cell.

    What I need to know is how to get the relevant cell in the summary sheet to update as the data in the corresponding cell is changed with each input entry.

    The idea is, I start with an opening balance, enter debits or credits, a new balance is created and updated in the summary sheet. Next time I open the spread sheet I simply enter new data and the new balance is updated in the summary sheet each time I make changes.

    I trust you are able to help and I await you advice in due course.
    Peter.

  7. Grace says:

    Hi!

    Im having hard time formulating the beginning balance on a selected period of a cash flow. For example, i would like to filter only the transaction of yesterday or the other day, but i dont know how to formula the beginning balance of that period.

    Do you have a preferred excel formula to be used on this problem?

    Thank you.

  8. Taslima says:

    Good tips. I love it.

  9. Tejinder says:

    I have 3 coloumn , abc,a coumn heading date,b col party name,c column amt,d columns balance d1 only want to see i i entetd anydate in a columns,add amt im d1 cell automatically how can i do this,=if(blank(),sum(c comumn row 1,2,3,4,..... in only d 1 cell only when we enterd date in columns a1 to a12 for exanple.we guide me

  10. Padraig says:

    Hi,

    I have 5 worksheets, 2 with weekly wages for 2016 & 2017 and two with weekly revenue for 2016 & 2017 and I have setup another worksheet which gives me what revenue/wages was for both years by putting in a week number in a cell (done by hlookup )
    What I need is a cumulative figure for the year depending on what week we are in. ie if I put in wk 9 in my cell, that It will cumulative the first 9 weeks of the year for the wages/revenue for 2016/2017 and not the total for all the weeks are have been entered.

    Kind regards,

    Padraig

  11. Rajen says:

    Thank you so much for the explanation. That was very clear and helpful.

    Regards
    Rajen Appadoo

  12. Carlos says:

    Thank you so much for this information. I have been wanting to learn how to create a cumulative graph for quite some time now, and these instructions are wonderful. I do, however, have a question:

    I successfully created a cumulative graph based on hours entered in time sheets by a particular role of employee. My problem is that the dates for time entry are daily, and the project has been running for a long time. This makes the graph impossible to read. Is there a way to change the time increment to monthly instead of daily? This would help tremendously!

  13. owoicho says:

    hi guys! how do I run a cumulative sum of a previous cell value with the current cell value is a different cell? I.e previous value of A1 current value of A1 in B1

  14. Name says:

    How to display running total in a cell. For example: you want cell E1 to display the newest ever chaning running total in above diagram $170 from column D.

  15. srinivas says:

    How to check particular row cumulative result in a column using cumulative addition

  16. A says:

    How do we handle when instead of sales in units ? for example the weekly budget is 130 and weekly sales for week 1 -133 week 2- 133, week 3-133 week 4- 134 week 5 -135 so whats happening here is in week 1 it will be +4 and should be +4 for week 2 and 3 because in actuality we are not selling more units. Further when we come to week 4 the increase is just 1 unit in sales so do we add 4 from prior week and 1 from current week to show 5 units or will it be 4+5 units in cummulative ? This is a case of addition but when there is a deficit vs the budget we have to add the negative units from prior period to current period so either it can net off against the surplus or add to more deficit.

  17. Charles says:

    I need a formula that sums a column up to $250 then stops the sum and starts listing the amount in the cell.

    This formula got me started =IF(SUM($E$4:E11)>$F$1, ,SUM($E$4:E11)-250)

  18. Elance says:

    Hi,
    I need to calculate the rate of an electricity bill but the tariff is accumulative so if I have 5,000 unit the first 1,000 is at certain price the the second 1,000 at a different price and so on. can I make a formula in Excel to segregate the total units number and calculate each part at a different rate.

  19. Airston Tumlos says:

    i need a formula to get ending balance of today then that ending balance will be the beginning balance the next day. thank you.

  20. Brad says:

    This was an elegant and efficient solution, thanks

  21. MASHOK says:

    my sheet column head is description (A:A), debit(B:B), credit(C:C), Balance (D:D). In description if i write "Deposit" then the value will put on debit column, and if write "Credited", the value will put on credit column. But how to ensure if i write "credit" in A3, THE VALUE 500 will only can be post in Credit column C3, B3 should not accept any input if the description is 'CREDITED'. OR if the description is "deposit" the value cannot be post in Credit clumn C3. How to apply, pls.

Post a comment



Unfortunately, due to the volume of comments received we cannot guarantee that we will be able to give you a timely response. When posting a question, please be very clear and concise. We thank you for understanding!
Excel add-ins and Outlook tools - Ablebits.com
Ultimate Suite for Excel Professionals
 
 
60+ professional tools for Excel 2016-2007 to do your daily work
Incredible product, even better tech support…AbleBits totally delivers!
Deborah Bryant
Anyone who works with Excel is sure to find their work made easier
Jackie Lee
The best spent money on software I've ever spent!
Patrick Raugh
Ablebits is a fantastic product - easy to use and so efficient.
Debra Celmer
Excel is at its best now
Annie C.
I don't know how to thank you enough for your Excel add-ins
Jennifer Morningstar
Anybody who experiences it, is bound to love it!
Kumar Nepa
AbleBits suite has really helped me when I was in a crunch!
Nelda Fink
I have enjoyed every bit of it and time am using it
Christian Onyekachi Nwosu
It's the best $100 we've ever spent!
Mike Cavanagh
I love the program, and I can't imagine using Excel without it!
Robert Madsen
One word… WOW!
Dave Brown
Love the products!
David Johnston
It is like having an expert at my shoulder helping me…
Linda Shakespeare
Your software really helps make my job easier
Jeannie C.
Thanks for a terrific product that is worth every single cent!
Dianne Young
I love your product
Brad Gibson
Awesome!!!
Sheila Blanchard