Tanjima Hossain
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. But before that, check the image below where we have shown an overview of a dataset where the calculation of the running total was performed.
Download Practice Workbook
Download this workbook to practice while you are reading this article.
9 Ways to Calculate Running Total in Excel
I have used a data table that 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.
Now I will illustrate 9 easy and simple methods to calculate the running total of sales of this data set. Let’s explore the method one by one.
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.
Now follow the steps below.
Steps:
➤First, select the output Cell E5.
➤Then, write the following formula
=D5
➤Now, press ENTER. Then the value in Cell D5 will be entered into Cell E5.
➤Now, select the output Cell E6
➤Then, write the following formula.
=D6+E5➤Press ENTER. As a result, it will add up the values of the left and upper cells of Cell E6. That means you will get the sales of January and February together in Cell E6.
➤Now, drag down the Fill Handle Tool.
➤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.
To do that, follow the steps below.
Steps:
➤First, select the output Cell E5.
➤Then, type the following formula and press ENTER. As a result, you will get the following result.
=SUM(D5) 
➤Here, we can see that the value in the D5 Cell has been entered into Cell E5.
➤ Now, select the output Cell E6 and write the following formula, and press ENTER.
=SUM(D6,E5)➤ Consequently, it will add up the values of the left and upper cells of Cell E6. So, you will get the sales of January and February together in Cell E6.
➤Now, drag down the Fill Handle Tool.
➤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. To know more, follow the steps below.
➤Firstly, select the output Cell E5.
➤Then, type the following formula and press ENTER. Consequently, we will get the following result.
=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.
➤Now, drag down the Fill Handle Tool to get the cumulative sum for the rest of the cells.
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.
Now, follow the steps below to get Cumulative Sum of North Region only.
Steps:
➤First, select the output Cell E5 and 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. Now press ENTER. As a result, you will get the following result.
➤ Now, press ENTER.
➤Now, drag down the Fill Handle Tool to get the complete result.
pic 117
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.
Now, follow the steps below.
Steps:
➤ First, go to the Data tab then select 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.
➤Now, go to Add Column Tab>>Index Column Dropdown>>From 1 Option.
➤Then, Index Column will be created.
➤Now, 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
➤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 Excel Table to Calculate Cumulative Sum
You can create a Table to calculate the running total easily because here you just need to type the formula once and then the other cells will be filled up with the values automatically. To know more, follow the steps below.
Steps:
➤First, select the entire dataset and then press CTRL+L or CTRL+T to open Create Table dialogue box. Here, click OK.
➤As a result, a table will be created like this.
➤ Now, select the output Cell E5 and write down 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.
➤After that, the Cumulative sum column will be filled up with the running total of sales.
Method-7: Creating Table and Using INDEX Function
Here, I have created a Table and used the INDEX function to calculate the running total easily. To know more, follow the steps below.
Steps:
➤First, follow the Steps of Method-6 to create a table from the existing data set.
➤Now, select the output Cell E5 and write down the following formula.
=SUM(INDEX([Sales],1):[@Sales])
[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. Afterward, the Cumulative sum column will be filled up with the running total of sales.
Method-8: Using Pivot Table for Calculating Running Total
You can use the Pivot Table option for calculating the running total of the Sales.
To do that, follow the steps below.
Steps:
➤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 has two portions; PivotTable1 and PivotTable Fields.
➤Drag down Month to the Rows area once
➤Drag down Sales to the Values area twice.
➤Now, click the Sum of Sales2 Dropdown and select the Value Field Settings Option.
➤ Now, choose the following in the Value Field Settings window.
Custom Name➜Cumulative sum (or any other name)
Summarize Values by➜Sum
➤Then, select Show Values as➜ Running Total In
➤After that, select the Base field as Month
➤Finally, press OK.
➤As a result, 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.
To know more, follow the steps below.
Steps:
➤Firstly, go to Insert Tab>>Pivot Table Option
➤After that, PivotTable from table or range Wizard will pop up.
➤From here, select the data range. Also, click the New Worksheet Option and Add this data to the Data Model
➤Finally, press OK.
➤As a result, a new sheet with PivotTable Fileds will open. From there, drag down Month to the Rows area and Sales to the Values area.
➤ As a result, you will get the following table in the new sheet.
➤ Now, right-click on the Range Option (which is the table name) and select Add Measure Option.
➤After that, the Measure Dialogue Box will appear.
➤Here, write Cumulative Sum or any other name as Measure Name.
➤Then, type the following formula in the Formula Box.
=CALCULATE (SUM ( Range[Sales] ),FILTER (ALL (Range[Month] ),Range[Month] <= MAX (Range[Month]) ))Here, 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
➤Finally, press OK.
➤After that, the Cumulative Sum Measure will appear under the Range Table.
➤Now, check the Cumulative Sum box and, drag down the Cumulative Sum to the Values area.
➤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)
