Month: April 2021

4 Ways to do Running Total in Excel | Cumulative Total in Excel

by

In this article, you will see various ways to do running sum (cumulative total) in Excel.

I have discussed getting running total using formulas and using pivot table in Excel.

Simple and fast running sum using addition operator

This is the most easy and efficient way to do running sum in Excel using + operator.

1st cell formula will be different than the rest. 1st cell formula is just a cell link to a 1st cell of a range on which sum needs to be performed.

In cell C2 formula is =B2

Cell C3 formula is =C2+B3. In the rest cells below formula pattern will follow.

 =UpperCell running sum + Respective Cell of Sum Range

Since formula is using only two cell values at a time hence this is much faster than other methods.

Running Sum using addition operator

Fast running sum using Sum function

Here the Sum function is used for doing running sum. Sum function is supplied with two cell reference. 

All cells have same formula pattern like the below.

=Sum(UpperCell running sum , Respective Cell of Sum Range)

Cell C2 formula =SUM(C1,B2)

Since running sum is done by inputting only two cells hence it is also faster one.

Running Sum Using Sum function

Running total with partial fixed range using Sum function

In this method of running total Sum function is supplied with range of respective column to sum. 

In supplied range 1st cell reference is fixed or made absolute and 2nd is relative.

In the below image B2 to B13 is sale range for which I want to do running sum.

Cell C2 formula is =SUM($B$2:B2)

In SUM($B$2:B2) 1st cell reference is fixed $B$2. 

If you copy this formula in the below cells then range dimension will increase but start cell will remain same. 

 Cell C3 formula becomes =SUM($B$2:B3) (2 cells input B2,B3)

 Cell C4 formula is =SUM($B$2:B4) (3 cells inputs B2,B3,B4) and so on.

As you can see in further below cells number of cell inputs increasing by 1. 

So, calculation time of each cell’s formula increases as we go from top to bottom. 

This formula is not fast as compared to the first two methods. So, avoid this when you want to do running sum on much larger range.

Running sum using partial fixed range in Sum function

Running total in Pivot table

  1. To get running total first create pivot table.
  2. For creating pivot table select table with heading then go to Insert tab.
  3. In Insert tab click on Pivot Table, now choose pivot table placement whether in new worksheet or in existing worksheet then click on Ok.
  4. Select required columns.
  5. Drag column to sum two times in Values section of pivot table. Make sure to summarize them by Sum.
  6. Right mouse click on one of the sum column then in Show Values as click on Running Total in. 
  7. Select base column for which you want to perform running  total in this case base column is Month. Click on Ok.

This method is easy, clean and fast one.

Running total in pivot table 1
Running total (cumulative sum) in pivot table

Conclusion

First two formula methods are fast and efficient for getting running sum.

Use formula method when you want to do other calculations based on running total which may not be present in pivot table else use pivot table for running total.

Getting running total in pivot table is clean and fast method.