Merge Spreadsheets Using Data Consolidation In Excel 2010
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.
You can also checkout previously reviewed guides on How to make Comparative Histogram in Excel 2010 & Inserting Checkboxes in Excel 2010
I am in home for lockdown .when country is good thean come or if needed pls call me .
As posted by others this does not work. There is at default no F3 functionality in MS Excel. He may be using some VBA scripts or add-on he forgot about, which does the magic.
It took me a bit of trying to get this to work, but it works!!!! This will save me many hours. I wish you could consolidate based on multiple columns, but since it can’t, I just concatenated the columns together and will use text-to-columns to divide it after the consolidation is done.
I agree with MF. my F3 doesn’t work on my keyboard either. When I manually enter exam 1, exam 2 and exam 3 it also doesn’t work. any other suggestions?
Thank you for the tips but apparently I’ve just discovered a web-application to consolidate or merge the data (include excel spreadsheet) much easier. its called podbox.
F3 doesn’t work on system…..Plz Confirm anyone?
Also works for Excel 2007 SP2
Thank you. This has helped me tremendously!
when using the consolidate function in excel 2010, i was able to conslidate the data from multiple worksheets that are numeral but not letters. How do I consolidate the cells that have letters for values?
It would be great if each teacher could be given a template with the formulas so they can enter data once. The resulting “final” could then be tracked. Very few teachers know about the consolidation feature of excel. It would save a lot of time and effort if a template could be developed so the teacher could input exam grades, homework, other assignments and the like. Good tutorial though. Thanks again and keep up the good work.
I made a tool using Excel VBA script that merge multiple excel files into one. Handy if you are merging more than 100 files. Trial version (that can merge up to 10 files) is avalable for download. Paid version cost only USD 10.
http://jeeshenlee.wordpress.com/2010/09/23/jmc-excel-join-merge-combine-multiple-excel-sheets-or-excel-workbooks/
Thanks.
Your instructions are great but F3 doesn’t work on mine. Pressing it does nothing so I typed in Tab1, Tab2, etc. which is what I renamed my worksheets. My particular worksheets do not have labels either. No matter what I try I get a “no data was consolidated” message when following the instructions. For what appears to be a simple procedure this has turned into quite a project. Any ideas you might have are greatly appreciated.
thanks keep it up
thanks for your help.