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.

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

➤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

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 

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

➤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

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 

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

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

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.

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

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. 

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

This formula uses Structured References
But you don’t have to write this formula, just type

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

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.

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

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.