How to Consolidate Information in Excel (2 Simple Ways)

Get FREE Advanced Excel Exercises with Solutions!

Despite having the same type of information for various occasions in different worksheets, we can consolidate them in a single worksheet. It will combine information from multiple worksheets into one. To fulfill this purpose, there is a command named Consolidate in Excel. In this article, I am going to explain two simple ways how to consolidate information in Excel.
For more clarification, I am going to use two datasets. Dataset-1 contains the Mid-Term Result- 2021 of a student in Physics, Chemistry, and Math.

Similarly, Dataset-2 contains the Annual Result- 2021 of a student in Physics, Chemistry, and Math.

We will try to consolidate these results and make a consolidated one.

Table of Contents hide

Download Practice Workbook

2 Simple Ways to Consolidate Information in Excel

1. Consolidate Information with Excel Consolidate Feature

2. Using Pivot Table to Consolidate Information

Conclusion

Download Practice Workbook

Consolidate Information.xlsx

2 Simple Ways to Consolidate Information in Excel

1. Consolidate Information with Excel Consolidate Feature

The best and simplest way to consolidate information from multiple worksheets is to use the Consolidate feature in Excel. We can do it easily just by following the steps mentioned below.

Steps:

First of all, create a new worksheet. Here, I created a new worksheet and named it
Next, pick a cell for the consolidation (i.e. B4).

Now, go to the Data tab.
Click on the Consolidate feature from the ribbon.

A Consolidate wizard will appear.

Pick a function according to your need (i.e. SUM).
Select the range of the particular worksheet in the Reference section (i.e. B4:E11 cells in Mid worksheet).
Next, click on Add.

After that, select the information range of another worksheet in the Reference section (i.e. B4:E11 cells in the Annual worksheet).
Press on Add.
Now, check all the boxes.
Finally, press OK to finish the process.

We will have our consolidated information on the master worksheet.

You can decorate your consolidated information according to your choice.
There will be two buttons naming 1 and 2 which will control the consolidated information’s view. Button 1 shows the consolidated final information hiding all the data collected from the multiple worksheets.

On the other hand, button 2 shows all the information keeping the same type of information in groups.

2. Using Pivot Table to Consolidate Information

Pivot Table is another way to consolidate information in Excel. This process is mainly used to organize the disorganized information of a dataset.
To explain the whole consolidation process using Pivot Table, I am going to use a new dataset about the salary structure of a company. I explained the salary structure of that company using the Name, Department, and Salary columns.

Steps:

At first, select all the cells that you want to consolidate(i.e. B4:D14).
Secondly, go to the Insert tab.
Next, click on the Pivot Table option from the ribbon.

The Table/Range section will be automatically filled as I have mentioned the range in the first step.

Choose an option where you want to have your Pivot Table. I decided to have the Pivot Table in the Existing Worksheet.
If you select the Existing Worksheet, select the cell where you want to have the table in the Location section(i.e. B18).
Followingly, press OK.

We will be able to see the Pivot Table format in the selected cell. A Pivot Table Fields box will appear on the right side of the worksheet.

Mention the Rows and Values from the Pivot Table Fields I have selected Department and Name as the Rows where Department is the primary label. I have also selected Salary as Values.

After that, we will have our consolidated data on the selected location.

You can modify your data if you wish to do so.

Conclusion

I have tried to simply explain the two simple ways on how to consolidate information in Excel. It will be a matter of great pleasure for me if this article could help any Excel user even a little. For any further queries, comment below. You can visit our Exceldemy site for further information on Excel.