To summarize and report results from data on separate worksheets, you can consolidate the data from each separate worksheet into one worksheet (or master worksheet). The worksheets you consolidate can be in the same workbook as the master worksheet or in other workbooks. When you consolidate data in one worksheet, you can more easily update and aggregate it on a regular or ad hoc basis.
For example, if you have a worksheet of expense figures for each of your regional offices, you might use data consolidation to roll up these figures into a corporate expense worksheet. This master worksheet might contain sales totals and averages, current inventory levels, and highest selling products for the entire enterprise.
There are two main ways to consolidate data:
Consolidate by position Use this method when the data from multiple source areas is arranged in the same order and uses the same row and column labels. For example, when you have a series of expense worksheets that are created from the same template.
Consolidate by category Use this method when the data from multiple source areas is arrange differently, but the same row and column labels are used. For example, you can use this method when you have a series of inventory worksheets for each month that use the same layout, but each worksheet contains different items or a different number of items.
You can consolidate data by using the Consolidate command (Data tab, Data Tools group). You can also consolidate data by using a formula or a PivotTable report.
For more details pls visit youtube or reply me for reference.