How to Make an Automatic Balance Sheet in Excel (Step-by-Step)

We need to create a balance sheet table. The table can be like the following, which includes columns Category, Debit, Credit, Balance, and Cr/Dr. In the Category, we will define the type of our input, which will help to separate debit and credit. automatic balance sheet in excelMake a Profit & Loss Balance Sheet table. Add an extra table for the Category Name and Category Type. automatic balance sheet in excel datasetRead More: How to Make a Forecasting Balance Sheet in Excel

Step 2: Provide Category and Checking Type in the Balance Sheet

automatic balance sheet in excel step 1

In the table, provide the Category first. Sales are income for the business, and purchases and bills are expenses. Cash in hand and bank balance are assets for the business. Accounts payable and capital amount are liabilities. We categorized them accordingly.

=VLOOKUP(C5,$L$5:$M$8,2,FALSE)

C5 is the lookup value,

$L$5:$M$8 is the table array,

2 is the column index number and

FALSE is a range lookup for an exact match.

automatic balance sheet

We can see the debit or credit type for each category.

Step 3: Put the Input into Fields

Credit is money flowing out of the account, so we inserted Income and Liability in the Credit column. In contrast, Expense and Asset are inserted in the Debit column because debit means money flowing into the account.

automatic balance sheet step 2

Step 4: Calculate and Verify Balance

Calculate and verify our balance.

We want the sum of debits below the Debit column in cell D17.

Enter the following formula in the cell:

=SUM(D5:D16)
Here, SUM is an Excel function, and D5:D16 is the range for sum.

Press ENTER and use the Fill Handle to copy the formula to the cell beside.

We can see the sum of all debits and credits in the bottom cells, which match.

We will make the balance for each input.

=IF(B5="Cr",E5-D5,D5-E5)

Here, IF is an Excel function,

B5=”Cr” is the criteria,

E5-D5 is the result if the criteria match,

D5-E5 is the result if the criteria don’t match.

We can see the balance for each input. Credit is shown as a negative value, and Debit as a positive value.

Step 5: Split the Summary of the Balance

Calculate profit and loss, as well as asset and liability.

=SUMIF($C$5:$C$16,"Income",E5:E16)-SUMIF($C$5:$C$16,"Income",D5:D16)

Here, we have taken the subtraction value of the 2 SUMIF function.

$C$5:$C$16 is the criteria range,

“Income” is the criteria

E5:E16 is the sum range.

automatic balance sheet profit loss

We can see the total income.

Calculate the total expense.

=SUMIF($C$5:$C$16,"Expense",D5:D16)-SUMIF($C$5:$C$16,"Expense",E5:E16)

Here, we used the subtraction value from 2 SUMIF functions.

$C$5:$C$16 is the criteria range,

“Expense” is the criteria

D5:D16 is the sum range.

We can see the total expense now.

=J5-J6

Here, we have used the subtraction value of 2 cells J5 and J6.

We can see the net profit or loss.

=SUMIF(C5:$C$16,"Asset",D5:D16)-SUMIF(C5:$C$16,"Asset",E5:E16)

The formula is similar to the formula used previously.

We can see the total assets in the cell.

=SUMIF($C$5:$C$16,"Liability",E5:E16)-SUMIF($C$5:$C$16,"Liability",D5:D16)

The formula is similar to the previously used formula.

automatic balance sheet asset

We can see the total liabilities in the cell.

=J11-J12

We used the subtraction value of cells J11 and J12.

We can see the net profit/loss here, the same as the profit calculated from total income and expenses.

=J12+J13

We will see total liabilities there.

Download the Practice Workbook

You can download the practice workbook from here.

Automatic Balance Sheet.xlsx

Related Articles