If you are looking for some of the easiest ways to calculate the running total in Excel, then you will find this article useful. Running total means a cumulative total of a range of values. It becomes necessary to get the running total of a range of sales or these types of calculations in different business organizations. So, let’s get started with the main article.
Download Workbook
9 Ways to Calculate Running Total in Excel
I have used a data table which includes sales for some months of a company. By using the following methods you will be able to have the running total for each month. For this calculation I have used Microsoft Excel 365 version, you can use any other version according to your convenience.
Method-1:Â Using a Formula to Calculate Running Total
You can have the running total of the sales by using a simple formula including Addition Operator. To have the results I have added a column named Cumulative Sum.
Step-01:
➤Select the output Cell E5
➤Write the following formula
=D5
➤Press ENTER
Then the value in Cell D5 will be entered into Cell E5
Step-02:
➤Select the output Cell E6
➤Write the following formula
=D6+E5
It will add up the values of the left and upper cell of Cell E6
That means you will get the sales of January and February together in Cell E6Â
➤Press ENTER
➤Drag down the Fill Handle Tool
Result:
In this way, you will get the running total of the SalesÂ
Read More: How to Calculate Running Total in One Cell in Excel (5 Ways)
Method-2:Â Using SUM function to Calculate Running Total
If you want to have the running total of the sales, you can use the SUM function.
Step-01:
➤Select the output Cell E5
➤Type the following formula
=SUM(D5)Â
➤Press ENTER
Then the value in the D5 Cell will be entered into the Cell E5
Step-02:
➤Select the output Cell E6
➤Write the following formula
=SUM(D6,E5)
It will add up the values of the left and upper cell of Cell E6
So, you will get the sales of January and February together in Cell E6Â
➤Press ENTER
➤Drag down the Fill Handle Tool
Result:
Afterward, you will get the running total of the SalesÂ
Read More: Cumulative Sum in Excel If Condition Applied (6 Methods)
Method-3:Â Using SUM function and Mixed ReferenceÂ
To calculate the running total of the sales, you can use the SUM function with a partially fixed range.
Step-01:
➤Select the output Cell E5
➤Type the following formula
=SUM($D$5:D5)
Here, $D$5 has the Absolute Reference so that it will be fixed
D5 has the Relative Reference so that it will change with each row so that the range of SUM will increase with each row.
Step-02:
➤Press ENTER
➤Drag down the Fill Handle Tool
Result:
Then, you will get the running total of the SalesÂ
Method-4:Â Calculation of Running Total Based on Criteria
If you want to have the running total only for the North Region then you can use the SUMIF function for using the criteria. For getting these values I have introduced the Cumulative Sum(North Region) column.
Step-01:
➤Select the output Cell E5
➤Type the following formula
=SUMIF($B$5:B5,"North",$D$5:D5)
Here, $B$5:B5 is the criteria range, North is the criteria, and $D$5:D5 is the sum range
Step-02:
➤Press ENTER
➤Drag down the Fill Handle Tool
Result:
After that, you will get the running total of the Sales for the North Region.
Read More: Quick Analysis Tool: Calculation of Running Total in Excel (4 Ways)
Method-5:Â Using Power Query to Calculate Running Total
You can use the Power Query to calculate the running total of sales
Step-01:
➤Go to Data Tab>>From Table/Range Option
Then the Create Table Dialog Box will appear.
➤Select the data range
➤Click My table has headers Option
➤Press OK
After that, the following Power Query Editor will open.
Step-02:
➤Go to Add Column Tab>>Index Column Dropdown>>From 1 Option
Then, Index Column will be created
Step-03:
➤Go to Add Column Tab>>Custom Column Option
After that, a Custom Column Wizard will appear.
➤Write Cumulative sum or any other name in the New column name Box
➤Type the following formula in the Custom column formula Box
=List.Sum(List.Range(#“Added Index”[Sales],0,[Index]))
List.Sum will give the sum of the range within it and List.Range will give the range of Sales and it will change depending on the Index value
Make sure that No syntax errors have been detected has been appeared
➤Press OK
Then the Cumulative sum column containing the running total values will appear.
➤Select the Index column and Right-click on your mouse.
➤Choose the Remove option
Afterward, the Index column will vanish.
➤Go to Home Tab>>Close & Load Option
Result:
In this way, you will get the following table containing the running total of Sales. You can choose the Accounting format for the Sales and Cumulative sum column.Â
Read More: How to Calculate Running Total by Group Using Excel Power Query
Method-6: Creating Table Â
You can create a Table to calculate the running total easily because here you just need to type the formula for once and then the other cells will be filled up with the values automatically.
Step-01:
➤Go to Insert Tab>>Table Option
After that, the Create Table Dialog Box will pop up.
➤Select the data range
➤Click My table has headers Option
➤Press OK
Then the Table will be created as below.
Step-02:
➤Select the output Cell E5
➤Use the following formula
=SUM(Table[[#Headers],[Sales]] :[@Sales])
This formula uses Structured References
But you don’t have to write this formula, just type
=SUM(D4:D5)
Then automatically Table[[#Headers],[Sales]] will replace D4, and [@Sales] will replace D5 within the formula.
➤Press ENTER
Result:
After that, the Cumulative sum column will be filled up with the running total of sales.
Method-7:Â Creating Table and using INDEX
Here, I have created a Table and used the INDEX function to calculate the running total easily.
Step-01:
➤Follow Step-01 of Method-6
Then, you will get the following table.
Step-02:
➤Select the output Cell E5
➤Use the following formula
=SUM(INDEX([Sales],1):[@Sales])
This formula uses Structured References
[Sales] is the range of the Sales column
And [@Sales] is the corresponding cell of the Sales column
Here, the INDEX function will create a reference to the first cell of the Sales column because 1 is used for row number
So, the range for the SUM function will increase with each row.
➤Press ENTER
Result:
Afterward, the Cumulative sum column will be filled up with the running total of sales.
Method-8:Â Using Pivot Table
You can use the Pivot Table option for calculating the running total of the Sales.
Step-01:
➤Go to Insert Tab>>Pivot Table Option
Then, PivotTable from table or range Wizard will pop up.
➤Select the data range
➤Click the New Worksheet Option
➤Press OK
Then a new sheet will appear which have two portions; PivotTable1 and PivotTable Fields
Step-02:
➤Drag down Month to the Rows area once and Sales to the Values area twice
➤Click the Sum of Sales2 Dropdown
➤Select the Value Field Settings Option
➤Do the following
Custom NameâžśCumulative sum (or any other name)
Summarize Values byâžśSum
➤Select Show Values as➜ Running Total In
➤Select the Base field as Month
➤Press OK
Result:
After that, you will get the running total of the Sales values for each month.
Read More: How to Use Pivot Table to Calculate Running Total by Date in Excel
Method-9:Â Using Power Pivot and DAX Measure
You can use the Pivot Table option and DAX Measure for calculating the running total of the Sales.
Step-01:
➤Go to Insert Tab>>Pivot Table Option
After that, PivotTable from table or range Wizard will pop up.
➤Select the data range
➤Click the New Worksheet Option and Add this data to the Data Model
➤Press OK
Step-02:
➤Drag down Month to the Rows area and Sales to the Values area
Step-03:
➤Right-click on the Range Option (which is the table name)
➤Select Add Measure Option
Then, Measure Dialog Box will pop up
➤Write Cumulative Sum or any other name as Measure Name
➤Type the following formula in the Formula Box
=CALCULATE (SUM ( Range[Sales] ),FILTER (ALL (Range[Month] ),Range[Month] <= MAX (Range[Month]) ))
Range[Sales] refers to the Sales column of the Range Table and Range[Month] indicates the Month column of the Range Table
➤Select the Category as Currency
➤Press OK
After that, the Cumulative Sum Measure will appear under the Range Table
➤Drag down the Cumulative Sum to the Values area
Result:
Afterward, you will get the running total of the Sales values for each month.
Practice Section
For doing practice on your own we have provided a section for this purpose in a sheet named Practice. Please do it by yourself.
Conclusion
In this article, I have tried to explain the easiest methods to calculate the running total in Excel. Hope you will find it helpful. Please provide any further suggestions if you have any. Thank you.
Related Articles
- How to Calculate Running Total in One Cell in Excel (5 Ways)
- Calculate Debit Credit Running Balance Using Excel Formula (3 Examples)
- How to Keep a Running Balance in Excel (8 Methods)
- How to Calculate Running Balance Using Excel Formula (4 Ways)
- Calculate Running Balance Using Excel Formula (4 Ways)