Merge Excel Files

MS Excel, or Microsoft Excel, is widely used spreadsheet software that helps us record huge amounts of data within the cells across several worksheets. Although each Excel file can contain vast amounts of data, there may be cases when we may sometimes record data in different Excel files. For example, suppose we enter month-wise data of sales in different Excel files. However, when we need to read the recorded data, it becomes difficult as we have to open all the Excel files and switch between these opened files individually. This also takes time and impacts the performance of the system. However, merging these Excel files into one file could be very useful, allowing us to read or access data in a single place. There can be various cases when we might need to merge Excel files.

We discuss a step-by-step tutorial on merging different Excel files into a single workbook in this tutorial. This will help us easily read the data in one file instead of switching between each file separately.

How to merge Excel files?

Getting the data in multiple Excel files is quite usual, especially in offices and businesses. When multiple people work on the same task and create separate reports, it is better to merge them in a single file to read them easily. Since an Excel file consists of multiple worksheets, we can merge data from different Excel files into different worksheets of one Excel file (also called workbook).

Excel is powerful spreadsheet software and offers multiple ways to complete any typical task. Likewise, when merging Excel files, we can take advantage of various methods. Each method can be useful in specific use cases. Some of the common methods to merge Excel files are discussed below:

Method 1: Merge Excel files using the Copy-Paste

The basic method to merge Excel files is to copy the contents from one file and paste it to another. This traditional method includes a few steps and can be helpful when we need to merge Excel files so that the data from each file gets combined into different worksheets in a single Excel file.

• First, we must select a range in an Excel file (workbook) and copy the data using the keyboard shortcut ‘Ctrl + C‘. If we need to copy the data from the entire workbook’s sheet, it is better to select the entire sheet using the keyboard shortcut ‘Ctrl + A‘ before copying the sheet data. In our example, we select the entire data of an Excel file named xlsx.
  
• After copying the data from one Excel file (Jan.xlsx), we need to go to another file (let’s say xlsx) and head to an empty spreadsheet. We can also create a new sheet with a custom name if required. For example purposes, we create a sheet named ‘Jan’ and paste the copied data in this sheet using the keyboard shortcut ‘Ctrl + V‘.
  
• Now, suppose we have more Excel files, such as xlsx, Mar.xlsx, Apr.xlsx, etc. We can open each file separately and copy the data similar to the above steps.
  
  On the other side, we can create more sheets in another Excel file with names like Feb, Mar, Apr, …, etc. We can paste the data from each separate file into these created sheets, as shown below:
  
  This way, we can merge different Excel files into a single Excel workbook.

The merging of Excel files using the manual copy-paste technique is the fastest and most straightforward. However, it is typically useful when dealing with smaller data sets. Also, there are chances of human errors while copy-pasting the data from one file to another. It is the reason why we must use this method only for basic personal use cases.

Method 2: Merge Excel files using the Move and Copy

Another easy way to merge multiple Excel files into another or new file is to use Excel’s Move and Copy feature. We can access this feature from the Sheets tab. Suppose we want to combine all the excel files into a new file or workbook. For this, we need to select on-screen instructions as discussed below:

• First, we need to create a new Excel file and keep it active in the background.
  
• Next, we need to open all the other Excel files that we want to merge into a new file. If all the corresponding files are located in the same folder, we can select all these files by using the keyboard shortcut ‘Ctrl + A’ and pressing the ‘Enter‘ key. This will open all the desired Excel files simultaneously, saving our time to some extent.
  
• After that, we need to maximize the first file that we wish to merge into a new file. We must press the right-click on the respective sheet from the Sheet tab and choose the ‘Move or Copy‘ option.
  
• In the Move or Copy window, we must click the drop-down list under the ‘To book‘ option. It will list all the Excel files we have opened and kept active in the background. Here, we must choose a new Excel file we created to hold data from all other files.
  
• After selecting the Excel file to merge other files, we must select the location on the Sheet tab where we want to place the merged file. We need to choose the sheet from the list, and our data will be merged before the selected sheet. Also, we can merge the data at the end of the sheet tab in the new Excel file.
  
• Lastly, we must tick the checkmark associated with the ‘Create a copy‘ option and click the OK
  

This will merge the selected sheet into a new Excel file at the chosen location in the Sheet tab.

After completing the merging process, we can change the sheet name on the Sheet tab for the new Excel file. Also, unnecessary sheets can be deleted accordingly.

Similarly, we must open other Excel files individually and follow the above steps multiple times until all the Excel files are merged into a new file. Once all the files are merged, we can save the new file into the desired location.

Method 3: Merge Excel files using the VBA

When merging many files into a single file, it is best to use Excel’s VBA (Visual Basic for Applications) to automate the entire process. Using the VBA to merge Excel files into a single file is the fastest and most straightforward method. However, this method only works when we open Excel files to be merged physically or in the memory. In that case, we will get a run-time error.

