# How to Consolidate Sheets in Excel (2 Suitable Examples)

Get FREE Advanced Excel Exercises with Solutions!

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.

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

📚 Note:

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

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

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

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

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

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

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

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

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

Soumik Dutta

Hi! I am Soumik. I have completed my Bachelor of Science in Naval Architecture and Marine Engineering. I have found my calling in Machine Learning and Data Science, and in pursuing so, I have realized the importance of Data Analysis. Excel is one excellent tool to fulfill the purpose. I am always trying to learn every day about different features of Excel and trying to share here what I am learning.

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF