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

### Step 1: Create a Table

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.

Make a Profit & Loss Balance Sheet table.

Add an extra table for the Category Name and Category Type.

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

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.

• Enter the following formula in the 1st cell of column Cr/Dr:
`=VLOOKUP(C5,\$L\$5:\$M\$8,2,FALSE)`

Here, VLOOKUP is an Excel function,

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.

• Press ENTER and use the Fill Handle to copy the formula in the cells below.

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.

Read More: Rental Property Balance Sheet in Excel

### 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.

• Enter the following formula into the 1st cell of the column Balance:
`=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.

• Press ENTER and use Fill Handle to copy the formula in the cells below.

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.

• Select the cell where we want to see the total income. In our case, the cell is J5.
• Enter the following formula there:
`=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.

• Press ENTER.

We can see the total income.

Calculate the total expense.

• Select the cell to display the result. In our case, cell J11.
• Enter the following formula in the cell.
`=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.

• Press ENTER.

We can see the total expense now.

• Calculate the net profit.
• Select the cell and enter the following formula:
`=J5-J6`

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

• Press ENTER.

We can see the net profit or loss.

• Calculate total assets and total liabilities.
• Select the desired cell. In our case, cell J11.
• Enter the following formula there:
`=SUMIF(C5:\$C\$16,"Asset",D5:D16)-SUMIF(C5:\$C\$16,"Asset",E5:E16)`

The formula is similar to the formula used previously.

• Press ENTER.

We can see the total assets in the cell.

• Calculate the total liabilities.
• Select the desired cell and enter the following formula:
`=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.

• Press ENTER.

We can see the total liabilities in the cell.

• Calculate the net profit/loss on the balance sheet.
• Select the desired cell to show the result and enter the following formula:
`=J11-J12`

We used the subtraction value of cells J11 and J12.

• Press ENTER.

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

• Calculate the total liabilities.
• Select the respective cell first and enter the following formula:
`=J12+J13`

• Press ENTER.

We will see total liabilities there.

Download the Practice Workbook

You can download the practice workbook from here.

## Related Articles

<< Go Back to Balance Sheet | Finance Template | Excel Templates

Get FREE Advanced Excel Exercises with Solutions!
Mehedi Hasan Shimul

Md. Mehedi Hasan, with a BSc in Electrical & Electronic Engineering from Bangladesh University of Engineering and Technology, holds a crucial position as an Excel & VBA Content Developer at ExcelDemy. Driven by a deep passion for research and innovation, he actively immerses himself in Excel. In his role, Mehedi not only skillfully addresses complex challenges but also exhibits enthusiasm and expertise in gracefully navigating tough situations, emphasizing his steadfast commitment to consistently deliver exceptional and quality content.... Read Full Bio

2 Comments
1. EXCEL TIPS FOR ACCOUNTS WORK
HOW MAKE THE BALANCE SHEET WITH AUTOMATE

• Hello, PREM! Hope you are doing well.
Thank you for your query.
Regarding your query, basically, the Balance Sheet comes after generating a journal, ledger, trial balance, income statement, and ownerâ€™s equity statement. So, to automate a balance sheet, you need to automate journal, ledger, trial balance, income statement, and ownerâ€™s equity statement first.
In this article, basically, we automated trial balance and income statements. An automated balance sheet will be uploaded soon too. Before that, you can go through our website to get individual automated sheets to generate your own balance sheet automatically.

With Regards,
Md. Tanjim Reza Tanim
Team Leader, ExcelDemy

Advanced Excel Exercises with Solutions PDF