Formatted Tables allow you to create formulas that automatically copy down as the table expands. To create a running total in a column you have a couple of options.

In the example below we want to create a running total in column C.

There are two solutions.

Structured references only

Formatted tables include structured references that are similar to range names. We can use those structured references to create the running total formula.

We can use the SUM function but there is trick to creating the formula. We need to use the colon : symbol to separate the starting cell and the last cell for the SUM range. The problem with this technique is that sometimes Excel won’t let you use the colon. It will let you use the comma , so that is the workaround. Try creating it using the colon and if it doesn’t work use the comma and then change that to a colon – see images below.

In cell C2 type

=SUM(

And click on cell B1

This enters the headers reference as above, type a colon if it doesn’t allow you the type the comma and click on cell B2 and add the closing bracket to the formula.

Then change the comma into a colon if necessary.

Press Enter.

The final formula is

=SUM(tblData[[#Headers],[Amount]]:[@Amount])

This formula is identical in all the formula cells in column C.

This formula works because the SUM function treats text entries as zero – e.g. the heading in cell B1 is treated a zero.

Structured and a cell reference

The second technique combines a structured reference with a relative cell reference.

We will add another column to demonstrate this technique. In cell D2 type

=SUM(

Then click on cell B2 and type a comma then type D1 and press Enter.

The final formula is

=SUM([@Amount],D1)

This formula works because the SUM function treats text entries (cell D1) as zero and doesn’t display a #VALUE! error.

If you had used

=[@Amount]+D1

It would display have displayed a #VALUE! error.

Referring to other rows

In general you don’t refer to another row in a table, but in this case the formula works even when the table is sorted.