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.
2. Click Consolidate in Data Tools.
3. Click the up arrow on the left of Browse… and switch to the Excel file example.xlsx.
4. Drag your mouse to select the range of table. It will be filled in the textbox of Reference automatically.
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.
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.
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.
8. You can also check Top row and Left column if you want to keep these labels in new created table.
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.
[…] 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 […]
[…] 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 […]