Working in Excel can be stressful when you need to refer to several workbooks. You may need to find a way to automatically combine worksheets in one master workbook to make your work easier. Excel offers its users various ways to combine Excel files. You can use some of the in-built commands to make your work easier.
In our guide below, we give you the different methods you can follow to automatically combine your Excel files.
Similar Read: How to extract Excel Sheet from Workbook: 4 easy ways
Merging an Excel file by manual copying
is in of the most straightforward methods you can use to merge two or more files into one master file.
1. Start by selecting the entire range of your file. You can also click anywhere within the worksheet and use the keyboard shortcut Ctrl + A to select the entire sheet.
2. Next, press the keyboard keys Ctrl + C to copy the selected range.
3. Head to your master file or the file where you want to merge all your files.
4. Select the location you want to paste your work and press the keys Ctrl + V.
5. Do this to all the files you need to combine.
Note
This method may be the fastest but it is safer used on smaller and simple worksheets. When working with large and complicated files, it may become hard to avoid human error when using this method.
Use Power Query
You can use the Power Query in Excel to combine your files automatically from multiple sources. The feature allows you to edit, merge, and import data in Excel.
1. Open your Excel workbook and move all the files you wish to combine in one directory..
2. Go to the main menu ribbon and click on the Data tab. This is the tab that contains the power query feature
3. Navigate to Get & Transform Data >> Get Data >> From File >> From Folder (If you don’t see these options click connections )
4. Here you will need to locate the folder where your files are. Click the Browse or add>browse for more button and select a directory.
5. Click the OK You will get a window listing all the files from the folder.
6. If your sheets are ready for merging, click the drop-down arrow next to Combine.
7. Choose Combine & Load/ add to the data mode
8. But if you wish to make some changes before combining, select the Transform Data Doing this will launch a window to re-organize, filter data, and manage columns and rows.
9. Click OK.
Use Excel 365 online or Google Sheets
If all you want is to work on the same file but as different people, then you should use an online spreadsheet. This makes work easier between you and your team. You can see edit History and real-time changes that the other party has just made. The two most popular online spreadsheets are Excel 365 and Google Sheets. All you need to do is sign up create a spreadsheet and share a link with Edit permissions to your colleagues.