How to Calculate Running Total in Excel (9 Easy Ways)

Get FREE Advanced Excel Exercises with Solutions!

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.

The Overview of Running Total


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.

The Dataset of running total in Excel

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.

Dataset of running total calculation by using addition operator

Now follow the steps below.

Steps:

➤First, select the output Cell E5.
➤Then, write the following formula

=D5

Writing formula in cell E5

➤Now, press ENTER. Then the value in Cell D5 will be entered into Cell E5.

Result after inserting formula in 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.

Writing formula in cell E6

➤Now, drag down the Fill Handle Tool.

Using Fill Handle Tool

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

Results of using formula for calculating running total

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.

Dataset of using sum function to calculate running total in Excel

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) 

Result after applying sum function in cell E5

➤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.

Results after using sum function to calculate running total in excel

➤Now, drag down the Fill Handle Tool.

Using Fill Handle to using sum function

Afterward, you will get the running total of the Sales.

Result after using sum function to calculate running total in excel

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)

Using SUM function and Mixed Reference to calculate running total in excel

➤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.

Using Fill Handle to autofill SUM function and 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.

Data set of Calculation of Running Total Based on Criteria

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.

Calculation of Running Total Based on Criteria by using sumif function

➤Now, drag down the Fill Handle Tool to get the complete result.

Using Fill Handle to Autofill the sumif function to calculate running total in excel

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.

Dataset for Using Power Query to Calculate Running Total

Now, follow the steps below.

Steps:

➤ First, go to the Data tab then select From Table/Range option.

Choosing From Table/Range option from data tab

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

Creating table from the dataset

➤After that, the following Power Query Editor will open.

Power Query Window

➤Now, go to Add Column Tab>>Index Column Dropdown>>From 1 Option.

Adding extra Index column from power query editor

➤Then, Index Column will be created.

Addition of extra column in Power Query window

➤Now, go to Add Column Tab>>Custom Column Option.

Custom Column from power query editor

➤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

Editing Custom Column

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

Cumulative sum column on power query

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

Removing Index column from power query

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

Selecting Close & Load to option in Power Query

➤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. 

Results of Power Query to calculate running total in excel

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.

Creating Table to Creating Excel Table to Calculate running totol in excel➤As a result, a table will be created like this.

Creating Table to calculate running total in excel

➤ 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.

Applying formula in excel table to calculate running total in excel

➤Press ENTER.

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

Results of calculation of running total in excel


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.

Creating Table from the dataset using the previously mentioned methods.

➤Now, select the output Cell E5 and write down the following formula.

=SUM(INDEX([Sales],1):[@Sales])

Using formula in EXcel table

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. Afterward, the Cumulative sum column will be filled up with the running total of sales.

Results of Running Total


Method-8:  Using Pivot Table for Calculating Running Total

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

Dataset for Using Pivot Table for Calculating Running Total

To do that, follow the steps below.

Steps:

➤Go to Insert Tab>>Pivot Table Option

Selecting pivot table from Insert tab

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

PivotTable from table or range dialogue box

➤Then a new sheet will appear which has two portions; PivotTable1 and PivotTable Fields.

New Sheet with many windows

➤Drag down Month to the Rows area once

➤Drag down Sales to the Values area twice.

Dragging windows in different fields

➤Now, click the Sum of Sales2 Dropdown and select the Value Field Settings Option.

Choosing Value Field Settings

➤ Now, choose the following in the Value Field Settings window.

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

Choosing options in Value Field Settings window

➤Then, select Show Values as Running Total In

➤After that, select the Base field as Month
➤Finally, press OK.

Choosing Base Filed in Show Value As Tab in Value Field Settings window

➤As a result, you will get the running total of the Sales values for each month.

Result after using pivot table

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.

Data set of Using Power Pivot and DAX Measure

To know more, follow the steps below.

Steps:

➤Firstly, go to Insert Tab>>Pivot Table Option

Inserting Pivot table

➤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.

Using Pivot Table for Calculating Running Total

➤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.

Dragging Ranges in different fields

➤ As a result, you will get the following table in the new sheet.

Results after using pivot table

➤ Now, right-click on the Range Option (which is the table name) and select Add Measure Option.

Choosing Add measure option from PivotTable Fileds

➤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.

Measure Dialogue Box in Power Pivot

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

Cumulative Sum in the window

➤Now, check the Cumulative Sum box and, drag down the Cumulative Sum to the Values area.

Dragging down the Cumulative Sum to the Values area.

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

Results of runng sum Using Power Pivot and DAX Measure


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.

Practise Yourself sheet


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

Tanjima Hossain

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

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo