How to Make Automatic Balance Sheet in Excel

Get FREE Advanced Excel Exercises with Solutions!

Excel is widely used to make financial statements of any company or business whether it is a big one or a small one. We can use different Excel functions and formulas to get Assets, Liabilities, Incomes, Expenditures, etc. from a dataset of financial statements. In this article, we will show you the procedure to make an automatic balance sheet in Excel.


Why Do We Need Automatic Balance Sheets in Excel?

The financial statements of any company or business can be huge. So it’s quite tough to find the Profit and Loss or asset and liability at a glance. But it can be easily found by using Excel. Later on, we can change the input data and get the result values instantaneously.


Procedure to Make Automatic Balance Sheet in Excel

Making an automatic balance sheet is quite simple in Excel. We can make an automatic balance sheet following a few steps. The steps are discussed below.


Step 1: Produce a Tabular Form Structure

First, we need to create a table for the balance sheet. 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 in turn help to separate debit and credit.

automatic balance sheet in excel

We need to make Profit & Loss, Balance Sheet table also.

Besides that, we need to add an extra table for the Category Name and Category Type.

automatic balance sheet in excel dataset

Read More: How to Make a Forecasting Balance Sheet in Excel


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

In the table, we have to provide the Category first. Sales are income for the business, purchases, and different bills are expenses. Cash in Hands and bank Balance are assets for the business. Accounts payable and capital amount are liabilities. So we categorized them accordingly.

automatic balance sheet in excel step 1

Then we need to write 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.

Then we need to press ENTER and use the Fill Handle to copy the formula in the cells below.

automatic balance sheet

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

Read More: How to Create Common Size Balance Sheet in Excel


Step 3: Provide Input into Fields

In this step, we will provide our input data in the table. Credit is money flowing out of the account so we inserted Income and Liability in the Credit column whereas Expense and Asset are inserted in the Debit column cause debit means the money flow in the account.

automatic balance sheet step 2

Read More: Rental Property Balance Sheet in Excel


Step 4: Calculate and Verify Balance

Now we will calculate and verify our balance. The steps are given below.

Below the Debit column in cell D17, we want to have the sum of debits. For that, we need to write the following formula in the cell.

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

We need to 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 and they match.

Now we will make the balance for each input. To do that we have to write the following formula to the 1st cell of 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.

Now we need to 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 is shown as a positive value.


Step 5: Split Summary of Balance

This time we will show the steps to calculate the Profit and Loss and Asset and liability.

We have to select the cell where we want to see the total income. In our case the cell is J5. Then we need to write 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.

automatic balance sheet profit loss

Now we have to press ENTER.

We can see the total income.

We will calculate the total expense now. To do that we need to select the cell to display the result. In our case the cell is J11. We have to write 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.

We have to press ENTER.

We can see the total expense now.

Here we will calculate the net profit. To do that we need to select the cell and write the formula given below there.

=J5-J6

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

We need to press ENTER.

We can see the net profit or loss.

This time we will calculate total assets and total liabilities. To calculate the total assets we need to select the desired cell. In our case the cell is J11. We have to write 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.

We have to press ENTER now.

We can see the total assets in the cell.

Now we will calculate the total liabilities. For that, we need to select the desired cell 1st and write the following formula 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 have to press ENTER.

We can see the total liabilities in the cell.

Now we will calculate the net profit/loss on the balance sheet. for that, we need to select the desired cell to show the result and write the following formula there.

=J11-J12

We used the subtraction value of cells J11 and J12.

Now we need to press ENTER.

We can see the net Profit/Loss here which is the same as the profit calculated from total income and expenses.

In this step, we will calculate the total liabilities. We have to select the respective cell first and write the following formula there.

=J12+J13

Now we need to press ENTER.

We will see total liabilities there.

Read More: How to Create a Balance Sheet for Small Business in Excel


Download Practice Workbook

You can download the practice workbook from here.


Conclusion

Making an automatic balance sheet in Excel can reduce a lot of hard work and save valuable time. We need to be careful about the data entry because one wrong piece of information can cause a chain of errors in the balance sheet. In this article, we have shown the procedures to make an automatic balance sheet in Excel. If you have faced any problems regarding the procedures.


Related Articles


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

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.
Mehedi Hasan Shimul
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

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo