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

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

`=SUM(C7:C9)`

## 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:
`=SUM(C11:C12)`

## Step 6: Calculate Total Assets

• In cell C14, calculate the Total Assets by adding the Total Current Assets and Net Fixed Assets:
`=SUM(C10,C13)`

## 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:
`=SUM(C16:C18)`

## 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:
`=SUM(C19:C20)`

## 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:
`=SUM(C22:C23)`

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:
`=SUM(C21,C24)`

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

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

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

Advanced Excel Exercises with Solutions PDF