As you can see I have an ID row, with a simple formula to get me an incremented number:

=ROW()-ROW (tblData[[#Headers],[ID]])

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!