Consolidate Data From Multiple Worksheet to Single Worksheet in Excel

In Microsoft Excel, we can easily consolidate multiple worksheet by using Excel Consolidate tool. lets discuss an example to learn how to use consolidate tool in excel.

For example, you have month wise sales reports of your company and your company owner required combine sales report. Therefore we required Sales summary report as mentioned below:

By using below mentioned steps, you can easily consolidate the data into single worksheet:

Step 1: First Arrange the data into same format and Create Sales Summary Report without amount as mentioned below:

Step 2: Now Select all Empty Cells in Sales Summary report as mentioned below:

Step 3: Now Click on Data. In Data Tool group, Click on Consolidate to open Consolidate Window. 

Step 4: In Consolidate window, select following data as mentioned below:

1: In Function drop down menu, select your desired summary functions. In this example, we select Sum as mentioned below:

2: Now Click on Reference box and Select the range of your first month sales report and Press Add to add range in All reference as mentioned below:

3: Now Repeat this step for all ranges that you want to consolidate in Sales Summary Report and Select Create links to source data and Press OK to Consolidate Sales Reports.

Now you can see the result as mentioned below:

To check Joseph month wise sales, Click on (+) sign to expand joseph sales data as mentioned below:

To check data source, Click on separate cell as mentioned below:

---Thank You---