How to Merge Multiple Tables from Different Excel Sheets

Date:2019-10-6 Author:Cordelia

Sometimes you may have the need to create a comprehensive Excel document whose data comes from different files or even files edited by different people. It’s feasible to copy & paste the data manually if it isn’t too much. Otherwise you can try the Consolidate feature to let Excel do most of the work and merge the tables from multiple Excel files.

For example, there’re 2 tables from example.xlsx and example2.xlsx separately need to be merged.

1. For the first step, you can create a new Excel file and switch to Data tab.

How to Merge Multiple Tables from Different Excel Sheets

2. Click Consolidate in Data Tools.

How to Merge Multiple Tables from Different Excel Sheets

3. Click the up arrow on the left of Browse… and switch to the Excel file example.xlsx.

How to Merge Multiple Tables from Different Excel Sheets

4. Drag your mouse to select the range of table. It will be filled in the textbox of Reference automatically.

How to Merge Multiple Tables from Different Excel Sheets

5. Click that down arrow to go back to the Consolidate window. Then hit Add button to add the table range into the list of merging.

How to Merge Multiple Tables from Different Excel Sheets

6. Then you can repeat the steps above to select the table range in example2.xlsx. Please note that you must delete the text in Reference before selecting the table range in another Excel file, cause you’ll be automatically located to the referenced file.

How to Merge Multiple Tables from Different Excel Sheets

7. Add the text of table range in example2.xlsx using the same method. If you want to continue updating the data in the referenced files, don’t forget to check Create links to source data.

How to Merge Multiple Tables from Different Excel Sheets

8. You can also check Top row and Left column if you want to keep these labels in new created table.

How to Merge Multiple Tables from Different Excel Sheets

9. Click OK to confirm the settings. Now the two tables you selected are combined in the new file. You can make adjustments to it then. This is just a simple guide to merge tables from different Excel files. In fact, you can even combine more tables in this way.

Copyright Statement: Regarding all of the posts by this website, any copy or use shall get the written permission or authorization from Myofficetricks.

2 responses to “How to Merge Multiple Tables from Different Excel Sheets”

  1. SOPHUC says:

    […] together, is there a way to merge all the data at once? In fact, you can use clipboard to merge tables quickly instead of cut & paste […]

  2. […] together, is there a way to merge all the data at once? In fact, you can use clipboard to merge tables quickly instead of cut & paste […]

Leave a Reply to 24+ Microsoft Excel Tips for Beginners Who Work at Office Cancel reply

Your email address will not be published. Required fields are marked *