Running Total in an Excel Table
Tables were introduced in Excel, 2007. I love them, they’re very handy. Along with them we were introduced to Table referencing. This made it easy to identify parts of a Table. It’s called “structured referencing”. Here is pretty much the best resource for identifying structured referencing in Excel Table’s: http://office.microsoft.com/en-us/excel-help/using-structured-references-with-excel-tables-HA010155686.aspx
For this example I have a data set which has a few columns and rows of fictitious data.
As you can see I have an ID row, with a simple formula to get me an incremented number:
I have named my Table “tblData”. Here I am referencing the row of the header of that column, as you can see with the structured reference “[[#Headers],[ID]]”. This is useful because no matter where my Table is I will have an ID which starts at 1 and continues up in a linear increment.
If you want a running total in a Table, it’s not so easy, and there’s no specific structured reference for this. In previous versions (and still currently) we can make use of absolute vs. relative referencing. For example, if we have numbers in column A (assuming A1 contains a header, data starts in row 2), we could use the following:
Notice there is no $ on the last row number reference. This leaves it relative, so when we copy it down it will increment nicely and we’ll have our running total. However, using this in a Table isn’t a good idea, as it can be fraught with problems. The problem is when you start adding new rows in a Table. The behavior will give you odd results, and it will not always continue your range as you would think.
The bottom line is if you are using a Table, it’s always best to use structured referencing when you can.
I want to do a running total on the ‘Some Value’ column. What we need is to be able to define a range which says from the first cell in this Table column to the current row the formula is in. We know we can reference the current row in a Table with the following structured reference:[@[Column1]]
Where “Column1” is the header of the column you’re looking at. This is the second part of our range to use in the formula. For the first part, we need a way to use structured referencing for the first row in the column. This is the tricky part, which took me quite a while to find. We will use the INDEX() function for this, which is really quite simple:
This says we will look at the range of the entire “Some Value” column, but only row 1. This will give us the first part of our formula.
If we put it all together, the function would then become:
=SUM(INDEX([Some Value],1):[@[Some Value]])
That’s it, you now have a running total utilizing structured referencing!
[…] I adapted a formula from the Excel and Access Blog to generate the month numbers for the cash flow table. Share […]
[…] I adapted a formula from the Excel and Access Blog to generate the month numbers for the cash flow […]
[…] is a trick for doing a running total via structured cell reference formulas (http://excelandaccess.wordpress.com/2012/07/12/running-total-in-an-excel-table/), and is the example I’m using to show how standard cell referencing won’t work in […]
Thank you for the much needed information. How about if you want to calculate a running total but it is using the same cell as the input field only?Everyday the value in the cell is changing.For example, today you enter 20 in cell H2 and the next day 30 in cell H2 again, the running total in cell I2 should now be 50.Please shed a light on this.Thank you very much.
Sorry for not seeing this earlier. That would take a little bit of coding to get that done. I’m not sure I’d necessarily recommend something like this, but if I were to do it, it’d probably be something like this…
Private Sub Worksheet_Change(ByVal Target As Range)
Dim RunningTotalCell As Range
If Target.Cells.Count > 1 Then Exit Sub
If Target.Address(0, 0) <> "H2" Then Exit Sub
If IsNumeric(Target) = False Then Exit Sub
Worksheets("Sheet1").Range("H1").Value = Worksheets("Sheet1").Range("H1").Value + Target.Value
Thanks for the article. I think there is a minor typo. You wrote: “As you can see I have an ID row, “. Did you mean: “As you can see I have an ID column, “?
Thank you for the article, it worked perfectly for my application