How to Create Vertical Balance Sheet Format in Excel (In 4 Steps)

Excel is a wonderful tool for creating balance sheets as it provides many valuable features that are very easy to apply. In this article, we will learn how to create a vertical balance sheet format in Excel.

Introduction to Vertical Balance Sheet

A vertical balance sheet is one in which the line items on the balance sheet are shown as a single column of figures, starting with assets, then liabilities, and finally shareholders’ equity. Line items are displayed inside each of these categories in decreasing order of liquidity. As a result, the presentation within the top block of line items (for assets) starts with cash and typically concludes with fixed assets or goodwill (which are far less liquid than cash). Similar to the assets part, the liabilities section often starts with accounts payable and finishes with long-term debt.

How to Create Vertical Balance Sheet Format in Excel: 4 Easy Steps

In this section, we will see how we can create a vertical balance sheet format in Excel. For ease of illustration, we have divided the whole process into 4 steps. Let’s begin.

Step 01: Make Heading of the Balance Sheet

The first step is to create a heading for the balance sheet. A beautiful and well-formatted heading increases the aesthetic value of the balance sheet. Follow the steps below to prepare this.

First, open a blank Excel sheet. On the top of the sheet, take some merged cells and write “Vertical Balance Sheet” with a large font size and suitable background color. Also, make the font bold.

In the figure above, we merged and centered the B2:F2 and wrote the heading.
Now in the cell below, write the date with suitable formatting.

Step 02: Input Assets Data

After writing the heading and date, we will move to the main part of our balance sheet. As we know, a balance sheet consists of three major components, i.e., Assets, Liabilities, and Stakeholder’s Equity. The first component that appears on the balance sheet is Assets. To input the asset data, follow the steps below.

First, write down the heading “Assets” in some merged cells with suitable font size and background color.

Now, we will first input the data of the current assets. Hence, write the heading “Current Assets” in the same number of merged cells, but unlike the previous cells, it will be left aligned.

Below the heading, merge one less number of cells (B6 to E6) and make it left aligned.

Now give input your current assets and their corresponding values on the adjacent right cells.

It is better to express figures in the accounting format. So we will convert the number formatting of the whole F column into Accounting. To do that, click on the column heading F, then go to the Number group and click on the $

As a result, all the numbers will convert into the accounting format.

Now, we will calculate the total current asset by the following formula.

=SUM(F6:F8)

Similarly, we will input the data of Fixed Assets.

Now, calculate the total fixed assets with the following formula

=SUM(F11:F12)

Now to calculate total assets, we add Total Current Assets (F9) and Total Fixed Assets(F13).

=SUM(F9,F13)

Step 03: Input Liabilities Data

After inputting assets data, we have to insert the liabilities data. There are two types of liabilities: Current and Long Term. Follow the steps below to insert liabilities data.

Like in the Assets, input a heading of Liabilities in some merged cells with proper formatting.

Now insert current liabilities data like the figure below and calculate total liabilities with the formula below.

=SUM(F17:F18)

Now input the Long Term Liabilities and calculate the total long-term liabilities by the formula below.

=SUM(F21:F22)

Now adding the Total Curren Liabilities and Total Long Term Liabilities, we get the total liabilities.

=SUM(F19,F23)

Step 04: Input Stakeholder’s Equity

This is the last item we need to input. Follow the steps below to input the Stakeholder’s Equity data.

Like the 2nd and 3rd steps, write a heading for Stakeholder’s Equity.

Now input all the items of Stakeholder’s Equity and their corresponding values.

Now calculate the Total Equity by the following formula.

=SUM(F26:F27)

And lastly, we have to calculate the Total Liabilities and Equity by adding Total Liabilities( F24) and Total Equity (F28)

=SUM(F24,F28)

So, overall our balance sheet will look like this.

Things to Remember

In a balance sheet, the amount of Total Assets must be equal to Total Liabilities and Equity.
You have to add additional elements according to your needs that are not given in the sheet. Then you need to adjust the formula as well.

Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.

Vertical Balance Sheet.xlsx

Conclusion

That is the end of this article. If you find this article helpful, please share this with your friends. Moreover, do let us know if you have any further queries.

<< Go Back to Balance Sheet | Finance Template | Excel Templates

Get FREE Advanced Excel Exercises with Solutions!