How to Combine 2 Spreadsheets to Make One in Excel

Updated March 23, 2017

Microsoft Excel is a valuable spreadsheet application that you can use to manage and manipulate data. Excel is particularly valuable for small businesses that need to manage customer or employee information. In Excel, you can share information online, create complex formulas to recognise data patterns or extract specific information, create mailing lists, timesheets and pivot tables and charts. You can also merge existing spreadsheets by following a relatively easy process, although that process differs according to whether you want to keep all of the original data in both worksheets or consolidate data, which will remove any duplicate listings.

Open both of the spreadsheets you want to merge.

Create backups of both documents. Since you will be combining this data, it is important to make copies to preserve the original data in case it merges incorrectly or if you simply want access to the original data.

Organise the data so the labels (column headings) on the two spreadsheets are consistent and in the same order. For example, if column A is labelled “First Name” in the first spreadsheet, yet "First Name" is the label for column B in the second spreadsheet, highlight the second column in the second spreadsheet and drag it left, which will convert it to column A. If you have more labels in one spreadsheet than another, this is fine. Use the spreadsheet with the most labels as your base document.

Highlight the information in the spreadsheet with fewer labels, starting with the first row of actual data—do not include the row labels—and press “Ctrl” and “C” at the same time. This will copy the text to your clipboard.

Click on the second spreadsheet. Click in the cell where you want the information to begin being pasted. Make sure all cells to the right of and below that cell are clear of data, or it will be pasted over.

Press “Ctrl” and “V” simultaneously. This will paste the data.

Save the combined spreadsheet under a new name to avoid saving over your original document.

Edit the spreadsheets as necessary to consolidate by position, category, formula or pivot table. Generally, the easiest approach is to adjust each spreadsheet so the column labels are identical.

Open a new spreadsheet. Click on cell "A1, then click on the “Data” tab. Click “Consolidate.”

Select the reference area, or range, in the “Consolidate” box by typing in the range of data from the first spreadsheet you want to merge or by highlighting the entire data range from within the first spreadsheet and selecting “Add.” Repeat for the second spreadsheet.

Select “Top row” and “Left column” under the “Use labels in” if you have labels that begin in the first row and in the left column. Select “Create links to source data” if you need your data to link back to the original source. Use this option if you plan to update the original files separately and want them to automatically update in your new file as well.

Click “OK.” Excel will merge the two spreadsheets into one and remove any duplicate listings.

Cite this Article A tool to create a citation to reference this article Cite this Article

About the Author

Shanika Chapman has been writing business-related articles since 2009. She holds a Bachelor of Science in social science from the University of Maryland University College. Chapman also served for four years in the Air Force and has run a successful business since 2008.