How to Create a Balance Sheet Format in Excel with Formulas

Step 1: Create a Proper Heading for the Balance Sheet

  • In cell B2, type the name of your company.
  • In cell B3, write Balance Sheet.
  • Enter the date in cell B4.
  • Merge cells B and C for rows 2 to 4.
  • Apply the Bottom Border from the Home tab.
  • Your balance sheet heading will now look as follows.

Step 2: Insert Balance Sheet Components

  • In column B, enter the components: Assets, Liabilities, and Shareholder’s Equity.
  • Corresponding amounts will be entered in column C.

Step 3: Format the Amount Column

  • Before entering data, format the Amount column.
  • Select column C by clicking on the column number.
  • Press CTRL+1 to open the Format Cells dialog box.
  • Choose the Accounting number format with 2 decimal places.
  • Ensure the $ sign is visible.
  • Click OK.

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

Step 4: Insert Current Assets

  • Enter the current assets into your balance sheet. These are typically assets with a period of less than one year. Examples include Cash and Equivalents, Accounts Receivable, and Inventory.
  • In cells B7 to B9, input the names of these components.
  • Corresponding amounts should be entered in cells C7 to C9.
  • To calculate the total current assets, use the formula in cell C10:


Balance Sheet Format in Excel with Formulas

Step 5: Input Fixed Assets

  • Enter the fixed assets (such as Plant & Equipment) in cell B11.
  • The corresponding amount should be entered in cell C11.
  • Depreciation amounts must be entered as negative values (inside brackets).
  • Calculate the Net Fixed Assets using the formula in cell C13:

Step 6: Calculate Total Assets

  • In cell C14, calculate the Total Assets by adding the Total Current Assets and Net Fixed Assets:

Balance Sheet Format in Excel with Formulas

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

Step 7: Input Current Liabilities

  • Enter the current liabilities (such as Accounts Payable, Short-Term Notes Payable, etc.) in cells B16 to B18.
  • Corresponding amounts should be entered in cells C16 to C18.
  • Calculate the total current liabilities using the formula in cell C19:

Step 8: Estimate Total Liabilities

  • Enter the long-term liabilities (such as Long-Term Debt) and their corresponding values.
  • Calculate the total liabilities using the formula in cell C21:

Step 9: Calculate Total Shareholder’s Equity

  • Enter the Shareholder’s Equity components (Common Stock, Treasury Stock, Retained Earnings, etc.), also known as Owner’s Equity.
  • In cell C24, calculate the total shareholder’s equity:

Read More: Schedule 6 Balance Sheet Format in Excel

Step 10: Estimate Total Liabilities & Shareholder’s Equity

  • In cell C25, enter the following formula to find the Total Liabilities & Shareholder’s Equity. Ensure that this value matches the total assets obtained earlier:

Balance Sheet Format in Excel with Formulas

Step 11: Prepare Final Balance Sheet

  • Apply any necessary fill color or other formatting as required.
  • After completing these steps, your balance sheet will appear as follows:

Balance Sheet Format in Excel with Formulas

Things to Remember

  • Always use a negative (-) sign before any depreciation amount.
  • Ensure that the total assets, total liabilities, and shareholder’s equity in your balance sheet match.


Download the Free Templates

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!
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