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. If you are new to this term and want to learn this format read: What is Running balance method of maintaining ledgers and journals?
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.
If you have not download the file required for this tutorial yet then please download it from here: Running totals and Running Balance.xslx
Method 1 – Simplest method
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 🙂
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:
- 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.
- 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.