How to Calculate Running Total in Excel (9 Ways)

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.

data table

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.

using simple formula

Step-01:
➤Select the output Cell E5
➤Write the following formula

=D5

using simple formula

➤Press ENTER

Then the value in Cell D5 will be entered into Cell E5

using simple formula

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 

using simple formula

➤Press ENTER
➤Drag down the Fill Handle Tool

using simple formula

Result:
In this way, you will get the running total of the Sales 

calculate running total in Excel

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.

using SUM function

Step-01:
➤Select the output Cell E5
➤Type the following formula

=SUM(D5) 

using SUM function

➤Press ENTER

Then the value in the D5 Cell will be entered into the Cell E5

using SUM function

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 

using SUM function

➤Press ENTER
➤Drag down the Fill Handle Tool

using SUM function

Result:
Afterward, you will get the running total of the Sales 

using SUM function

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.

SUM function with mixed reference

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.

SUM function with mixed reference

Step-02:
➤Press ENTER
➤Drag down the Fill Handle Tool

SUM function with mixed reference

Result:
Then, you will get the running total of the Sales 

SUM function with mixed reference

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.

using SUMIF function

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

using SUMIF function

Step-02:
➤Press ENTER
➤Drag down the Fill Handle Tool

using SUMIF function

Result:
After that, you will get the running total of the Sales for the North Region.

using SUMIF function

Method-5:  Using Power Query to Calculate Running Total

You can use the Power Query to calculate the running total of sales

power query

Step-01:
➤Go to Data Tab>>From Table/Range Option

power query

Then the Create Table Dialog Box will appear.
➤Select the data range
➤Click My table has headers Option
➤Press OK

power query

After that, the following Power Query Editor will open.

power query

Step-02:
➤Go to Add Column Tab>>Index Column Dropdown>>From 1 Option

power query

Then, Index Column will be created

power query

Step-03:
➤Go to Add Column Tab>>Custom Column Option

power query

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

power query

Then the Cumulative sum column containing the running total values will appear.

power query

➤Select the Index column and Right-click on your mouse.
➤Choose the Remove option

power query

Afterward, the Index column will vanish.
➤Go to Home Tab>>Close & Load Option

power query

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. 

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.

table

Step-01:
➤Go to Insert Tab>>Table Option

table

After that, the Create Table Dialog Box will pop up.
➤Select the data range
➤Click My table has headers Option
➤Press OK

table

Then the Table will be created as below.

table

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.

table

➤Press ENTER

Result:
After that, the Cumulative sum column will be filled up with the running total of sales.

table

Method-7:  Creating Table and using INDEX

Here, I have created a Table and used the INDEX function to calculate the running total easily.

table

Step-01:
➤Follow Step-01 of Method-6

Then, you will get the following table.

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.

table

➤Press ENTER

Result:
Afterward, the Cumulative sum column will be filled up with the running total of sales.

calculate running total in Excel

Method-8:  Using Pivot Table

You can use the Pivot Table option for calculating the running total of the Sales.

pivot table

Step-01:
➤Go to Insert Tab>>Pivot Table Option

pivot table

Then, PivotTable from table or range Wizard will pop up.
➤Select the data range
➤Click the New Worksheet Option
➤Press OK

pivot table

Then a new sheet will appear which have two portions; PivotTable1 and PivotTable Fields

pivot table

Step-02:
➤Drag down Month to the Rows area once and Sales to the Values area twice

pivot table

➤Click the Sum of Sales2 Dropdown
➤Select the Value Field Settings Option

pivot table

➤Do the following

Custom NameCumulative sum (or any other name)
Summarize Values bySum

pivot table

➤Select Show Values as Running Total In

pivot table

➤Select the Base field as Month
➤Press OK

pivot table

Result:
After that, you will get the running total of the Sales values for each month.

calculate running total 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.

pivot table

Step-01:
➤Go to Insert Tab>>Pivot Table Option

pivot table

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

pivot table

Step-02:
➤Drag down Month to the Rows area and Sales to the Values area

pivot table

Step-03:
➤Right-click on the Range Option (which is the table name)
➤Select Add Measure Option

pivot table

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

power pivot

After that, the Cumulative Sum Measure will appear under the Range Table

power pivot

➤Drag down the Cumulative Sum to the Values area

power pivot

Result:
Afterward, you will get the running total of the Sales values for each month.

power pivot

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.

practice

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.

Tanjima Hossain

Hello everyone, This is Tanjima Hossain. I have completed my graduation from BUET. Then I have started working as a technical writer in SOFTEKO. I have grown interest in technical content writing, research topics, numerical analysis related field and so I am here. Besides this I love to interact with different people and I love to spend my spare time by reading, gardening ,cooking etc.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo