by 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 a sequence of partial sums of a given data set. It is used to show the summation of data as it grows with time (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:B4), and totals numbers in cells B2 to B4, and so on:
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($C$2:C2)
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:
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 C2 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:
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.
- 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:
- 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.
- 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:
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):
In Excel 2010 and earlier, simply select the desired line type for the Cumulative Sum series, which you've selected on the previous step:
- Click OK, and evaluate your Excel cumulative chart:
- Optionally, you can right-click the Cumulative Sum line in the chart, and select Add Data Labels from the context menu:
As the result, your Excel cumulative graph will look similar to this:
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 useful formulas, check out the below examples. I thank you for reading and hope to see you again soon!
101 comments
thank you so much! this helped a ton!
thank's for your help but there is another issue when I tried to select the cell and didn't go well, but it worked eventually
Hi,
Simple solution for everything. please swtichoff your system and sleep.
I have my weekly expenses totalled and each week has different items listed with a final total for the week. what I want to do is over the year I want a total of each weeks totals to give me a total for the year. e.g. I want a total for say P15+P30+P45 etc. I have a basic knowledge of excel but this has beaten me. Please Help.
Thanks very much for tutorials, I like to know if there is a way to make just a cell behave as a normal calculator.
i.e this cell should be capable of summing figures that appears in another cell, while keeping last cumulative figure visible.
This other cell will be the key-in cell or active cell.
Example:
Cell 1: =2*5, Answer appears in Cell 2,
Cell 1: =3*6.8, Answer is added to the previous value resulting from (2*5) and still appears in cell 2.
Kindly share a video if this is possible.
Thank you.
Veryful, thank you
Thanks you saved me a ton of time with the cumulative sum formula :)
Let's say I want cell B2 to be a cummulative total of minutes that only accumulates if cell A2 matches with the same name I select from a dropdown list in cell D2. Can this be done? Example: Cell A2(A constant) contains T Young, then Cell D2 from a drop down list has T Young selected. Then Cell E2 has a number entered that I want added only to the running total of minutes in Cell B2, only if cells A2 and D2(Dropdown List) match.
Wonderful, I'm grateful
Hi
I am trying to do a running total of collum M in Collum P. I have managed to do this using you help. Now i need to take away collum O and keep the running total going in Collum P
PLEASE HELP its doing my head in.
What about a formula where a number is entered in a cell and other cells subtract the new increment from their previous total? Not the total in the 1st cell, but the new number added to it. I'm trying to keep it from becoming a list, as with expenses.
I am trying to set up a running total of total hours worked and total dollars in labor. So that each week I will change the weekly numbers but need it to be added as I go from week to week in another column. What formula will work for this? I am not having any luck. All the formulas I am finding want the week to week numbers to be kept and then added as a running total. I need the running number to keep adding as the weekly numbers change.
if $ sign is used, what happens if a new column is inserted to the left.
Absolute cell references (with the $ sign) do change when you add or remove rows and/or columns to reflect a new position of the referenced cell, so your reference will change accordingly.
Hi There,
I am doing a rota at work for around 50 staff. I want to know how do I set up a total figure for individuals holidays as they are booked so a running total of what they have left for the year is displayed.
I insert the letter H on the rota if a holiday is booked.
Hope you can help
Thank you
Jethro
I am a person looking for a probability function in a distribution where one has to make Cumulative functions as well as probability ranges!
Hi there,
My request is complex (well, I think so). I am looking to do a running total of the number of equipment used in a period of dates.
If I have in total: 100 bikes
Shop A with 50 bikes
Shop B with 30 bikes
Shop C with 20 bikes
In the week of March 1-6, Shop A will use 30 bikes, B will use 10 bikes and C will use 5 bikes, is there a way of determining how many bikes are being used in March 1-6 across all three sites?
This will also be able to produce an error/warning if I may be using more than 100 bikes.
I would imagine that I have start and end dates in a particular format so that I can use an sumif() function to calculate the sum of bikes in a particular week. Does that sound right?
Happy to discuss through private email.
Thanks
I have columns with different items and then prices and then a total what I want is for it to automatically sum up the total of the previous square with the new amount put in. For example
B C D E
ITEM, DESCRIPTION, AMOUNT, TOTAL
Food, Bagel , $3.00, =$3.00
Drink, Tea ,$ 2.00, =$5.00 etc...
But I want it to sum it up automatically, right now I am having to do it individually =SUM(E1,D2) can anyone help?
Hello,
If I understand your task correctly, please try to do the following:
1. Enter the following formula in cell E2;
=SUM($D$2:D2)
2. Just select the cell where you've entered the formula and drag the fill handle (a small square at the lower right-hand corner of the selected cell) down.
Hope this will help.
how to sum multiple cell in sum if formula
I have been adding expenses in a column in Excel using the Summation function showing at the BOTTOM of a column of data.
Whenever I have to add a new expense, I simply add a row and then enter the datum.
However, upon returning to the expensive file/column recently, I noticed that somewhere during the middle of the year the column started adding a cumulative data amount, over-writing the entry I added for the individual expense.
I tried clearing "format" to see if it would return the original data entry. I can see that the cell below (e.g. C24 was originally C24-C23. Yikes! How can I go back to original summary cell at the bottom of my column, and undo the cumulative summation/overwrite that somehow is at play? Thank you.
Hello,
For me to understand the problem better, please send me a small sample workbook with your source data and the result you expect to get to support@ablebits.com. Please don't worry if you have confidential information there, we never disclose the data we get from our customers and delete it as soon as the problem is resolved.
Please also don't forget to include the link to this comment into your email.
I'll look into your task and try to help.
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.
Hello,
If I understand your task correctly, please create a custom Data Validation rule for columns B and C using these formulas:
column B
=A1="Deposit"
column C
=A1="Credited"
We have an article on our blog that describes how to use Data Validation in Excel. Please have a look at it.
Hope you’ll find it helpful.