How Do I Merge Two Excel Spreadsheets?

When you're working in Excel, it can be frustrating to switch between multiple spreadsheets. Excel makes it easy to combine data from difference sources into one master spreadsheet. This is known as merging spreadsheets, and it's a fast way to combine data without the hassles of copying, pasting and formatting. These instructions are for Excel 2013, but the process is similar in earlier versions of the program.

First, open Excel and open both of the files you're looking to merge. Make sure the column headers are the same. Now open a new spreadsheet, which will be the destination for your merged data. From this new spreadsheet, click on the "Data" tab and click on "Consolidate." The Consolidate menu will pop up with Function and Reference fields. For Function, choose "Sum" (this is for basic consolidation, see the link in Resources for more complex tasks). Click to put your cursor in the Reference field. Then select one of the Excel spreadsheets you want to merge. Click the upper left cell on the spreadsheet and drag to select the area to merge. Make sure to select extra rows after the data to make room for the data you're adding. You will see the Reference field populate with the spreadsheet information (it will look something like this: '[DS Excel Book 1.xlsx]Sheet1'!$A$1:$C$10).

In the Consolidate menu, under Use Labels, click to select "Top Row" or "Left Column," or both depending on where your labels are. Then click "Add" to add this spreadsheet to the master sheet. Click to select the Reference box and repeat the process above to add the second Excel sheet. (You can also follow this procedure to add more than two spreadsheets, just keep adding the spreadsheets you want to merge.)

If you want changes made to the individual sheets to automatically update in the master spreadsheet, click the box next to "Create links to source data." Click "OK." All of your data will appear in the master spreadsheet.

If you've tried the method described above to merge data and it didn't work, look for these common issues. Are there blank cells or rows in your data? This can confuse Excel. Delete them and try again.

Do both sets of data begin in the upper left corner of their spreadsheet? If you're choosing to include both the "Top Row" and "Left Column" labels, the merge can't distinguish between the two for cell A1. For example, if the A1 header says Dates and column A lists various dates, by choosing to use all labels in the merge (you would do this by selecting both "Top Row" and "Left Column" labels in the Consolidate menu), Excel will only show the list of dates and not the header. If you choose to include only the "Top Row" labels, Excel will ignore the list of dates in column A and include only the A1 header. Either manually type this header in the master spreadsheet or manipulate the original spreadsheets so the A1 cell is blank.