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.


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.

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

Income list for January

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

Income list for 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.

Lunching Consolidate Dialog Box to Consolidate Sheets

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

Adding Range of Cells to Consolidate Sheets

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

Consolidate Multiple Sheets in New Worksheet

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

how to consolidate sheets in excel

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.

Sales report of first store

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

Sales report of second store

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.

Opening Consolidate Dialog Box for Consolidating Sales Reports of Two Stores

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

Inserting the range of cells reference

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

Consolidating Sales Reports of Two Stores in a Sheet in Excel

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.

Income list for January

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

Income list for February

📌 Steps:

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

Opening Consolidate Dialog Box to Merge or Consolidate Sheets

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

Choosing all suitable properties to merge or consolidate sheets

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

Merge Two Sheets in Excel

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.

Income list for January

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

Income list for 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.

Applying Consolidate Command to Combine or Consolidate Multiple Sheets in One Excel Workbook

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

Checking All Features to Combine or Consolidate Multiple Sheets in One Excel Workbook

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

Combine or Consolidate Multiple Sheets in One Excel Workbook

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

Soumik Dutta
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

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo