How to Prepare Balance Sheet from Trial Balance in Excel: Method 5

Method 1 – Estimate Non-Current Assets

The balance sheet is a new one. For that, create a new spreadsheet. We will use the SUM function to calculate non-current assets. We see three non-current assets: Land and buildings, Equipment, and equipment depreciation (a credit to assets).

So we fill out the non-current assets segment of the balance sheet with these accounts.

Remember to put the negative values for all the credit accounts. This makes the rest of the calculations easier.

Select cell C7 and write down the following formula.

• Pressing Enter you will have all the non-current assets for the balance sheet. The section will look something like this.

Method 2 – Evaluate Current Assets

There are a total of four current assets accounts in the trial balance.  We will use the SUM function to calculate current assets.

Make a section for current assets in the balance sheet and put these values in them.

Select cell C8 and write down the following formula.

After pressing Enter, the balance sheet will now look like this.

Method 3 – Calculate Liabilities

Return to the trial balance and identify all the liabilities on the list. The figure, there are three total: Creditors’ control, Income received in advance, and accrued expenses. We will use the SUM function to calculate the liabilities.

Put them all in a section on the balance sheet.

Select cell F4 and write down the following formula.

Press Enter, and you will have the liabilities segment completed. The balance sheet will look something like this now.

Method 4 – Evaluate Equity

There are only two entries here—the capital and drawings. To calculate all the earnings in this segment, we need external sources. As equity is a debit, in this case, it should have a negative value compared with the rest. We are putting a negative value on it here on the balance sheet.

Enter these values and the earnings in the balance sheet now.

Select cell F8 and write down the following formula. We will use the SUM function to calculate equities.

Press Enter to complete the equity list on the balance sheet.

Method 5 – Determine Total Assets and Liabilities

Calculate the total assets and liabilities and determine whether the sheet is balanced. We need to use the SUM function.

To find the total assets, add the non-current and current assets. Select cell C13 and write down the following formula.

Press Enter and you will have the total assets on the balance sheet.

To calculate the sum of liabilities and equity, select cell F12 and write down the following formula.

Press Enter, and finally, your balance sheet will be prepared in Excel from the trial balance.

Download Practice Workbook

You can download the workbook with the example used for a demonstration from the download box below. Download and try it yourself while you go through the article.