Debit Credit Balance Sheet with Excel Formula (3 Suitable Examples)

Get FREE Advanced Excel Exercises with Solutions!

Maintaining a debit credit balance sheet of your checking and credit accounts is one of the most essential chores in personal finance. The ancients may have made certain math mistakes when recording a debit credit balance sheet. Our current spreadsheets may appear vastly superior when we use Excel formulas to do the math, but it may also lead to other types of errors. Here are 3 handy ways to create a Debit Credit Balance Sheet with Excel Formula.


Create Debit Credit Balance Sheet with Excel Formula: 3 Effective Examples

Let’s say you have an account at a bank. The bank in column C and column D tells you what balance you should deposit and withdraw from your account. Excel formulas named SUM, OFFSET, and INDEX will be used to calculate the balance sheet. Let’s introduce our dataset first. Our goal is to create a debit-credit balance sheet with an Excel formula.

Create Debit Credit Balance Sheet with Excel Formula


Example 1: Apply a Formula with SUM Function to Create Debit Credit Balance Sheet

Using the SUM function, we can calculate the debit-credit balance from our dataset. The SUM function in Excel is the easiest and fastest way to create a debit-credit balance sheet. Please follow the steps below to apply the function.

📌 Steps:

  • First, select cell E6. Type the following formula in the formula bar, then, press ENTER.
=SUM(E5+C6-D6)

Apply a Formula with SUM Function to Create Debit Credit Balance Sheet with Excel Formula

  • Now, drag the Fill Handle to the remaining cells.

Apply a Formula with SUM Function

Here is the debit credit balance sheet in Excel using the SUM function

Apply a Formula with SUM Function

Read More: How to Keep a Running Balance in Excel


Example 2: Use a Formula Combining SUM and OFFSET Functions

The OFFSET function allows you to generate a reference by specifying the number of rows and columns to offset. Using our dataset, we will calculate the debit credit balance sheet in Excel using the OFFSET function. Just follow the steps below.

📌 Steps:

  • First, select cell E6. Copy the following formula and paste it into the formula bar, then press ENTER.
=SUM(C6,-D6,OFFSET(E6,-1,0))

Use a Formula Combining SUM and OFFSET Functions to Create Debit Credit Balance Sheet with Excel Formula

  • Now, drag down the Fill Handle to the remaining cells.

Use a Formula Combining SUM and OFFSET Functions

Finally, here is the balance sheet in  Excel using the OFFSET function

Use a Formula Combining SUM and OFFSET Functions

Read More: Calculate Debit Credit Running Balance Using Excel Formula


Example 3: Combine SUM and INDEX Functions

The last way is to calculate the debit credit balance using the SUM function and INDEX function. Just follow the steps below to apply these 2 functions to create a debit-credit balance sheet in Excel.

📌 Steps:

  • First, to create a table with the dataset, select the array B4:E14. Then, go to the Insert tab >> Tables >> Table. A Create Table dialog box will pop up.

Combine SUM and INDEX Functions

  • Now, check the selection (In this example, $B$4:$E$14), and select the My table has headers option. Finally, click OK.

This will create a table as shown in the image below.

Combine SUM and INDEX Functions

  • Now, select cell E5. Copy the following formula in the formula bar and press ENTER. It will automatically balance all the cells of the Balance column.
=SUM(INDEX([Credit],1):[@Credit]-INDEX([Debit],1):[@Debit])

to Create Debit Credit Balance Sheet with Excel Formula to Create Debit Credit Balance Sheet with Excel Formula

Read More: How to Calculate Running Balance Using Excel Formula


Download Practice Workbook

You can download the practice workbook that we have used to prepare this article.


Conclusion

In this tutorial, I have discussed 3 handy ways to create a debit-credit balance sheet in Excel with a formula. I hope you found this article helpful. Please, drop comments, suggestions, or queries if you have any in the comment section below.


Related Articles


<< Go Back To How to Make Balance Sheet in Excel |Excel For Finance | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Hafizul Islam
Hafizul Islam

Hafizul Islam is an excellent marine engineer who loves working with Excel and diving into VBA programming. For him, programming is like a superhero tool that saves time when dealing with data, files, and the internet. His skills go beyond the basics, including Rhino3D, Maxsurf C++, AutoCAD, Deep Neural Networks, and Machine Learning. He got his B.Sc in Naval Architecture & Marine Engineering from BUET, and now he's switched gears, working as a content developer. In this role,... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo