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.

## Download Practice Workbook

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

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

**Read More: ****Balance Sheet Format of a Company in Excel (Download Free Template)**

### 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 upto 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: ****Balance Sheet Format in Excel with Formulas (Create with Easy Steps)**

**Similar Readings**

**Balance Sheet Format for Construction Company in Excel****How to Make Balance Sheet Format in Excel for Individual****Debit Credit Balance Sheet with Excel Formula (3 Suitable Examples)****Schedule 6 Balance Sheet Format in Excel****Balance Sheet Format in Excel for Proprietorship Business**

### 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 Vertical Balance Sheet Format in Excel**

### Step 4: Determine All Values in 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 Create Common Size Balance Sheet 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 Create a Balance Sheet for Small Business in Excel**

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

Don’t forget to check our website, **ExcelDemy**, for several Excel-related problems and solutions. Keep learning new methods and keep growing!

**Related Articles**

**How to Create Monthly Balance Sheet Format in Excel****Create Ledger Balance Sheet in Excel (with Easy Steps)****How to Create School Balance Sheet Format in Excel (with Steps)****Create NGO Balance Sheet Format in Excel (4 Easy Steps)****How to Make a Forecasting Balance Sheet in Excel (With 3 Steps)****Create Projected Balance Sheet Format for Bank Loan in Excel****Revised Schedule 3 Balance Sheet Format in Excel with Formula**