Running totals and Running balance in Excel – How to

On daily basis accountants prepare day books and ledgers as regular part of accounting cycle. Usually students in the beginning learn to prepare accounts in T format. However, professional accountants maintain accounting books in dedicated accounting software in which bookkeeping is a little different.

If accounts are maintained using T format then user can know the account balance only if it is closed which is usually done at the end of period. However, now a days ledgers are maintained in running balance format.

There are many ways in which you can make running balance or running total ledgers in Excel using simple to advanced features of Excel for example:

• using simple formula on normal data
• using tables with simple formula
• using tables with formula based on structured references
• pivot tables

And I am pretty sure there are lot other ways as well. But lets start with the most simplest method to make running balance/total and later in the article I will discuss other methods as well and how each method is better than the earlier ones.

Difference between Running totals and Running balance

Running total means that amounts are totaled after every transaction. This concept applies in situations where transactions are of same nature so amounts are only to be added up. For example sales, purchases etc. Sales day books and Purchases day books are usually prepared with running totals at the far right of the data.

Running balance is a little different from running totals in a sense that balance is the resultant of debit and credit entries in certain ledger. For example cash book has running balance that is a net of receipts and payments made so far.

Method 1 – Simplest method

Running totals

Step 1: Make sure tab name: Method 1 – Sales is active. With sales data already present in the first two columns A and B, in the third column (column C) where we will have cumulative sales, in cell C2 type in formula: =B2. This will give the starting value to cumulative balance.

Step 2: In cell C3 type in the formula: =C2+B3 and press Enter. It will give the result 300. Basically we are adding the previous cumulative balance with new transaction and resultant is total sales with all the previous sales and current sales.

Step 3: To fill in the same formula in the following cells you can simply drag the contents of cell C3 by holding cell handle or even better double click the handle and it will fill down the data automatically for all the rows. As the cell address in the formula are relative so excel will auto update address in formula while moving down

Step 4: Review the result to check if there are any errors, which I hope must not be there.

Step 5: Give a tap to yourself as you have excelled and learnt how to make running totals in excel 🙂

Running balance

Analyse the data that this is one has debit and credit columns where sales data had only one column. Debit column represent cash receipts and credit column represent cash payments. In accounting debits and credits are opposite and thus sets-off each other.

Step 1: Open tab named Method 1 – Cash. With cash data already present in the first three columns A, B and C, in the fourth column (column D) where we will have balance of cash after each transaction, in cell D2 type in formula: =B2-C2. This will give the starting value.

Step 2: In cell D3 type in formula =D2+B3-C3. Principle is still the same as it was running totals with only difference that now we are deducting the values in credit column (column C) while working out the resultant value.

Step 3: Fill all cells for remaining data by dragging fill handler or double clicking it and you will have running balance in a snap. You can change values to see how running balance change on their own immediately.

Step 4: The resultant are in positive and negatives. However, in accounting the balance is either debit or credit and debit balance is usually denoted by Dr following the number and credit balance is represented by Cr at the end of number. To get this we have use custom formatting. Select “Balance” column and hit Ctrl+1 combo. Format cell dialogue box will open.

Step 5: Make sure number tab is active. From the list at left click “Custom”

Step 6: In the Type field on the right inside format cell dialogue box remove anything that is written (by default it is general) and type in the following and click OK button.

0 "Dr";0 "Cr";0

Now you have running balance ledger with balance being shown in Debits and Credits.

Problems with simple method

Two of the main problems are:

1. If you delete a certain row of data then the data calculation will be break for next rows in both running totals and running balance calculations and gives #REF! error which is reference error that is due to missing reference as row has been deleted. In real life we might have to delete certain data and this is not affordable. Method 2 given below solves this problem easily.
2. Transactions occur on daily basis and so does the related data. However, if we have used simple data then every time a new entry is made you manually have to extend the running total/balance content to get the result. This is cumbersome as now a days we extract thousands of transactions from the source and simply paste it in excel to have calculations done. So if the results can be automated then it will increase our productivity many times. Method 3 given below gives the solution for this issue.

Method 2 – Simple but Better Method

Running totals

Step 1: Open tab named Method – Sales. With sales data already present in the first two columns A and B, in the third column (column C) where we will have cumulative sales, in cell C2 type in formula: =SUM($C$2:C2). This will give the starting value to cumulative balance.

Step 2: Fill the data down for the rest of rows by dragging down fill handler or double clicking it. Formula consists of a range with one part static and the other relative and when it is filled downwards the relative part will update automatically

Running Balance

Step 1: Open tab named Method 2 – Cash. With cash data already present in the first three columns A, B and C, in the fourth column (column D) where we will have balance of cash after each transaction, in cell D2 type in formula: =SUM($B$2:B2)-SUM($C$2:C2)

Step 2: Fill the rest of cells by dragging down the fill handle or double clicking it and formula will populate in rest of the cells.

Step 3: By default results are in positive and negative figures to have them in debit and credit as discussed before simply follow the steps learnt to do this via custom formatting in simple method’s Step 4 – Step 6 under running balance method.

Problems with Method 2

Although it solves the problem of #REF errors and now if in between rows are deleted the results don’t break and calculation still occur flawlessly for rest of the data. However, it does not solve our automation problem i.e. calculations are not carried out automatically on each new record input. To solve this issue we have our third method.

Method 3: Running totals/balance with Excel Tables

Tables are one great thing happened to excel back in 2007 and with every new release of excel’s version this feature is getting stronger and better. One of the coolest thing about tables is that they grow as your data grows and if you have formula placed inside table then Excel handles it quite well (most of the time) and spares us the hassle of updating the formula or copying/pasting formula down the rows etc. So we can utilize tables to achieve the automation.

However, there are pitfalls in the methods we have learnt so far and when combined with tables then new old/new problem can arise.

Running totals/balance – using method 1 and 2 with tables.

Step 1: Open tab named Method 3 – Sales if you want running totals. If you like to do running balance then open tab named Method 3 – Cash. Select the whole range of data by having an active cell within data and hitting Ctrl+A shortcut. This will select Months column and also Sales column.

Step 2: Convert the data in to tables either by hitting Ctrl+T shortcut or from the ribbon above clicking Insert tab>Table button in the tables group.

Step 3: Create table dialogue box will appear asking for the range of data to be converted to tables and if data has headers. By default it selects the whole range of data. Make sure “my data has headers” option is checked and click OK. This will convert the whole data in tables that is preformatted and number of other options enabled specifically for this table alone.

Step 4: In the first cell of cumulative sales column type in the formula you wish you learnt in above methods. The excel will paste the same formula for all the rows immediately and save you the hassle of copying the formula down manually.

Problem: If you use method 1 with tables, everything will work fine and even if you include new records. But method 1 still haunts us with #REF! error and also to escape the tables’ auto calculate is a bit tricky. Method 2 worked flawlessly if we had to do things manually but when used with tables its behaviour is unpredictable as data grows with new record inputs.

In essence method 3 fails miserably! However, this is not the end.

Solution to all these problems is using tables’ own referencing system called structured references which is our Method 4

Method 4: Running totals/balance using tables with structured references

Running totals

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! 😀