We’ll use the following dataset, where deposits and withdrawals on the credit and debit accounts are put in column C and column D. We’ll calculate the running balance. The cell E5 contains the starting account balance.
Method 1 – Apply the SUM Function to Calculate the Debit-Credit Running Balance in Excel
Steps:
- Select cell E6 to calculate the running balance.
- Insert the following formula and hit Enter.
=SUM(E5+C6-D6)
- You’ll get $109,958.00 as the return of the SUM function in cell E6.
- Place your cursor on the bottom-right corner of cell E6. It will change into the Fill Handle sign.
- Drag the Fill Handle down to AutoFill the column.
- Here are the results.
Read More: Debit Credit Balance Sheet with Excel Formula
Method 2 – Use the OFFSET Function to Calculate the Debit-Credit Running Balance in Excel
Steps:
- Select cell E6.
- Insert the following formula:
=SUM(C6,-D6,OFFSET(E6,-1,0))To refer to the past balance, we use the current balance (E6) as a reference, which has and offset of [-1,0].
- Press Enter.
- AutoFill the formula by dragging the Fill Handle from cell E6 down.
Read More: How to Keep a Running Balance in Excel
Method 3 – Merge the SUM and INDEX Functions to Calculate the Debit-Credit Running Balance in Excel
Steps:
- We’ve modified the dataset to no longer contain the starting balance at the top.
- Select the cell array B4:D14.
- From the Insert tab, go to Table.
- A window named Create Table appears.
- Press the OK button from the Create Table window, and you will get a table with the headers.
- Select cell E5.
- Insert the following function in cell E5:
=SUM(INDEX([Credit],1):[@Credit]-INDEX([Debit],1):[@Debit])
- Press Enter to get the result. The table automatically fills all the cells in the column.
Download the Practice Workbook
Related Articles
- How to Prepare Balance Sheet from Trial Balance in Excel
- How to Make Stock Balance Sheet in Excel
- How to Make Projected Balance Sheet in Excel
- How to Make Profit and Loss Account and Balance Sheet in Excel
- How to Tally a Balance Sheet in Excel
- How to Make Trial Balance in Excel
<< Go Back To How to Make Balance Sheet in Excel |Excel For Finance | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
Very helpful. Thank you!!