The consolidated balance sheet is a report that represents the whole financial state of a parent business and all of its subsidiaries on a single sheet, without separating the entities. It is structured similarly to a conventional balance sheet. In this article, we are going to demonstrate the step-by-step procedure to create a consolidated balance sheet format in Excel. If you are also curious about it, download our practice workbook and follow us.

## Step-by-Step Procedure to Create Consolidated Balance Sheet Format in Excel

In this article, we will show you the step-by-step procedure to create a consolidated balance sheet format in Excel. After completing all the steps, the final summary layout will be like the image shown below:

**ðŸ“š Note:**

All the operations of this article are accomplished by using the **Microsoft Office 365** application.

### Step 1: Creating Preliminary Summary Layout

In the first step, we will input the essential particulars to create the preliminary summary layout. The steps are given below:

- First of all, we will insert a shape to show the title of the sheet. For that, select cell
**B1**. - Afterward, in the
**Insert**tab, click on the**drop-down arrow**of the**Illustration**>**Shapes**and choose the**Scroll: Horizontal**shape.

- Now, write down the title of the sheet as
**Balance Sheet Summary**.

- Then, in the range of cells
**B5:B6**, write down the following entities and allot the corresponding cells to input the values of that company. - Modify the cell format according to your desire.

- Next, in the range of cells
**B8:B16**, write down the following particulars and denote the corresponding cells of the next column to input the values.

- At last, insert the logo of your financial institution. To demonstrate the process, we insert the logo of our website.
- In the
**Insert**tab, click on the**drop-down arrow**of the**Illustration**>**Pictures**and choose the**This Device**option.

- As a result, a small dialog box called
**Insert Picture**will appear. - After that, select the picture titles
**ExcelDemy**and click on**Insert**.

- You will get the image, and our first task ends here.

Thus, we can say that we have completed the first step to creating a consolidated balance sheet format in Excel.

### Step 2: Estimating Total Assets

In this step, we will calculate the value of **total assets**. The procedure is described below:

- First, in the
**Summary**sheet, select rows**1:6**. - Now, press
**â€˜Ctrl+Câ€™**to copy the rows.

- Then, create a new sheet and press
**â€˜Ctrl+Vâ€™**to paste the data.

- After that, change the title of the sheet from
**Balance Sheet Summary**to**Total Assets**.

- Afterward, select cell
**C5**and write down the following formula using**the IF function**to import the**Company Name**into the cell.

`=IF(Summary!C5=0," ",Summary!C5)`

- Press
**Enter**.

- Then,
**drag**the**Fill Handle**icon to copy the formula up to cell**C6**to import the**Address**.

- Now, we will estimate the value of total
**Current Assets**. For that, create a dataset in the range of cells**B9:D13**with a sample dataset.

- At last, we will use
**the SUM function**to calculate the value of total current assets. - For that, select cell
**D14**and write down the following formula in the cell.

`=SUM(D9:D13)`

- Press
**Enter**.

- Similarly, create a dataset for determining the values of total
**Fixed Assets**and total**Other Assets**.

- In the end, select cell
**D26**, and write down the following formula to evaluate the value of total assets.

`=D14+D20+D24`

- Press
**Enter**for the last time.

Hence, we can say that we have finished the second step to creating a consolidated balance sheet format in Excel.

**Read More:Â **How to Create Monthly Balance Sheet Format in Excel

### Step 3: Evaluating Total Liabilities and Ownerâ€™s Equity

In the following step, we will estimate the value of **total liabilities and ownerâ€™s equity**. The process is explained below:

- At first, create a new sheet and entitle it as
**Liability&Equity**.

- Now, in the
**Assets**sheet, select rows**1:6**and press â€˜**Ctrl+C**â€™ to copy the rows.

- Then, go to the
**Liability&Equity**sheet and press â€˜**Ctrl+V**â€™ to paste the content.

- Change the title of the sheet from
**Total Assets**to**Total Liabilities and Ownerâ€™s Equity**.

