How to Tally a Balance Sheet in Excel

Get FREE Advanced Excel Exercises with Solutions!

For every kind of business, making a balance sheet is mandatory. Because it gives you a clear idea and output of your business. And if any mistake occurs in the calculation then the balance sheet can detect it. I hope, this article will be a proper guide to tally a balance sheet in Excel with vivid illustrations.


Download Practice Workbook

You can download the free Excel workbook from here and practice on your own.


What Is a Balance Sheet?

A balance sheet is a financial document that can express the connection between a company’s assets, liabilities, and shareholder equity at a specific time. It helps to calculate a company’s net worth. Along with the cash flow statement and income statement, it is another important statement that a company should keep.

Here is our sample balance sheet, in which I’ll show how to tally between the liabilities and assets using a company’s data.


Steps to Tally a Balance Sheet in Excel

Calculate Liabilities

Liability is the amount or obligation for which a company is liable. First, we’ll calculate the total liabilities amount. There are three sections in this section. If you have more sections then add them here.

Steps:

  • There is the capital of two persons, so we’ll find the total capital using the SUM function. Apply the following formula in Cell D7
=SUM(C6:C7)
  • Hit the Enter button and you will get the total.

How to Tally a Balance Sheet

  • Then place the loan amount. As there is only one loan amount, so we’ll insert it directly as the total in Cell D9. So, type the following formula and hit the ENTER button.
=C9

How to Tally a Balance Sheet

  • Now, we’ll calculate the other current liabilities. I have shown three current liabilities, if you have more then add them in this section. To sum them up, insert the following formula in Cell D13
=SUM(C11:C13)
  • Later, press the Enter button to get the total.

How to Tally a Balance Sheet

The amounts of all the section’s liabilities are calculated. Now let’s find the total liabilities amount.

  • In Cell D18, write the following formula-
=SUM(D6:D13)
  • Then just hit the Enter button to finish.

Read More: Balance Sheet Format of a Company in Excel (Download Free Template)


Calculate Assets

Assets are the resources that a company holds, they can be property, equipment, cash, etc. Now we’ll calculate the total assets. It has also three sections. If you have more sections then add them here.

Steps:

  • In the Motor Vehicle section, there is a depreciation so to find the net amount we’ll subtract it from the Opening Balance using a manual formula. The formula will be as follows-
=G6-G7
  • Hit the Enter button to find the output.

How to Tally a Balance Sheet

  • There are depreciation and addition in the next Office Equipment section. So we’ll add and subtract them from the Opening Balance to find the net amount here. Type the formula as follows-
=G9+G10-G11
  • Don’t forget to press the Enter button to get the output.

How to Tally a Balance Sheet

  • We have some current assets here like, cash in hand, bank accounts, etc. Add them using the following formula-
=SUM(G13:G16)
  • Press the Enter button to get the summed amount.

How to Tally a Balance Sheet

  • All the individual assets are calculated. After that, apply the following formula to calculate the overall assets-
=SUM(H6:H16)
  • Later, just press the Enter button.

Read More: Balance Sheet Format in Excel for Proprietorship Business


Calculate Difference

If the difference between the liabilities and assets gets zero then we can say that we haven’t done anything wrong in our calculations, everything is all right. But if it becomes zero that means there’s something wrong in the data or calculations. Let’s check it for our dataset.

Steps:

  • Subtract the liabilities from the assets by inserting the following formula-
=H18-D18
  • Finally, just hit the Enter button to finish.

Now have a look, it returns zero. So we have done nothing wrong.

How to Tally a Balance Sheet

Read More: How to Make Projected Balance Sheet in Excel (with Quick Steps)


Things to Remember

  • Make sure you have inserted the right data.
  • Make sure about the amount before inserting that it’s a liability or asset.
  • The difference must be zero for a perfect calculation.

Conclusion

I hope the procedures described above will be good enough to tally a balance sheet in Excel. Feel free to ask any question in the comment section and please give me feedback. Visit ExcelDemy to explore more.


Related Articles

Md. Sourov Hossain Mithun
Md. Sourov Hossain Mithun

Hello! I am Md. Sourov Hossain Mithun. Currently, I am working at Exceldemy as an Excel and VBA Content Developer. Excel is an amazing software to learn or work. Here, I will post Excel related useful articles. I am a graduate of Bangladesh University of Engineering and Technology. I love to learn new things and work with them. Thank you.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo