How to Merge Data From Two Spreadsheets
Merging data from two Excel spreadsheets is done by consolidating the data. Consolidation lets you summarize information by specifying the location of the data source and the formula used to merge the data together. This feature is helpful if you have several people working on a project. If each person is generating totals for her region and you need an overall total, use consolidation to merge the separate worksheet data to get a master worksheet with a grand total.
Open Excel 2010 and locate the workbook containing the worksheets that will be merged. Click the "File" tab and select the "Open" icon. Find the workbook and double-click it.
Click in the next blank worksheet in the workbook. Select the "Data" tab. Click "Consolidate." The Consolidate dialog box opens.
Select the function you will use to merge the data. If you are adding the merged values, select "Sum." Click the red box in the reference section.
Click the worksheet that contains the first set of data that will be merged. Highlight the range and click the "Add" button. Select the worksheet containing the second set of data that will be merged. Highlight the range and click the "Add" button.
Select the option that displays the location of the row or column headers in this merged data. Select either "Top Row," "Left Column" or both. Click "OK." The data is merged in the new worksheet.