Method 4: Running totals/balance using tables with structured references
Running totals
In this tutorial:
Step 1: Open tab named Method 4 – Sales. Follow the first three steps of Method 3 to convert the range in tables.
Step 2: In the cumulative sales column type in the formula: =SUM(INDEX([Sales],1):[@Sales]) and press Enter.
Formula has three things to understand (I will keep the details low):
- Sum function – simply summing the range as provided by: INDEX([Sales],1):[@Sales]. Notice that range is denoted by two parts on either side of colon.
- [Sales], [@Sales] – these are new style of referencing called structured reference and is used exclusively in tabled data. [Sales] represents sales column and its values. There are a lot of things to discuss about this referencing system but for now that is all we need to know.
- Index function – within sum function provides a references of cell number from where to start the sum. It is written as INDEX([Sales],1 which means simply start from the first row of column with the name “Sales”
Step 3: Enjoy the totals running for you at your finger tips! If you include another record at the end of table then excel will correctly calculate the running totals for you.
Running Balance
Step 1: Open tab named Method 4 – Cash. Follow the first three steps of Method 3 to convert the range in tables.
Step 2: In the Balance column type in the formula: =SUM(INDEX([Debit],1):[@Debit])-SUM(INDEX([Credit],1):[@Credit]) and hit enter and you will get the results in positive and negatives
Step 3: Now you can format the positive and negative values as debit and credit balances using the custom format options you learnt above.
Can’t run to the end of the world!
Excel is all about flexibility and variety. There are for sure other methods available. One such method is using Pivot tables to do the running totals and running balance ledgers and consider at as method 5 but I haven’t explained it yet as there are some grounds to cover before we embark on understand Excel’s yet another super power pivot tables.
So here you have the methods to have totals and balances running and flowing the way you want them as per your convenience. Let me know if you have other ideas to do running totals and running balances and it will add to my and other dear readers’ knowledge. Good luck and happy running totally and balancing and keeping in excelling at Excel! 😀
