How Do I Merge Two Excel Spreadsheets?
DAVID WEEDMARK
TECH
If you have two or more spreadsheets containing related data, you can merge them into a single Excel worksheet using Excel's consolidate option. Before consolidating spreadsheets, they must use the same format. For example, if you have two sales reports using the same template, these can be merged quite quickly. If the columns don't contain the same types of data in the same order, you will have to rearrange one of the worksheets before you can consolidate them.
1 Open the workbooks
Open the workbooks containing the two spreadsheets you want to merge. This may be two separate workbooks, or a single workbook containing two different worksheets.
2 Compare both worksheets
Compare both worksheets to ensure they use the same formatting and that the columns correspond to each other. For example, if the first worksheet lists names, then gross revenue then net revenue, the data in the second worksheet should be in the same order.
3 Create a new worksheet
Create a new worksheet. This is your master worksheet to host the merged data from the first two worksheets.
4 Click the first cell
Click the first cell in the upper left corner of the master worksheet. Click the "Consolidate" option, located in the Data Tools group under the Data tab. The Consolidate dialog box opens.
5 Click the Function button
Click the "Function" button and select "Sum" if it isn't already selected. This step ensures that the data will be added to the master worksheet without performing any other functions.
6 Click the Reference field in the Consolidate dialog box
Click the "Reference" field in the Consolidate dialog box.
7 Select
Select on the worksheet containing the data you want to appear first and highlight the data with the cursor. Click the "Add" button in the Consolidate dialog box.
8 Click the Reference'' field again
Click the "Reference" field again, highlight the next range of cells you want to appear next, and then click the "Add" button. Keep repeating this process until you have selected all of the cells you want to appear on the merged worksheet.
9 Click the OK button in the Consolidate dialog box
Click the "OK" button in the Consolidate dialog box. The data from your first two worksheets are merged into the master worksheet.
- Information in this article applies to Microsoft Excel 2013. It may vary slightly or significantly with other versions or products.
- When you are selecting data to consolidate, the Consolidate dialog box may get in the way. You can click the "Expand/Collapse" button on the Consolidate dialog box to expand and collapse it as needed. This button is located beside the Reference text field beside the Browse button.
Related Articles