We can follow the below steps to combine or merge several Excel files into one:

• First, we need to put (copy) all the Excel files into the same folder that needs to be merged. Also, the master Excel file (where all files will be merged) should be in the same folder. After that, we must open the master Excel file (i.e., New File).
  
• Next, we need to press the keyboard shortcut ‘Alt + F11‘ to open the VBA editor. It will look like this:
  
• In the VBA editor, we need to navigate the Insert tab and select Module to open VBA Code Edit window.
  
• In the VBA code window, we must copy-paste the following codes:

After copy-pasting the code, our code window will look like this:

• After copying the code, we need to go to the Run > Run Sub/UserForm. Also, we can press the key F5 to execute the copied code quickly.
  
• After running the codes, Excel will open the standard explorer window. We need to select all the Excel files to be merged using this file explorer. We must ensure not to select the master file from the folder. To select the desired files, we can click on each file while holding the Ctrl After selecting files to be merged, we must click the Open button.
  
• After that, Excel will merge all the selected files into the master file. Depending on how many files we have selected to be merged, it may take a few seconds to several minutes. After completing the process, Excel displays a notification explaining how many files have been processed and sheets merged into the master file. We must click the OK button from the notification window, as shown below:
  
• Lastly, we must close the VBA window by clicking the close button of the VBA window.
  
  Later, we can rename the sheet name in the master file if required. Also, we must delete the empty or unnecessary sheets for a better view and save a corresponding workbook accordingly.
  

Method 4: Merge Excel files using the Power Query

Power Query is another useful way to merge multiple Excel files into one file. Although this method takes a long time, it works in different cases most of the time. The main advantage of using Power Query to merge Excel files is that it allows us to transform the data in the workbook.

To use this method, we need to make sure that the data is structured in the same way in the Excel files, which means that they should contain the same number of columns in the same order. While using Excel’s Power Query, we need to go through the following steps:

• First, we need to store all the Excel files in the same folder because the folder will be supplied into the power query tool to load source Excel files to be merged.
  
• After putting all the excel files into one folder, we have to create a new excel file in which we will store the data from other source files. We should not create this new file in the same folder where the source files are also stored. We need to open this newly created excel file and launch Power Query.
• To open Power Query, we must navigate through the Data tab > New Query > From File > From Folder.
  
  In Excel 2013 and earlier versions, we can download the Power Query tool from the Microsoft Official site and install it normally. After installing the Power Query, we will see a new tab on the Excel ribbon with the name Power Query. From the Power Query tab, we must navigate through the Power Query > From File > From Folder.
  
• In the next window, we must choose the desired location where all the source files are stored. We can click on the Browse button to select the folder. After that, we must click the OK
  

• In the next window, Excel will display details of all the source files. We must click on the Combine button and select the ‘Combine & Load‘ option.
  
• We need to select the desired table from any supplied files in the next window. The selected table from the given Excel file will act as a template for the combined Excel file. The final Excel file will keep the columns based on the template from the source files. If any Excel file has additional columns, the Power Query tool will ignore these columns.
  
• After selecting the template, we must click the OK button, and corresponding Excel files will be merged to the destination file (new Excel file).
  
  In the above image, we can see that the supplied Excel files have been merged. The Power Query tool has also added file names for the corresponding data in the first column. However, we can arrange the data accordingly and save the merged file into the desired location for future use.

Method 5: Merge Excel files by CSV conversion

While merging Excel files into one file, we can also use an alternative method where we first need to convert them to CSV formats and then merge using specific commands. Suppose we have two excel files (Book1.xlsx and Book2.xlsx) to be merged into a new file.

We can follow the below steps to merge these Excel files into a single file:

• First, we need to convert both the Excel files into CSV format. Therefore, we need to open the first file and go to File > Save As.
  
• In the Save As dialogue box, we must save our file using the ‘CSV (Comma delimited) (*.csv)‘ format, as shown below:
  
  Similarly, we must convert all Excel files into CSV formats.
• After converting the files into CSV formats, we must put or copy all files into the same folder. In our case, we put Book1.csv and Book2.csv into the same folder.
  
• Next, we must launch the command prompt window in the same folder. We must press the right-click while holding the Shift key and select ‘Open in Terminal Window‘.
  
  Also, we can launch the CMD window from the search and navigate the corresponding folder. In our case, the CMD window looks like this:
  
• Since we want to merge all the CSV files of the folder, we must enter the following command and press the Enter This will create a new CSV file in the same folder containing the data from other files.

After pressing the Enter key, the CMD window will notify us when the execution is complete.

• After the given Excel files have been merged into a new CSV file, we must open it into Excel. Again, we must navigate the File > Save As and save our file as a typical Excel file by selecting the format ‘Excel Workbook (*.xlsx)‘.
  
  This will create an Excel file with merged data from all the selected or desired Excel files.
  

We can use any of the above methods to merge excel files. However, we must practice each method for better understanding.