|
Home > Formulas > Sum > Running Total Excel Running Total Examples
|

A running total and a rolling total both calculate a cumulative sum for numbers in a list, down to the current row.
In the screen shot below, the two types of totals are shown side-by-side.
There are examples for running and rolling totals in the sections below, and you can get the sample Excel file in the download section.

To see a running total in each row of an Excel list, you can use the SUM function, with the starting row of the sum range locked as an absolute reference. There are slightly different steps below, based on the list location:
For a worksheet list (not a named Excel table), this video shows how to set up the running total formula, and lock the starting row. The written steps are below. For the video transcript, go to the Running Total Video page.
For a worksheet list (not a named Excel table), follow these steps to create a running total. In the screen shot below, amounts are entered in column C, and a running total is calculated in column D.
See how this formula works, in the section below.
The formula uses two types of cell references:
With these row references:
Here is the formula in cell D6 -- the starting point has stayed the same and the ending point is in the current row -- C6

For a named Excel table, we can't use the worksheet list formula in the previous section. First, I'll show you the problem with that formula, and then you'll see the formula that works in a named table.
After you enter the formula in cell D2 of the named Excel table, it automatically fills down, and the running total looks correct.

But, as soon as you start a new row at the bottom of the table, the formula in the last row changes.
Before the row was added, cell D6 had this formula:
As soon as the next entry was started in row 7, the formula in D6 automatically changed. Now it has an incorrect ending reference to C7, instead of C6:

As each new row is added, the formulas in the bottom rows keep changing, to show the latest row number.
To avoid that problem, I use a slightly different formula for a running total in a named Excel table.
In the screen shot below, amounts are entered in column C, and a running total is calculated in column D.
The formula automatically fills down to cell D6

The formula uses an absolute reference to a heading cell as the starting point -- C$1
For the ending point, there is a structured table reference -- [@Amt]
This screen shot shows that when a new row is started, cell C6 continues to show its original formula, and the running total amounts are correct in each row

Instead of summing all the amounts, a rolling total sums a specific number of amounts, such as 3 months, or 12 months.
In this example, my formulas will calculate a rolling total for 12 months. Each sum will include:
To create a 12 month rolling total, I set up a small table with test data. There is only one entry per month, but the formula would work with multiple rows per month.
For the rolling total to calculate correctly, the data must be sorted by date, from oldest to newest.

To calculate the rolling 12-month total, I entered the following formula in cell C2, and copied it down to the last row with data.
In column C, each row shows the Rolling Total, for the latest 12 months (or fewer, if 12 months aren't listed)

NOTE: If your list has month END dates, use this formula instead:
You can see more EOMONTH examples on the Excel Date Functions page.
Here is the rolling total formula from cell C2, with colour coding added:
And here's how the 12-month rolling total formula works:
Instead of always seeing a 12-month rolling total, you might want to see different scenarios, in some workbooks.
To do that, you could put the "months" number in a worksheet cell, so it can be changed easily.
In the screen shot below, the number of months is entered in cell E1, and the formula will include a reference to that cell.

In the screen shot below, the number of months is entered in cell E1.
Old Formula: Here is the old formula in cell C2, which has the number of previous months (11) hard coded into the MONTH calculation
New Formula: In the formula below, I replaced the "11", with an absolute reference to cell $E$1., and copied it down to the last row of data
Note: With this formula, the rolling total shows in every row, even if:

In some workbooks, you might want to see the rolling total, only in rows where there are the required number of previous months.
To do that, use the following formula in cell C2, and copy it down to the last row of data.
Note: With this formula, the result is a number or empty string:

Don't miss my monthly Excel newsletter! You'll get quick tips, article links, and a bit of fun. Add your email, and click Sign Up.
Next, when you get my reply, click the Confirm button. I add this step to protect you from spam!
Last updated: May 25, 2025 3:27 PM