- After that, like the previous step create a dataset in the range of cells
**B9:D14**with the sample data value to get the list of**Current Liabilities**.

- Next, select cell
**D15**and write down the following formula using**the SUM function**to get the value of**Total Current Liabilities**.

`=SUM(D9:D14)`

- Press the
**EnterÂ**key.

- Similarly, produce a dataset with a sample dataset to get the value of
**Total Long-Term Liabilities**and**Total Ownerâ€™s Equity**.

- Finally, to calculate the value of
**Total Liabilities and Ownerâ€™s Equity**, select cell**D27**and write down the following formula in the cell.

`=D15+D20+D25`

- Afterward, press
**Enter**.

Therefore, we can say that we have accomplished the third step of creating a consolidated balance sheet format in Excel.

**Read More:Â **How to Create School Balance Sheet Format in Excel

### Step 4: Determine All Values in the Summary Sheet

In the final step, we will determine all the values we listed in the **Summary** sheet to complete a consolidated balance sheet format in Excel. The procedure is shown below step-by-step:

- Firstly, input the
**Company Name**and the**Address**in the range of cells**C5:C6**. You will also notice those values will export into our other**two**sheets.

- Now, to get the value of Assets, select cell
**D8**and write down the following formula into the cell.

`=Assets!D26`

- Press
**Enter**.

- Similarly, to import the value of
**Liabilities and Ownerâ€™s Equity**, write down the following formula into cell**D9**.

`=' Liability&Equity'!D27`

- Then, press the
**Enter**key.

- After that, we will estimate all the financial ratios. First of all, to calculate the
**Debt Ratio**, select cell**D12**and write down the following formula. For that, we will use**the IF function**.

`=IF(Assets!D26=0,"",(' Liability&Equity'!D15+' Liability&Equity'!D20)/Assets!D26)`

- Again, press
**Enter**.

- Secondly, to get the value of the
**Current Ratio**, write down the following formula in the cell**D13**through the**IF**function.

`=IF(' Liability&Equity'!D15=0,"",Assets!D14/' Liability&Equity'!D15)`

- Press
**Enter**.

- After that, to calculate the value of
**Working Capital**, select cell**D14**and write down the following formula into the cell.

`=Assets!D14-' Liability&Equity'!D15`

- Press the
**Enter**Key.

- Afterward, select cell
**D15**and write down the following formula to estimate the value of the**Assets-to-Equity Ratio**by the**IF**function.

`=IF(' Liability&Equity'!D25=0,"",Assets!D26/' Liability&Equity'!D25)`

- Press
**Enter**.

- At last, to get the value of the
**Debt-to-Equity Ratio**, write down the following formula in cell**D16**.

`=IF(' Liability&Equity'!D25=0,"",(' Liability&Equity'!D15+' Liability&Equity'!D20)/' Liability&Equity'!D25)`

- Press
**Enter**for the last time.

- Our job is completed.

Finally, we can say that we have finished the final step of creating a consolidated balance sheet format in Excel.

**Read More:** How to Prepare Charitable Trust Balance Sheet Format in Excel

### Step 5: Verify with Sample Data

In this step, we will input a new sample dataset to check the accuracy of our formula. We have to input in our **Assets** and **Liability&EquityÂ **sheets.

- First of all, input the following dataset in the
**Assets**sheet.

- After that, input the following data in the
**Liability&Equity**sheet.

- Now, go to the
**Summary**sheet and you will get all the results.

Thus, we can say that all of our formulas work properly and we are able to create a consolidated balance sheet format in Excel.

**Read More:Â **How to Make Balance Sheet Format in Excel for Individual

**Download Practice Workbook**

Download this practice workbook for practice while you are reading this article.

## Conclusion

Thatâ€™s the end of this article. I hope that this article will be helpful for you and you will be able to create a consolidated balance sheet format in Excel. Please share any further queries or recommendations with us in the comments section below if you have any further questions or recommendations.