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!
49 comments
Hi there...I know this is an old post but putting this out there.
Is there a way to have a running total in one column, let's say column D, with "debits" in column B and "credits" in column C?
Is there a formula I can plug into Excel to add from Column C and subtract items from Column B for a running total in Column D?
Thanks
Hello Carolyn!
If I understand your task correctly, the following formula should work for you:
=SUM($C$2:C2) - SUM($B$2:B2)
Hey,Anyone know to find overall total from different sheets?
Hi! If I got you right, this guide will help you with your task: 3-D reference in Excel: reference the same cell or range in multiple worksheets.
The formula =SUM($L$7:L7) is working for me in an area formatted as a table; however, whenever I add a new row, the new row will have the correct formula, but what had previously been the final cell in that column will change to include the new row. Each subsequent adding of a row will screw up what had previously been the final row.
The formulas in three cells in a column will go from:
=SUM($L$7:L7)
=SUM($L$7:L8)
=SUM($L$7:L9)
To (when adding a forth row)
=SUM($L$7:L7)
=SUM($L$7:L8)
=SUM($L$7:L10)
=SUM($L$7:L10)
Adding a fifth row will turn to
=SUM($L$7:L7)
=SUM($L$7:L8)
=SUM($L$7:L11)
=SUM($L$7:L11)
=SUM($L$7:L11)
Is there a fix?
Hello Kris!
If you are working with an ever-changing dataset, you may want to make the range dynamic. This means that it automatically expands to accommodate newly added records. You can find the examples and detailed instructions here: How to create and use dynamic named range in Excel.
Editing a spreadsheet that has a cumulative sum column
I have a spreadsheet into which I have entered a series of transactions, and alongside each transaction amount I have a formula which gives the running total.
Lets say, at some point in the future, I notice that the order of the transactions is incorrect and I need to move one of them up to its correct position. So I insert a blank row at the appropriate point and I can then copy the transaction that is in the wrong place into this blank row, and then delete the original row. This works fine.
However, I had expected to be able to MOVE the transaction into the blank row by dragging, rather than COPY it and delete the original. When I MOVE the transaction, the relative reference does not update.
For example if row 4 has a cumulative total formula of =SUM($B$2:B4), and row 7 has a cumulative total formula of =SUM($B$2:B7), and I want to move the current row 7 transaction into the position currently occupied by the row 4 transaction, if I use "copy and delete", the formula copied from what was row 7 has its formula correctly changed to =SUM($B$2:B4) to reflect its new position.
If, instead I MOVE the row, then I end up with the formula =SUM($B$2:B8) in row 4. The relative reference has not been updated. (The reason it is now 8 not 7 is because it was updated when I added the new row at 4.)
Obviously it is no great hardship having to use "copy and delete", but I have to do this row rearrangement quite often, and I find myself using the MOVE method by mistake and then wondering why my spreadsheet is wrong.
Does anyone have any suggestions?
Hi! Maybe this article will be helpful: How to copy formula in Excel with or without changing references.
Great information here.
This is probably a simple one for the illuminati:
Suppose column C tracks profit gain and column D tracks profit losses. What would be the formula for column E tracking the ongoing balance?
It seems like it should be =SUM($C$2:C2)-SUM($D$2:D2) but I've apparently missed something.
Hi! If you copy this formula down the column, you will get the running profit total. To get just the profit total, change the cell references in the SUM formula. For example,
=SUM($C$2:C20)-SUM($D$2:D20)
How do I calculate the average of the total month at day 1, day 2, day 3 etc?
Hi!
If I understand your task correctly, you can use the AVERAGEIFS function and specify the desired days as the criteria.
Hi I'm trying to get a running accm. total for revenue over various sheets on an excel file. For each week I get the revenue and the following week I get another total and I want to keep adding them together as I pull new sheets. How would I do that?
for example;
sheet one -- total revenue
sheet two -- total revenue +total revenue of sheet one
sheet three -- total revenue + (sheet two total revenue + sheet one total revenue)
sheet four -- total revenue + (sheet three total revenue + sheet two total revenue + sheet one total revenue)
Hi!
Here is the article that may be helpful to you: 3-D reference in Excel: reference the same cell or range in multiple worksheets.
I want to set up a spreadsheet so that it totals values per week, i.e. on 12/2/22, I can see the sum of the values from 11/26/22-12/2/22. Then next week, I want it to total the sum of the values from 12/3/22-12/9/22 and so on and so forther.
Hi!
Write in cell B1 the formula SUM(A1:A7). Then select the range B1:B7 and drag the fill handle down the column. See for more information: How to copy formula in Excel.
Hi
Is there any function that will do running total values of same date and repeat again when new date starts
Hi!
You can calculate the amount for a certain date using the SUMIF function. When calculating the running total, use it instead of the SUM function.
I hope it’ll be helpful.
A B C D E
1 10 10
2 22 32
3 0 22
4 32 32
5 16 48
6 0 16
7 0 0
COUNTIF(B1:B7;">0")
RESULT = 3 from Column B
Require formula where
Result = 1
Which is determined as follows:
In Column C - Helper column
IF(B1+B2;"=0";0;B1+B3)
RESULT is 1
Formula required to get
RESULT =1
Without the Helper Column C
Thank you.
Hi!
I don’t know how you can get result 1. But the formula should be written like this
IF(B1+B2=0;0;B1+B3)
Read about using IF function in this tutorial.
How would you calculate the cumulative totals if the dates in column A were not in order? Thanks!
1 oct 1998 to today how much will be the total cumulative interest is 18per cent per month cumulativly if amount is 10000
have you ever found an answer for this
Thank you! My question:
I have a complex table. Therefore, I am not aiming at creating additional column with cumulative sum.
Can I then, ask Excel to plot a cumulative sum line chart? i.e. the dataseries entry will specify cummulative figures from a (yet to be cumulatively summed) column?
thanks.
Hi!
A graph in Excel is built according to the data that is written in certain cells.
When I follow your example exactly, it doesn't work for me!
When I have got the formulas in each of the cells, eg in C4 it is =SUM($B$2:B4) it only adds B2and B4, missing out B3.
For cell B5, the formula is =SUM($B$2:B5) but it only adds B2 and B5, missing out B3 and B4.
Am I doing something wrong?
Hello!
The formula =SUM($B$2:B5) sums cells B2 through B5. You can check this using a calculator.
You can learn more about SUM function in Excel in this article on our blog.
Is there a way to pull a specific date based on the cumulative total. As in, when did the total pass $1700? Is there a function to return that date?
I am working on a data set where I am projecting X number of real estate developments per year. Lets say at 6 per year. I want to show in what year that the total passes development 14 is built? (This would be year 3). I cannot figure out the function.
Thanks!
Hello!
Column A contains dates, column B - amounts, column C - cumulative total. The date when the cumulative total exceeded a certain amount can be determined by the formula
=INDEX(A1:A100,MATCH(1700,C1:C100,1)+1)
Hope this is what you need.
Hi Everyone,
I am looking to calculate a maximum cumulative average of at least 60 cells in length from within a larger range of cells. So, if in a range of data (A1:A101), with the cumulative average running from cell A1 to cell A101, how would I find out the section within this range that has the highest cumulative average? To qualify, it has to be at least 60 cells long.
Thank you
Based on 100 rows of data -
In column B, work out the average for your range,
so
B1 = =Average(B1:B60)
B2 - =Average(B2:B61)
Continue this down for 41 rows so all accumulations are covered.
In column C, use the Rank option to see which set is the highest/lowest group of values.
= B1 =RANK(B1,$B$1:$B$41,0) all the way down to C40
This will give you the highest ranked, =RANK(B13,$B$1:$B$41,1) Changing the 0 to a 1 will give you the lowest ranked.
I'm creating a spread sheet to track provisional credit issued and then funds recouped. What I want to be able to do is enter a total into column A say 200.00 then in column B enter the return credit of 50.00 and have that amount auto subtract so that column A now reads 150.00.
To further complicate it in rare situations I have a third column C where I will on occassion need to subtract from column B without impacting the total in column A. So if I put 25.00 in column C. Column B becomes 25.00 but column A stays 150.00.
Hello Kristin!
If a value is written in cell A1, then the formula can no longer be written to it. This has already been discussed on the blog many times. So your question about the formula in cell A1 does not make sense. Only VBA Macro Can Solve the Problem
Hi,
I have 2 columns.
First column is text of various items , with varying amounts of the same items (1-100).
Second column is just numbers relating to data in first column.
I am trying to get a running total in the second column for each item in the first column.
Any help appreciated.
Hello Alan!
If I understand your task correctly, if the name of the item is recorded in column A, the quantity is recorded in column B, then running total can be calculated by the formula
=SUMIF($A$3:A30,A3,$B$3:B30)
I hope my advice will help you solve your task.
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.
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.
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.