Balance Sheet Format in Excel with Formulas (Create with Easy Steps)

Get FREE Advanced Excel Exercises with Solutions!

This article illustrates how to create a balance sheet in a proper format in Excel with formulas. A balance sheet contains a summary of the financial amounts of a company or organization. Usually, it contains the assets, liabilities, and equities associated with that organization. A balance sheet is necessary to quickly assess the progress or the financial stability of a company. So, follow this article to learn how to make a balance sheet in a proper format in Excel with formulas.


Steps to Create a Balance Sheet Format In Excel with Formulas

Carry out the following steps to create your balance sheet in a proper format in Excel with formulas.

📌 Step-1: Make a Proper Heading for Balance Sheet

  • First, type the name of your company in cell B2. Then write “Balance Sheet” in cell B3. Next, enter the date in cell B4. Now merge cells B:C for rows 2 to 4. Then, apply the Bottom Border from the Home Finally, the balance sheet heading will look as follows.


📌 Step-2: Insert Balance Sheet Components

  • A balance sheet has Asset, Liabilities & Shareholder’s Equity. We will enter these components in column B. On the other hand, the corresponding amounts will be entered in column C.


📌 Step-3: Format the Amount Column

  • It is better if you format the Amount column before starting to enter data into the balance sheet. First, select column C by clicking on the column number at the top. Then, press CTRL+1 to open the Format Cells dialog box. Now choose the Accounting number format with 2 decimal places. Make sure the $ is visible. Then click OK.

Read More: How to Create Tally Debit Note Format in Excel


📌 Step-4: Insert Current Assets

  • Now we will enter the current assets into our balance sheet. Usually, assets that have a period of less than one year are considered current assets. For example, Cash and Equivalents, Account Receivable, Inventory, etc. Enter these components in cells B7 to B9. Then, enter the corresponding amounts in cells C7 to C9
  • After that, enter the following formula in cell C10 to calculate the total current assets.
=SUM(C7:C9)

Balance Sheet Format in Excel with Formulas


📌 Step-5: Input Fixed Assets

  • Now we will enter the fixed assets (Plant & Equipment) in cell B11 and the corresponding amount in cell C11. The depreciation amount must be entered as a negative amount. Notice the depreciation amount is inside brackets indicating a negative number.
  • Next, enter the following formula in cell C13 to calculate the Net Fixed Assets.
=SUM(C11:C12)


📌 Step-6: Calculate Total Assets

  • Now, enter the following formula in cell C14 to calculate the Total Assets (= Total Current Assets + Net Fixed Assets).
=SUM(C10,C13)

Balance Sheet Format in Excel with Formulas

Read More: Revised Schedule 3 Balance Sheet Format in Excel with Formula


📌 Step-7: Input Current Liabilities

  • Now, enter the current liabilities ( Accounts Payable, Short-Term Notes Payable, etc.) in cells B16 to B18 and the corresponding amounts in cells C16 to C18
  • Then, enter the following formula in cell C19 to get the total current liabilities.
=SUM(C16:C18)


📌 Step-8: Estimate Total Liabilities

  • Now, enter the long-term liabilities (Long-Term Debt, etc.), and their corresponding values. Then, enter the following formula in cell C21 to get the total liabilities.
=SUM(C19:C20)


📌 Step-9: Calculate Total Shareholder’s Equity

  • After that, we need to enter the Shareholder’s Equity (Common Stock, Treasury Stock, Retained Earnings, etc). These are also known as the Owner’s Equity.
  • Then, apply the following formula in cell C24 to get the total shareholder’s equity.
=SUM(C22:C23)

Read More: Schedule 6 Balance Sheet Format in Excel


📌 Step-10: Estimate Total Liabilities & Shareholder’s Equity

  • At this stage, enter the following formula in cell C25 to find the Total Liabilities & Shareholder’s Equity. Make sure this is equal to the total assets obtained earlier.
=SUM(C21,C24)

Balance Sheet Format in Excel with Formulas


📌 Step-11: Prepare Final Balance Sheet

  • Finally, apply some fill color or other necessary formatting as required. After that, the balance sheet will look as follows.

Balance Sheet Format in Excel with Formulas


Things to Remember

  • You must put a negative (-) sign before any depreciation amount.
  • The total assets the total liabilities and shareholder’s equity in your balance sheet must be equal.

Download the Free Templates

You can download the following templates from the download button below.


Conclusion

Now you can create your balance sheet in a proper format in Excel using formulas. Do you have any further queries or suggestions? Please use the comment section below for that.


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.
Md. Shamim Reza
Md. Shamim Reza

Md. Shamim Reza, a marine engineer with expertise in Excel and a fervent interest in VBA programming, sees programming as a time-saving tool for data manipulation, file handling, and internet interaction. His diverse skill set encompasses Rhino3D, Maxsurf C++, AutoCAD, Deep Neural Networks, and Machine Learning. He holds a B.Sc in Naval Architecture & Marine Engineering from BUET and has transitioned into a content developer role, generating technical content focused on Excel and VBA. Beyond his professional pursuits,... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo