Excel Cumulative Sum - easy way to calculate running total

Svetlana Cheusheva by , updated on

This short tutorial shows how a usual Excel Sum formula with a clever use of absolute and relative cell references can quickly calculate a running total in your worksheet.

A running total, or cumulative sum, is a sequence of partial sums of a given data set. It is used to show the summation of data as it grows with time (updated every time a new number is added to the sequence).

This technique is very common in everyday use, for example to calculate the current score in games, show year-to-date or month-to-date sales, or compute your bank balance after each withdrawal and deposit. The following examples show the fastest way to calculate running total in Excel and plot a cumulative graph.

How to calculate running total (cumulative sum) in Excel

To calculate a running total in Excel, you can use the SUM function combined with a clever use of absolute and relative cells references.

For example, to calculate the cumulative sum for numbers in column B beginning in cell B2, enter the following formula in C2 and then copy it down to other cells:

=SUM($B$2:B2)

In your running total formula, the first reference should always be an absolute reference with the $ sign ($B$2). Because an absolute reference never changes no matter where the formula moves, it will always refer back to B2. The second reference without the $ sign (B2) is relative and it adjusts based on the relative position of the cell where the formula is copied. For more information about Excel cell references, please see Why use dollar sign ($) in Excel formulas.

So, when our Sum formula is copied to B3, it becomes SUM($B$2:B3), and returns the total of values in cells B2 to B3. In cell B4, the formula turns into SUM($B$2:B4), and totals numbers in cells B2 to B4, and so on:
Excel Cumulative Sum formula

In a similar manner, you can use the Excel SUM function to find the cumulative sum for your bank balance. For this, enter deposits as positive numbers, and withdrawals as negative numbers in some column (column C in this example). And then, to show the running total, enter the following formula in column D:

=SUM($C$2:C2)
Calculating running total for the bank balance

Strictly speaking, the above screenshot shows not exactly a cumulative sum, which implies summation, but some sort of "running total and running difference"  Anyway, who cares about the right word if you've got the desired result, right? :)

At first sight, our Excel Cumulative Sum formula looks perfect, but it does have one significant drawback. When you copy the formula down a column, you will notice that the cumulative totals in the rows below the last cell with a value in column C all show the same number:
The cumulative totals in the rows below the last cell with a value in the dependent column all show the same number.

To fix this, we can improve our running total formula a bit further by embedding it in the IF function:

=IF(C2="","",SUM($C$2:C2))

The formula instructs Excel to do the following: if cell C2 is blank, then return an empty string (blank cell), otherwise apply the cumulative total formula.

Now, you can copy the formula to as many cells as you want, and the formula cells will look empty until you enter a number in the corresponding row in column C. As soon as you do this, the calculated cumulative sum will appear next to each amount:
An improved Excel cumulative sum formula

How to make a cumulative graph in Excel

As soon as you've calculated the running total using the Sum formula, making a cumulative chart in Excel is a matter of minutes.

  1. Select your data, including the Cumulative Sum column, and create a 2-D clustered column chart by clicking the corresponding button on the Insert tab, in the Charts group:
    Create a 2-D clustered column chart.
  2. In the newly created chart, click the Cumulative Sum data series (orange bars in this example), and right click to select Change Series Chart Type... from the context menu.
    Select Change Series Chart Type... from the context menu.
  3. If you are using a recent version of Excel 2013 or Excel 2016, select the Combo chart type, and click on the first icon (Clustered Column - Line) at the top of Change Chart Type dialog:
    In Excel 2013 or Excel 2016, select the Combo chart type.

    Or, you can highlight the Custom Combination icon, and choose the line type you want for the Cumulative Sum data series (Line with Markers in this example):

    Choose the line type you want for the Cumulative Sum data series.

    In Excel 2010 and earlier, simply select the desired line type for the Cumulative Sum series, which you've selected on the previous step:

    In Excel 2010 and earlier, select the desired line type for the Cumulative Sum series.

  4. Click OK, and evaluate your Excel cumulative chart:
    Excel cumulative chart
  5. Optionally, you can right-click the Cumulative Sum line in the chart, and select Add Data Labels from the context menu:
    Add data labels to the Cumulative Sum line.

As the result, your Excel cumulative graph will look similar to this:
Excel cumulative graph with data labels

To embellish your Excel cumulative chart further, you can customize the chart and axes titles, modify the chart legend, choose other chart style and colors, etc. For the detailed instructions, please see our Excel charts tutorial.

This is how you do a running total in Excel. If you are curious to learn a few more useful formulas, check out the below examples. I thank you for reading and hope to see you again soon!

Table of contents