We usually maintain the data in multiple worksheets to check the data for each instance and to view them individually but it would be great to merge all the data from different worksheets in Excel spreadsheet to analyze it. Consolidate feature in Excel 2010, lets you to pull-each record from the worksheet in to one master worksheet, which adds-up all data from spreadsheets. Through Consolidation, you can summarize data from multiple sheets into one designated master sheet. By assembling data in to single master sheet, you can modify, or aggregate it under one window.
Launch Excel 2010, and open a spreadsheet on which you want to apply data Consolidation. For instance, we have included worksheet, carrying student records, containing marks obtained by student in different exams. As you can see in the screenshots below that we have included three worksheets containing students records in each exam.
Exam 1:
Exam 2:
Exam 3:
Now we need to consolidate our data on a single sheet, for this we will start giving each exam, a name range. Go to the first sheet by the name of Sheet1, and select the data, from top-left corner of the sheet, enter exam1, as shown in the screenshot below.
Repeat the same procedure in other sheets to assign them name range exam2 and exam3 respectively. Now we will be moving to new worksheet, give it a suitable name by right-clicking on its name, and click Rename.
Now navigate to Data tab, and click Consolidate.
Upon click, Consolidate dialog will appear, from Function options, select SUM (as we want to show sum of values for consolidation).
Now click In the References text pane, to choose the references, you can also choose reference by clicking on Add button but to make it more easier for you, we have defined name ranges. So press F3 on keyboard to open name ranges in Paste Name dialog, rather than selecting sheets manually, which could be hectic if you are dealing with huge spreadsheet.
Upon pressing F3, Paste Name dialog will appear, select exam1 and click OK to insert it in reference text pane.
Upon click you will notice the name range is inserted into Reference pane, now under Use labels in, enable Top row, and Left Column options, as you have noticed earlier that the first row and Left column of every worksheet contains labels for data.
Now repeat the procedure for adding remaining name ranges for consolidation. you can see in the screenshot below, that we have added all the name ranges. You can also enable Create links to source data option, for linking the source data (contained in different sheets with this new one.). Click OK to proceed further.
Upon click, you will notice that the values from the sheets have added-up in to final sheet, as shown in the screenshot below.
