# 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:

http://office.microsoft.com/en-us/excel-help/using-structured-references-with-excel-tables-HA010155686.aspx

## 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.

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.

## 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!

Here the Running Totals Column is working.