How to Make Balance Sheet in Excel (2 Useful Examples)

Get FREE Advanced Excel Exercises with Solutions!

Preparing a balance sheet is a must when evaluating an organization. This is because it provides a window into an organization’s financial strengths and weaknesses. With this intention, this article hopes to guide you on how to make a balance sheet in Excel.


What Is a Balance Sheet? 

In a nutshell, a balance sheet shows the assets and liabilities owned by an organization. In fact, you can tell if a company is making a profit or sinking into debt using the balance sheet.

A balance sheet has two parts, to clarify, the asset part and the liabilities and equities part. Consequently, the two parts can be combined to give the following equation.

Asset = Liability + Equity

Assets consist of resources that generate benefits in the future like equipment, land, buildings, etc.

Liabilities are things that the company owes to a person or a company like cash, loans, etc.

Equity represents the value for a company’s shareholders after all the assets of the company were sold off and all liabilities of the company were paid off.


How to Make Balance Sheet in Excel: 2 Examples

Luckily, Microsoft Excel makes preparing a balance sheet very easy. So without further ado, let’s see the process in action.

1. Horizontal Balance Sheet

In the Horizontal balance sheet, the Assets and the Liabilities & Equities columns are shown side by side. So, let’s see the step-by-step process to construct a Horizontal balance sheet.

Step 01: Insert the Balance Sheet Headings

  • At the very beginning, type in Balance Sheet and enter the Date.
  • Next, make two columns for Assets and Liabilities as shown in the example below.

Horizontal Example

  • Then, enter the types of Assets and Liabilities.

Horizontal Example

  • In general, you should change the number format to Accounting as this is the standard practice when preparing a balance sheet. So, open the Format Cells dialog box by pressing CTRL + 1 and choose Accounting.

Format Cell

Step 02: Calculate the Assets, Liabilities, and Equities

=SUM(D6:D8)

In this formula, the D6:D8 cells refer to the Current Assets.

How to Make Balance Sheet in Excel Horizontal Example

  • Likewise, calculate the sum for the Total Current Liabilities.

=SUM(G6:G8)

In the above expression, the G6:G8 cells represent the Current Liabilities.

How to Make Balance Sheet in Excel Horizontal Example

  • Thirdly, we add Fixed Assets and calculate the Total Fixed Asset.

=SUM(D11:D12)

Here, the cells D11:D12 consist of the Fixed Assets.

How to Make Balance Sheet in Excel Horizontal Example

  • In a similar fashion, we calculate the Long-term Liabilities.

=SUM(G11:G12)

In this example, the G11:G12 cells represent the Long-term Liabilities.

How to Make Balance Sheet in Excel Horizontal Example

  • Now, include the Stockholder’s Equity in the Liabilities column and compute the Total Equity as illustrated below.

=SUM(G15:G16)

Here, the G15:G16 cells consist of the Stockholder’s Equity.

How to Make Balance Sheet in Excel Horizontal Example

Step 03: Calculate the Total Assets and the Liabilities

  • Consequently, we get the Total Assets by adding up the Total Current Assets and Total Fixed Assets.

=SUM(D9,D13)

In this formula, the D9 cell refers to the Total Current Assets while the D13 cell indicates the Total Fixed Assets.

How to Make Balance Sheet in Excel Horizontal Example

  • Moreover, the Total Liabilities and Equity are obtained in the same way.

=SUM(G9,G13,G17)

In the above expression, the G9 cell points to the Total Current Liabilities, next the G13 cell refers to the Total Long-term Liabilities, and finally, the G17 cell indicates the Total Equity.

How to Make Balance Sheet in Excel Horizontal Example

  • Considering the general principles of Accounting, the values on both the Total Assets and the Total Liabilities and Equity columns must be equal.

2. Vertical Balance Sheet

A vertical balance sheet consists of two tables one on top of the other. Generally, the Assets column is shown on the top, and the Liabilities and Equities are shown below. Now, to construct a Vertical balance sheet, just follow these steps.

Step 01: Calculate Total Assets

  • Firstly, make a heading named Assets followed by a sub-heading for Current Assets.
  • Next, enter the Current Asset types on the left side and record the assets’ values on the right side.

Vertical Example

  • Generally speaking, the Accounting number format is preferable when making balance sheets. So, press CTRL + 1 to open a dialog box and select Accounting.

Format Cell Dialog Box

  • Following, compute the Total Current Assets using the SUM function.

=SUM(F6:G8)

In this formula, the F6:G8 cells refer to the types of Current Assets.

Vertical Example

  • In turn, compute the Total Fixed Assets as shown below.

=SUM(F11:G12)

How to Make Balance Sheet in Excel Vertical Example

  • Eventually, we get the Total Assets by adding up the Fixed Assets and the Current Assets.

=SUM(F9,F13)

In the above formula, the F9 cell indicates the Total Current Assets, and the F13 cell points to the Total Fixed Assets.

How to Make Balance Sheet in Excel Vertical Example

Step 02: Compute Total Liabilities

  • Secondly, we enter the types and the corresponding values of the Current Liabilities respectively.
  • Following, we calculate the Total Current Liabilities as portrayed below.

=SUM(F17:G19)

How to Make Balance Sheet in Excel Vertical Example

  • Then, we calculate the Long-term Liabilities as shown below.

=SUM(F22:G23)

How to Make Balance Sheet in Excel Vertical Example

  • Hence, the Total Liabilities consist of the summation of Current Liabilities and Long-term Liabilities.

=SUM(F20,F24)

How to Make Balance Sheet in Excel Vertical Example

  • Last but not the least, we obtain the Total Equity using the same process as before.

=SUM(F27,F28)

How to Make Balance Sheet in Excel Vertical Example

  • Finally, we obtain the Total Liabilities and Equity.

=SUM(F25,F29)

In the above expression, the F25 cell points to the Total Liabilities, and the F29 cell indicates the Total Equity.

How to Make Balance Sheet in Excel Vertical Example


Download Practice Workbook

You can download the practice workbook from the link below.


Conclusion

To conclude, I hope you found in this article what you were looking for. If you have any queries, please leave a comment below.


How to Make Balance Sheet in Excel: Knowledge Hub


<< Go Back To Excel For Finance | Learn Excel

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.
Eshrak Kader
Eshrak Kader

Eshrak Kader is a dedicated professional with a BSc. Degree in Naval Architecture and Marine Engineering from Bangladesh University of Engineering and Technology. He boasts a rich background in Microsoft Office Suite, and over the past year, he has authored over 120 insightful articles for the ExcelDemy. Currently, Eshrak leads a team of 5 in the ExcelDemy Video project and enjoys problem-solving and making videos on Excel. Eshrak’s passion for continuous learning underscores his commitment to excellence in... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo