Excel Running Total
Running Total in Excel is also called as “Cumulative” which means it is the summation of numbers increasing or growing in quantity, degree or force by successive additions. It is the total which gets updated when there is a new entry in the data, in excel the normal function to calculate the total is sum function, so if we have to calculate the running total to see how the data changes with every new entry then that means the first row reference will be absolute while others change and that is the method of calculating running total in excel.
You are free to use this image on your website, templates etc, Please provide us with an attribution linkHow to Provide Attribution?Article Link to be Hyperlinked
For eg:
Source: Running Total in Excel (wallstreetmojo.com)
How to Calculate Running Total (Cumulative Sum) in Excel?
Example #1 – Performing “Running Total or Cumulative” with Simple Formula
Lets start.
- Let’s assume that we have the data on our expenses on a monthly basis as follows:
- From this data, we can observe that we spent 3,25,000 in total from January to December.
- Now, Let’s see how much of my total expenses were made by the end of the months. We are going to use a simple formula in excelto make the calculation as required.
- First, we should consider the amount spent in a particular month, i.e., January, as we are considering our spent calculation from the month of January.
- Now, calculate the money spent for the rest of the months as follows:
February – C3 +D2
- Running Total for February month is 45000.
For the next month onwards we have to consider the money spent till the previous month and money spent in the current month. i.e.
March – C4+D3 - Similarly, for the rest of the months, and the result would be as follows:
From the above result, we can observe that by the end of the year, i.e., December, we had spent 3,25,000, which is the total spent amount from the starting of the year. This running total will tell us how much we had spent on a particular month. - Till the month of July, we had spent 1,88,000, till November we had spent 2,94,000.
We can also use this data (running total) for certain analyses.
Q1) If we want to know by which month we had spent 90,000?
- A) April – We can see the cumulative column from the table, and it shows that cumulative spent till April month is 90,000.
Q2) Suppose if we want to know the % of money spent that we had spent till July?
- A) 58% – We can take the amount spent till July and divide it by the total amount spent as follows.
We had spent 58% of the money until July.
Example #2 – Performing “Running Total or Cumulative” with “SUM” Formula
In this example, we will use the SUM in excel instead of the “+” operator to calculate the cumulative in Excel.
Apply the SUM Formula in excel.
While using the SUM function, we should consider summing the earlier month spent and a current month spent. But for the first month, we should add earlier cells, i.e., Cumulative, which will be considered as zero.
Then drag the formula to other cells.
Example #3 – Performing “Running Total or Cumulative” with a Pivot Table
In order to perform running total using a Pivot table in excel, we should create a pivot table first. Create a pivot table by selecting the table and click on the pivot table from the Insert tab.
We can see the pivot table is created, Now drag the month column into the rows field & drag the amount spent column into the values field, and the table would be as follows:
In order to create a running total value, again drag the “Amount spent” column into the values field. Then right-click on the column as follows:
Click on “Show Value As,” and you will get an option of “Running Total As” and click on that.
Now you can see the table with a column having cumulative values as follows:
We can change the name of the table by editing the cell that has a Sum of Amount Spent2.
Example #4 – Performing “Running Total or Cumulative” with a Relative Named Range
In order to perform running total with a relative named range, we need to do some temporary changes in the excel options.
Change Excel referenceExcel ReferenceCell reference in excel is referring the other cells to a cell to use its values or properties. For instance, if we have data in cell A2 and want to use that in cell A1, use =A2 in cell A1, and this will copy the A2 value in A1.read more style from A1 to R1C1 from excel options as below:
Reference style R1C1 refers to Row 1 and Column 1. In this style, we can find a positive and negative sign which is used for a reason.
In Rows: –
- +(Positive) sign refers to a Downward direction.
- – (Negative) sign refers to an Upward direction.
Ex- R[3] refers to connect the cell, which is 3 rows below the current cell, and R[-5] refers to connect the cell, which is 5 rows above the current cell.
In Columns: –
- +(Positive) sign refers to the Right direction.
- – (Negative) sign refers to the Left direction.
Ex- C[2] refers to connect the cell, which is 2 columns right to the current cell, and C[-4] refers to connect the cell, which is 4 columns left to the current cell.
In order to use the reference style to calculate the running total, we have to define a name with certain criteria.
In our example, we have to define name by “R[-1]C” because we are calculating the cumulative, which is the sum of the previous row of the cell and column with every individual month expense. Define a name in excel with “Cum”(You can define as per your wish) as follows:
Go to the Formulas tab and select the defined name.
Then New Name window will pop out and give the name as per your wish and give the condition that you want to perform for this particular name that you defined. Here we take R[-1]C because we are going to sum the previous row of the cell and column with every individual month expense.
Once the name is defined, then go to the column of Cumulative/Running Total and use the defined name into SUM function as follows:
This tells us to perform SUM with the cell RC[-1} and Cum (Which is already defined), and in the first cell, we get the same expense incurred in the month of January.
Then drag down the formula till the end of the table, and we can see the cumulative results will be out as below:
Things to Remember
- Running Total/Cumulative will help in analyzing the information from the data for decision-making purposes.
- Relatively named range type of running total is performed in order to avoid the problems with inserting and deleting rows from the data because this kind of operation will refer to the cell as per the condition given though we insert or delete rows or columns.
- Cumulative in Excel is mostly used in financial modelingFinancial ModelingFinancial modeling refers to the use of excel-based models to reflect a company's projected financial performance. Such models represent the financial situation by taking into account risks and future assumptions, which are critical for making significant decisions in the future, such as raising capital or valuing a business, and interpreting their impact.read more, and the final value of the cumulative will be the same as the Total Sum.
- “Total Sum” and “Running Total” is different, and the key difference is the computation we do. Total Sum will perform the sum of each number in the series of data, whereas “Running Total” will sum the previous value with the current value from the data.
Recommended Articles
This has been a guide to Running Total in Excel. Here we discuss how to Calculate Running Total (cumulative sum) using Simple Formula, SUM Formula, Pivot Table, and Named Range in Excel along with practical examples and downloadable excel template. You may learn more about excel from the following articles –
- Running Total in Power BI
- Excel Examples of Text SUMIF
- Excel Examples of Not Blank SUMIF
- Examples of Chi-Square Test in Excel
- 35+ Courses
- 120+ Hours
- Full Lifetime Access
- Certificate of Completion
47.1K subscribers
Leave a Reply