Five 'Running Total' Methods
Running totals, such as a balance on a bank statement seems like something Excel should provide, out-of-the-box, but when you try to implement them you realise that a little more thought is required in order to deliver the results you want.
Since 2005 I have come up with five different ways to create running totals, all of which have merit. It may be that you end up with a method of your own, perhaps including elements from one or more of the methods below, which I have named as follows:
1: Simple
My first attempt at Running Totals took the balance from the previous line and added the credit less the debit from the current line. A very simple formula, "F5+D6-E6", can be copied to all the cells below it to show the balance for each line; but it doesn't work in the top line, since there's no previous line. Instead, that formula needs to read, "Opening Balance+D5-E5".
2005 was before Tables were introduced to Excel; and most users accepted the need to regularly recopy formula when rows were being added, deleted or reorganised; but additionally, with this method, I needed to remember always that the top formula was different.
2: Anchored Sums
In 2005, having prematurely given up on the simpler method, I found several online forums promoting this method, which challenged the normal 'human' approach and delivers a formula that can be copied to all rows, including the top one. The balance on each line is calculated as:
The Opening Balance
Plus the sum of the credits between the top and current line
Less the sum of the debits between the top and current line
And that's what the formula shown is doing. Here we're looking at the calculation for the first balance. The range of the SUM statements is just one line, but when this is copied down, because of the $ anchors, the SUM ranges are extended.
Even with the computing power of 2005 Excel can handle lots of SUM calculations, but it's important to note that each new line not only creates two more sums to calculate, but the sizes of the ranges being totalled also grows. This means processing time grows exponentially with the number of rows in the table, and this can lead to performance issues in large tables.
Also, again, there was a continued need to refresh the formula when new rows were added, deleted or rearranged. Particular attention needed to be paid that the anchored references in the formula (D$5 and E$5) always referred to the top row.
3: Excel Table
In 2007 Excel introduced Tables and this gave me the opportunity to part company with anchored sums. The INDEX function provided an easy way to lookup the balance from the previous line, and an IF statement allowed for a different calculation for the top line, yet still have a formula that could be copied to every row.
On the downside the formula covers ten lines (the first eight of which return either the Opening Balance or the balance from the previous line, depending on the row number. And the row number needed to be calculated twice in the formula.
Note. The remaining methods all use Excel Tables, but find ways to address some of the above issues.
4. Excel Table with Helper Columns
Helper columns can be used to break up more complicated formula into several small and easily understood formulas. And they can also improve performance issues, as typically the calculations in the helper columns will only need to be performed once.
In this example the columns "Row" and "BF" are the helper columns, which are in red as a reminder that they need to be hidden. The formula in the Helper and Balance columns are as follows:
These are smaller and more easily understood formulas, and is overall less code than in method 3. Moreover, the calculation for the row number only needs to be entered (and executed) once. However, having the calculation spread over three separate columns is not necessarily helpful.
5. Excel Table with LET Function
At the end of 2020 Excel release a 'LET' function that provided for the breaking down of complex formula, without using helper columns. It does this by allowing you to declare one or more 'variables' that can be used later on in the calculation.
.
In this example I have declared two variables. On the second line of the formula I have declared a variable called 'row', and told Excel how to calculate it. On the next line I have declared, 'BF', and in telling Excel how to calculate it I have made use of the row variable created on the second line. The final row doesn't have a variable name - just a formula, and so is the one Excel will use to set the cell value.
The formula is much simpler than the one used in Method 3, and isn't spread across three columns
Extending these Methods
All of the examples in this post show a starting balance and a small list of transactions, and this is sufficient to illustrate the different techniques that can be employed in providing running totals. In the real world, though, you may need to solve some other problems.
If a table becomes too long, how can it be broken up into pages?
If pages, then how can we ensure the bottom balance from one page is the starting balance for the next.
If there are rows for many different accounts, then how can one show just those for a selected account?
I plan to write posts to help in these matters. And by the time you have come to read this, they may well have been published. If not you can subscribe in the form at the bottom of this page to ensure you're notified of each new post - as well as comments on this post and discussions in the Forum.
Recommendation
Although Method 5 (Excel Table with LET function) is my favourite, each has it's own merit. In choosing, much will depend on other things going on in your workbook, and you may well end up using a combination of techniques from the above, together with some of your own. Should you require some help with this then Excel House provides a Consultancy Service.