Running Total in an Excel Table
Microsoft Excel Tables are an amazingly powerful tool, just as powerful as Excel VBA, UserForms, Pivot Tables, and Dynamic Arrays. Creating a running total in an Excel Table is a common practice. But sometimes people have issues with them. As such, we cover how to use Running Total in a Microsoft Excel Table.
(Post updated 6/27/2023)
Brief Introduction to Excel Tables
Tables were introduced in Microsft 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”. If you have not used Excel Tables, now is the time to start.
If you work with Excel Dashboards, consisting of Pivot Tables, Pivot Charts, and Slicers, we recommend that you base them on Excel Tables.
Excel Tables are a powerful tool in any Excel programmers toolbox. If you want to be an Excel expert, you should learn these.
Here is pretty much the best resource for identifying structured referencing in Excel Table’s:
Demo Excel Table for our Example
For this example, I have a data set which has a few columns and rows of fictitious data. It is a simple example, so that you will more easily get the point.
You can contact us if you have questions, please do so in the comment box.
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.
The Problem with Running Totals in an Excel Table
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:
=SUM($A$2:$A2)
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.
The Solution for Running Totals in Tables
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:
- INDEX([Some Value],1)
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 in a Microsoft Excel Table for utilizing structured referencing!
Need Help with Anything Excel or Access – Contact Us
Toll-Free: 877.392.3539 | Irvine, California: 949.612.3366 | Manhattan, New York: 646.205.3261
[…] 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…
[sourcecode language=”vb”]
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
End Sub
[/sourcecode]
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