How to Consolidate Sheets in Excel (2 Suitable Examples)

Consolidate is a pretty handy tool available in the Excel ribbon’s Data tab. It helps us to consolidate or merge multiple sheets into another sheet or workbook. In this article, we will demonstrate 2 suitable examples to consolidate sheets in Excel. If you are also curious about it, download our practice workbook and follow us.

Download Practice Workbook

Download these practice workbooks for practice while you are reading this article.

2 Suitable Examples to Consolidate Sheets in Excel

To demonstrate the examples, we are going to consider two different types of datasets. Each time, we will apply the Consolidate command located in the Data tab to merge them into a separate sheet.

1. Consolidate Two Income Sheets in Excel

In the first example, we consider the dataset of 10 employees’ income for January and February. The name of the employee and their earn for January is in the range of cells B5:C14 in sheet January.

Similarly, their name and income for February are in the range of cells B5:C14 in sheet February.

The steps to complete this example are given below:

📌 Steps:

• First of all, create a new sheet.
• Now, in the Data tab, click on the Consolidate option from the Data Tools group.

• As a result, a small dialog box called Consolidate will appear.
• Then, keep the Function in Sum.
• After that, in the Reference box, select the range of cells B4:C14 from the January sheet.
• Next, click on Add.

• Similarly, add the cell reference for February from the sheet February.
• In addition, check all three options at the bottom of the dialog box.
• Finally, click OK.

• You will see both datasets will appear with the corresponding employee name. Here, both columns represent different income values for two months. As a result, the value is just copied here without summing.

• Modify the dataset according to your desire to get a good outlook for the viewers.

Thus, we can say that our working procedure works perfectly, and we are able to consolidate sheets in Excel.

Read More: Consolidation of Financial Statements in Excel (2 Easy Examples)

2. Consolidating Sales Reports of Two Stores in a Sheet

In the second example, we take the sales report of two stores. The stores sell three different types of items. The sales report of the first store is the sheet Store 1 in the range of cell B5:E7.

Similarly, the sales report of the second store is in the range of cells B5:E7 in sheet Store 2.

The steps to accomplish this example are given as follows:

📌 Steps:

• First, create a new sheet.
• After that, in the Data tab, click on the Consolidate option from the Data Tools group.

• As a result, a small dialog box called Consolidate will appear.
• Now, keep the Function in Sum.
• Then, in the Reference box, select the range of cells B4:C14 from the Store 1 sheet.
• In the end, click on Add.

• Similarly, add the cell reference for the second store from the sheet Store 2.
• Additionally, check all three options at the bottom of the dialog box.
• Finally, click OK.

• You will notice both datasets will appear with the corresponding value. Here, both datasets have similar items. As a result, the Consolidate command sums each item’s quantity.

• Modify the dataset according to your desire to get a good outlook for the viewers.

Hence, we can say that our working procedure works effectively, and we are able to consolidate sheets in Excel.

Read More: How to Consolidate Data from Multiple Workbooks in a Single Worksheet

Merge Two Sheets in Excel

Besides sheet consolidation, we can also merge two sheets using the Consolidate command. Both tasks are almost similar, yet we are demonstrating the procedure to merge multiple sheets as follows:

We take into account the income statistics for January and February for 10 employees. Cells B5:C14 of sheet January include the employee’s name and their earnings for January.

Similarly, their name and monthly income for February are included in the range of cells B5:C14 of sheet February.

📌 Steps:

• At first, in a new sheet, go to the Data tab.
• Afterward, click on the Consolidate option from the Data Tools group.

• As a result, a small dialog box called Consolidate will appear.
• After that, keep the Function in Sum.
• Then, in the Reference box, select the range of cells B4:C14 from the January sheet.
• Thus, click on Add.

• Similarly, add the cell reference for February from the sheet February.
• Moreover, check all three options at the bottom of the dialog box.
• At last, click OK.

• You will see both datasets will merge into our new Excel sheet.

• Modify the new result sheet according to your desire to get a good outlook for the viewers.

Therefore, we can say that our working procedure works precisely, and we are able to merge multiple sheets in Excel.

Read More: How to Consolidate Two Sheets into One in Excel (3 Useful Methods)

Combine Multiple Sheets in One Excel Workbook

Here, we will combine multiple sheets from a different workbook into one single workbook by using the Consolidate command. For that, we take into account the January and February income statistics for 10 employees from the Consolidate Sheets workbook. In the range of cells B5:C14 of sheet January, the employee’s name and earnings for January are displayed.

In keeping with this, their name and income for February are located in the range of cells B5:C14 of sheet February.

The steps are described below step-by-step:

📌 Steps:

• Firstly, open a new workbook.
• Now, go to the Data tab and click on the Consolidate option from the Data Tools group.

• As a result, a small dialog box called Consolidate will appear.
• Then, keep the Function in Sum.
• Afterward, in the Reference box, select the range of cells B4:C14 from the January sheet located in the Consolidate Sheets workbook.
• Finally, click on Add.

• Following the same process, add the cell reference for February from the sheet February.
• Check all three options at the bottom of the dialog box.
• At last, click OK.

• You will get both datasets in our new workbook.

• Add some changes to the new result sheet in accordance with your desired outcome for the viewers.

So, we can say that our working procedure works successfully, and we are able to combine multiple sheets from a different workbook into a single Excel workbook.

Read More: Excel Consolidate Data from Multiple Worksheets in a Single Worksheet

Conclusion

That’s the end of this article. I hope that this article will be helpful for you and you will be able to consolidate sheets 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 Consolidate Data from Multiple Ranges in Excel (2 Easy Ways)
• Consolidate Multiple Worksheets into One PivotTable (2 Methods)
• Data Validation and Consolidation in Excel (2 Examples)
• Consolidate Data from Multiple Columns in Excel (7 Easy Ways)
• How to Remove Consolidation in Excel (2 Handy Methods)
• Consolidate Data in Excel from Multiple Workbooks (2 Methods)
• How to Automate Consolidation in Excel (with Easy Steps)