The Ultimate Guide on how to Merge Excel files

So you want to merge Excel files? You’re in the right place!

There are several different ways to merge Excel files and we’ve covered them below. Click on the title for the type of merge you want to do.

If you can’t see what you need to do here, please contact us and let us know. We’d love to add anything missing to this list.

Add all rows from separate spreadsheets into a single sheet in a single spreadsheet (concatenating the sheets into one)

For example, you have 3 spreadsheets, each containing a single worksheet with columns first_name, last_name, company_name, city, phone1, email. Each spreadsheet has this column structure and you’d like to concatenate these together into just one sheet in one spreadsheet. e.g.:

Merge Excel files with the same columns
Merge Excel files with the same columns

If this is what you need to do, click here to find out how to do it.

Add all rows from separate spreadsheets, with differing columns, into a single spreadsheet with consistent columns

For example, you have 3 spreadsheets, each containing a single worksheet:

  • The first spreadsheet has columns first_name, last_name, company_name, city, phone1, email.
  • The second spreadsheet has columns email, company_name, phone2.
  • The third spreadsheet has columns email, first_name, last_name, date_of_birth.

Here, you’d like to merge the data from each spreadsheet into just one sheet in one spreadsheet with the same set of columns; first_name, last_name, company_name, city, phone1, phone2, email, date_of_birth. e.g.:

Merge Excel files with differing columns
Merge Excel files with differing columns


If this is what you need to do, click here to find out how to do it.

Copy all sheets from separate workbooks into a single workbook

For example, you have several workbooks with different sheets of related data. You simply want to have all the sheets from the separate workbooks into a single workbook, e.g.:

How to merge worksheets from multiple Excel workbooks into one
Merge worksheets from multiple Excel workbooks into one


If this is what you need to do, click here to find out how to do it.

Consolidate the values in cells from separate spreadsheets with the same format (i.e. the same columns and rows)

For example, you have two or more spreadsheets with the same columns and rows. Only the values in cells containing numerical data are different and it is these that you would like to consolidate, e.g.:

How to consolidate Excel files
Consolidation in Excel


If this is what you need to do, click here to find out how to do it.

Take values from cells in one sheet to add to rows in another

For example, you have 3 spreadsheets:

  • The first has columns first_name, last_name, city, email.
  • The second has columns email, phone1 and phone2.
  • The third has columns first_name, last_name, email and company_name.

What you want is one spreadsheet with columns first_name, last_name, company_name, city, email, phone1 and phone2, e.g.:

Merge values from cells to rows in Excel
Merge values from cells to rows in Excel

If this is what you need to do, click here to find out how to do it.

Merge changes from shared workbooks

This type of merge is used when you are using the shared workbooks feature in Excel. You will have 2 or more workbooks where workbook sharing has been turned on and your colleagues have completed their changes to their copies of the shared workbook. You now need to merge the changes in the workbook copies back into the master workbook.

If this is what you need to do, click here to find out how to do it.

 
If you can’t see what you want to do here, please contact us and let us know. We’d love to add anything missing to this list.