Excel Tables are one of the best features of Excel. Tables were originally introduced as part of Excel 2003, under the name of Lists, but were expanded and renamed to Tables in Excel 2007. Whilst it is possible to use the standard cell referencing with a Table, they have their own referencing style. Using this new referencing style there is no simple way to create a reliable running total and maintain the benefit of using a Table.

But… with a bit of creative thinking, we can create something which provides exactly what we need.

### The example

All the scenarios below use the same Table. The goal is to create a rolling total in the Balance column, which maintains the use of structured referencing and will still work when rows are deleted or sorted.

## Normal cell references

We could use normal cell references in either the A1 or R1C1 style. However, as the purpose of a Table is to use the same formula in each row of the column, it is not possible to just use + (plus) and – (minus) in the formula.

The formula in Cell E2 is:

=[@In]-[@Out]+E1

The result of the formula is #VALUE!, because Cell E1 is a text value, which cannot be added to a number.

By using the SUM function with a comma (known as the Union Operator), the text value will be calculated as a zero, which avoids the #VALUE! error.

The formula in Cell E2 is:

=SUM([@In],-[@Out],E1)

Advertisement:

The result of this formula is a running total in each row of the Balance column.

Rather than the SUM function, you could use the N function. N will zero if the cell reference within it is not a number, otherwise it will display the number. IF Cell E2 contained the following formula it would create a running total.

=[@In]-[@Out]+N(E1)

These methods falls short when a row is deleted. The screenshot below shows the result after Row 4 has been deleted.

As you can see by the #REF! error, this method does not work when rows are deleted. Which doesn’t meet the requirements we wanted. But, don’t worry, the remainder of the post contains solutions which do.

## OFFSET Function

The OFFSET function is used to refer to a specific range or cell, by using another cell as a start point. It can have up to 5 arguments.

- Reference: is the cell from which you want to base the offset
- Rows: is the number of rows, that you want the Reference cell to move by.
- Cols: is the number of columns, that you want the Reference cell to move by.
- Height (optional): is number of rows you want the range to be
- Width (optional): is the number of columns you want the range to be

As a simple example, look at the formula below:

=OFFSET(E2,-1,0,1,1)

The formula above would start at Cell E2, move up by -1, move right by 0, and create a range which is 1 cell high and 1 cell wide. The result of which would be Cell E1.

We can include this methodology into our rolling total formula.

The formula in Cell E2 is:

=SUM([@In],-[@Out],OFFSET([@Balance],-1,0))

The result of this formula is a running total in each row of the Balance column. Even if a row is deleted the calculation will continue to be the rolling total.

Sounds like a reasonable option, right? However, you may know that OFFSET is a volatile function, which means it will recalculate the result at every cell change, making calculations inefficient in certain circumstances.

## Relative named range

Advertisement:

An option, which does not involve a volatile formula is to use a relative named range. In this scenario it means creating a named range which always refers to the cell above.

Click on Cell E2, then from Ribbon click *Formulas -> Name Manager*

On the Name Manager window click *New…*

Ensure the *Refers To* box contains the cell address for the cell above the selected cell (e.g. E1 is the cell above when E2 is the selected cell). Take note that the cell reference does not contain $ (dollar) symbols, or include a specific worksheet name. This enables the relative named range to be used on any worksheet and any cell in the workbook.

The screenshot above shows a relative named range called *CellAbove* which references the cell above the selected cell.

Wherever the name *CellAbove* is used it will always refer to the cell above (it’s not just a clever name). We can include this into our rolling total formula, whilst also maintaining the structured referencing.

The formula in Cell E2 is:

=SUM([@In],-[@Out],CellAbove)

The result of this formula is a non-volatile running total in each row of the Balance column. Even if a row is deleted the calculation will continue to be the rolling total.

# INDEX Function

There is more than one way to create a running total. So far all the options have considered adding the movement onto the previous running total. Summing all the previous movements would achieve the same result.

By using the INDEX function, it is possible to reference any cell within a column.

Advertisement:

**The formula below references the first cell of the [In] column.**

=INDEX([In],1)

Placing a colon between two cell references (known as Range Operator) will create a range.

The formula in Cell E2 is (containing both Range ( : ) and Union ( , ) Operators)

=SUM(INDEX([In],1):[@In],-INDEX([Out],1):[@Out])

The result of this formula is a non-volatile running total in each row of the Balance column. Which, as I’m sure you’ve guessed, will continue to work even if a row is deleted.

## AGGREGATE Function

As Tables include a filter, it would be great to include a running total which only included the visible cells. None of the options considered so far would be suitable for this. So, let’s welcome the AGGREGATE function. It was introduced in Excel 2010 and can be set to ingore hidden rows.

The formula in Cell E2 is:

=AGGREGATE(9,5,INDEX([In],1):[@In])-AGGREGATE(9,5,INDEX([Out],1):[@Out])

The AGGREGATE function is set with the following parameters:

- Function_num: 9 = SUM
- Option: 5 = Ignore hidden cells

The running total works the same as before, but with some added power. If the column is filtered, the rolling total will only show the visible cells. BOOM!!! How good is that.

## Which one to use?

There you have it, lots options to achieve the same result. But which one to choose?

I have discounted the options which cause errors when rows are added or deleted. Of the remaining options, the average calculation times over 10,000 rows are:

- OFFSET: 0.0264 seconds
- Relative Named Range: 0.0244 seconds
- INDEX: 0.3654 seconds
- AGGREGATE: 1.7573 seconds

Advertisement:

Therefore relative Named Ranges appear to be the fastest option. AGGREGATE is the one to use when you have specific needs, to exclude hidden cells.

Which ever option you choose, if the spreadsheet is getting a bit slow try another method, and calculate the times form yourself. Check out my post on how to build a Calculation Timer Add-in.