There are a few ways to approach a running total formula, but Excel Tables require something special, or you're likely to end up with errors that aren't obvious.
Watch the Video
Download the workbook
Enter your email address below to download the sample workbook.
The Right Way to Write Running Total Formulas for Excel Tables
You’ve probably seen a running total formula like this before:
You know the one, where the first cell reference is absolute and the second isn’t so that when you copy the formula down the column the range increases by one row at a time. Clever huh.
However when you put a formula like this in an Excel Table it’s likely to work ok the first time but if you try to add a row to your table you’ll get mixed results and often the formula will skip a row and do some funky things it shouldn’t.
For example, the table below contains a running total formula in column C before I add a new row to the table (column D shows the actual formula in column C):
Now when I add a new record in row 14 the Table automatically grows, as it should, but the formulas that get auto-filled get a bit funky on rows 13 and 14:
Not to mention the running total now returns a value formatted as a date. Huh?
You can see it before your very eyes in this animated image:
What’s also weird is if you remove row 14 the formula in row 13 corrects itself!
Excel Table Running Total Formula
Thankfully there’s a solution and it includes using the Excel Table’s own structured references.
Aside: Structured References are like dynamic named ranges that are automatically set up when you format your data in an Excel Table. They make working with Tables easy and efficient.
My Excel Table running total formula looks like this:
=SUM( INDEX([Values],1) : [@Values] )
We use INDEX to return the first cell in the Values column, and simply use the Structured Reference to the current row to return the second cell in the range we want to sum.
Note: [Values] refers to cells B2:B13 and [@Values] refers to the current row of column B. Learn more about Tables and Structured References here.
In English the formula reads:
INDEX the Values column and return the 1st cell : Return the cell reference to the current row of the Values column. And then SUM the values in that range.
If we were to step through the formula with the Evaluate Formula tool it looks like this:
If you’re familiar with the INDEX function, for example you might have used it as an alternative to VLOOKUP, then you may be having an ah-ha moment right now as you realise that INDEX has actually returned a reference to a cell, and not a value like you might expect.
There's a lot more to INDEX than meets the eye. Check out these 5 secret features of the INDEX function: