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.
And that right there is one of the beauties of INDEX. In fact I often use INDEX to return a dynamic named range instead of OFFSET, because it’s not a volatile function.
Download the workbook and try it yourself
If you liked this or know someone who could use it please click the buttons below to share it with your friends on LinkedIn, Google+, Facebook and Twitter.