-->
Home FREE SKILL DEVELOPMENT TRAINING'S LAB ASSIGNMENT

Microsoft-Excel 2007 Study Material Day-Twenty-Nine

Consolidate: 

To summarize and report results from separate worksheets, you can consolidate data from each into a master worksheet. The worksheets can be in the same workbook as the master worksheet or in other workbooks. When you consolidate data, you are assembling data so that you can more easily update and aggregate as needed.

There are two ways to consolidate data: by Category or by Position.

Consolidation by position: When the data in the source areas is arranged in the same order and uses the same labels. Use this method to consolidate data from a series of worksheets, such as departmental budget worksheets that have been created from the same template.

Consolidation by category: When the data in the source areas is not arranged in the same order but uses the same labels. Use this method to consolidate data from a series of worksheets that have different layouts but have the same data labels.


Examples:  
Create the Three Workbooks based below Table format

Workbook Name: TSRTC-2014:










Workbook Name: TSRTC-2015 :






Workbook Name: TSRTC-2016 :









Steps :

  • Take the Blank Workbook
  • Click on Data Tab
  • Then Click on Consolidate























  • Select the Reference
  • Click on “TSRTC-2014” Worksheet
  • Select the List range
  • Click on Add
  • Select the Reference
  • Click on “TSRTC-2015” Worksheet
  •  Select the List range
  •  Click on Add
  •  Select the Reference
  •  Click on “TSRTC-2016” Worksheet
  •  Select the List range
  •  Click on Add
  •  Check the Use labels in option: Top Row, Left Column, Create links to source data and Choose Function Ex: Sum, Count, and Average.
  • Click on Ok Button.