ExcelInsider Team
In the dual-entry system, balancing using debit and credit is a common practice. If you are creating a cash flow statement, you must learn the debit credit balance sheet excel formula. In this article, we will show you all the ways you can balance the debit and credit in your Excel sheet.
➤ Write this formula in the desired cell:
=C3-D3+E2
➤ Replace C3 with the debit, D3 with the credit, and E2 with the opening balance.
➤ Autofill other rows in the column from the current row.
That was a quick and easy way to calculate the debit-credit balance. However, there are more ways to do this with better precision. If you want to know all the ways to calculate debit and credit balances in an Excel sheet, read the whole article. Before starting, don’t forget to download the example workbook so that you can follow the exact steps we show.
Using Mathematical Expressions to Calculate Debit/Credit
In this tutorial, we are using a dataset with the transactions for the first few days of a month. There are dates, descriptions of the transactions, the debits and credits, and of course, the balance. However, there is only the opening balance, and we have to calculate the rest. For this method, we will use simple mathematical expressions like plus (+) and minus (–) to do so. Let’s begin:
➤ The first cell to insert the balance is E2. Therefore, write the formula in that cell:
=C3-D3+E2
➤ If we autofill the rest of the cells (E3 to E12), the cell references will be automatically replaced with the subsequent cells, and the previous balance will be added instead of the opening balance.
Making Use of the SUM Function
The SUM function can be used to add values from a range or an array of cells. In calculations, if we add a negative value, it is subtracted even if we are actually adding those values. We are going to use that property and make use of the function to add debits and subtract credits. Here is how to do that:
➤ Use the following formula to calculate the balance:
=SUM(E2,C3,-D3)
➤ Autofill the rest of the rows.
Combining SUM and OFFSET Functions
Mistakes happen while journaling, and you might find yourself in a position where you haven’t added a transaction that you were supposed to. In other methods, if you insert a row, the formulas will fail to do the calculations for the rest of the rows properly. To fix that, we can combine the SUM and OFFSET functions. Follow the steps below:
➤ You already know this part. Use the formula in the cell where you want the balance to show up, and autofill:
=SUM(C3,-D3,OFFSET(E3,-1,0))
Finally, 0 is for the column as we don’t need to change the column and be in the current one. The SUM function then sums up the debit (C3), credit (-D3), and the current balance.
Calculating the Balance in a Table
Using a table to do a balance calculation is probably the best method to do this. There are almost no downsides to using this, and you can copy the formula to any cell without having to depend on Excel to change the cell references properly. Follow the instructions below:
➤ Select the whole range, including headers, and press Ctrl + T .
➤ Click OK to create a table.
➤ Write this formula in the E3 cell, and autofill the rest of the cells like the other methods.
=SUM(E$2,INDEX([Debit],1):[@Debit],-INDEX([Credit],1):[@Credit])
➥The INDEX function is similar to the OFFSET function, except that it does not use a starting point but a range. The ranges used in this formula are the debit and credit columns, and INDEX returns the current cell reference that SUM uses for the calculation.
Frequently Asked Questions
How to calculate balance from debit and credit?
It depends on what you are using to do the balance. For assets, the credit needs to be subtracted from the debit, and for liabilities, the debits should be subtracted from the credit.
How to find debit credit?
If you have a journal, the debits are on the left, and the credits are on the right. At the end of the journal, the debits and credits must match. If it does not, there is an issue with the journal.
What is an example of a DR and CR?
Imagine you bought a computer for your business for 300$ dollars. In the journal, the debit entry will be Office Equipment/Computer. If you bought the computer in cash, the cash will be credited, and the credit will be Accounts Payable.
What is the format of the balance sheet?
In a balance sheet, the closing balance in assets should be the same as the sum of liabilities and owners’ equity.
What is the formula for account balance?
The account balance formula or the accounting equation is Assets=Liabilities + Owner’s Equity. In Excel, if you have the assets in A1, Liabilities in B1, and Owner’s Equity in C1, you will check the balance like this:
=A1=B1+C1
The formula will return TRUE if the equation matches, FALSE if it does not.
Wrapping Up
In this article, we have learned the debit credit balance sheet Excel formula. We sincerely hope that we were able to clear all your confusions regarding the calculations. If you have any questions, leave them below, and we will get back to you soon.
