Excel Article

Excel Tables (Part 5) - Running Totals

In this article we are going to look at producing a running total or running balance in an Excel Table.

In a plain table it is fairly easy to add a running total. Simply add a formula that takes the value from the row above, add the “In” and subtract the “Out”.

However we have to modify the formula slightly in the first data row.

This works reasonably well until you insert a new row to add new value; you get a break in the running total, which you have to fix, by dragging the formula from above the inserted row down to below the inserted row – in this example from row 5 down to row 7.

If you are inserting several rows, this can become quite tedious.

Now let’s look at adding a running total or balance to an Excel Table, starting with our example from the previous article

On any data row except the first one enter the formula we used in the plain table, so for example if we enter on row 6 this is the result.

It looks a bit of a disaster. The problem is in the first data row, we are trying to get a number from the header which has the word “Balance” in it. We could turn off the “Calculated Column” option, and then enter a different formula in the first cell. It would fix the problem but we lose the benefits of the calculated column when inserting new rows, and if we insert a new row in the first data row we could end up with a mistake that is hard to spot.

To solve this we need to modify the formula and do something different for the first data row. The formula is slightly complicated and can be tricky to enter but the end justifies the means; once you have it in place it is pretty robust.

It relies on 3 functions

Firstly we will use ISNUMBER which tests a value to see if it is a number, and returns True if it is, False if it is not. We are going to use this to see if the cell in the row above is a number or the column header.

Secondly we will use the IF function. This has 3 parts and in words works like this. IF(‘thing we are testing’,’do this if the test is true’, ‘do this if the test is false’)

Thirdly we will use the OFFSET function. This takes a reference and adjusts the row and column by the respective offset values. In words it works like this. OFFSET(‘reference’,’adjust reference row address by this amount’,’adjust reference column address by this amount’)

Here is our starting point with an added Opening amount of 5000 in the first row.

In cell J6, entering the following (not the double quotes)

Type “=if(isnumber(”, click on J5, Type “),”

Click on J5, type “,0)+”, click on H6, type “-”, click on I6

This is the result in Excel 2007

This is the result in Excel 2010

The key part of this equation is “=IF(ISNUMBER(J5),J5,0)” which is testing the cell in the row above to see if it is a number. If it is a number, it returns the number, if it is not a number is returns 0

However all is not perfect yet. If we delete or add a row, the formula breaks. For example if we insert a row above the Insurance item in row 6 we get this in Excel 2007

And this in Excel 2010

You will notice that the formula in the Insurance row is referring to 2 rows above not the row immediately above.

To fix this we need to change the two occurrences of the normal cell reference - J5 - to a table style references, and we are going to use the OFFSET function to do this referring to the cell above by making the row offset -1, and the column offset 0 (no change in column).

Replacing the formula is awkward, but the end result is worth it, so stick with it. Using our working example above, select the formula in cell J6, and in the formula bar highlight the first J5

In Excel 2007

In Excel 2010

You can then either replace it by typing in the offset formula directly or as in the following steps.

Type “offset(“

Click on cell I5 – note this is the right row but wrong column (you can't click on the cell you are editing). We will fix this very soon.

Type “,-1,0)”

This is the result so far in Excel 2007

This the corresponding result Excel 2010

Then highlight the wrong column reference in the formula, in our example “Out” in Excel 2007

Selected Out in Excel 2010

Change the highlight “Out” to “Balance”. Excel will help as you do this by giving you the column names in a drop down list, so you won’t have to type the whole word “Balance”.

The result with Out changed to Balance in Excel 2007

And the result in Excel 2010

Repeat for the other occurrence of J5 and this is the finished formula in Excel 2007

The finished result in Excel 2010

Now when we add or delete rows from the table, the formula doesn’t break

Lastly a slight variation is to put the opening balance above the table. This is achieved by adjusting the formula changing the last parameter in the IF function from 0 to J$2. Note that we need to make the row an absolute address by adding the dollar. This is the result in Excel 2007

The alternative in Excel 2010

It cannot be stressed enough that the pain spent in getting this formula entered correctly is greatly compensated by the benefit of having a formula that works as you add and delete rows into the table. It is a lot more robust than the plain table version, although with one caveat, you can still delete the formula in one of the cells, so a little bit of care is still needed.

The only design concession is the column header cannot be a number, which seems pretty reasonable.

Screen shots created in Excel 2007 and Excel 2010 where stated, all on Windows 7