We’ll use the following dataset to create a balance sheet for a bank account.
Example 1 – Apply a Formula with the SUM Function to Create a Debit-Credit Balance Sheet
Steps:
- Select cell E6.
- Insert the following formula and hit Enter.
=SUM(E5+C6-D6)
- Drag the Fill Handle to the remaining cells in the column.
- Here is the debit credit balance sheet in Excel using the SUM function
Read More: How to Keep a Running Balance in Excel
Example 2 – Use a Formula Combining SUM and OFFSET Functions
Steps:
- Select cell E6.
- Copy the following formula and paste it into the formula bar, then press Enter.
=SUM(C6,-D6,OFFSET(E6,-1,0))
- Drag down the Fill Handle to the remaining cells.
- Here is the balance sheet.
Read More: Calculate Debit Credit Running Balance Using Excel Formula
Example 3 – Combine SUM and INDEX Functions
Steps:
- Select the array B4:E14.
- Go to the Insert tab and hit Table. A Create Table dialog box will pop up.
- Recheck the selection (In this example, $B$4:$E$14), and select the My table has headers option.
- Click OK.
- This will create a table as shown in the image below.
- Select cell E5.
- Copy the following formula in the formula bar and press Enter.
=SUM(INDEX([Credit],1):[@Credit]-INDEX([Debit],1):[@Debit])
Read More: How to Calculate Running Balance Using Excel Formula
Download the Practice Workbook
Related Articles
- How to Make Stock Balance Sheet in Excel
- How to Prepare Balance Sheet from Trial Balance 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!