How can we Merge Excel Files on Windows 10

Table of Contents

Toggle

If you are working in Microsoft Excel, you may find that your data is somewhat difficult to track because the records are spread out on separate sheets, pivot tables, etc. However, you don’t always have to use multiple spreadsheets or Excel files to work on your data, especially if you work in a team.

To help you organize your data, you can merge the data into Excel. You can merge spreadsheets from separate files, merge separate Excel files into one, or use the consolidation function to merge your data.

Here’s how you can use these methods to merge Excel files and data.

To merge two Excel files into one by copying sheets

If you only need to merge a few Excel files, you can copy or manually move sheets from one file to another. This is how it does it:

Open the workbooks you want to merge.
In the source workbook, select the worksheets you want to copy to the main workbook.
To select multiple sheets, use one of the following techniques:
To select adjacent sheets, click the first sheet tab you want to copy, hold down the Shift key, and then click the last sheet tab. It selects all intermediate worksheets.
To select non-adjacent sheets, hold down the Ctrl key and click each sheet tab individually.
When all worksheets are selected, right-click one of the selected tabs, then click Move or Copy .
Do the following in the Move or Copy dialog box :
From the Move selected sheets to book drop-down list, select the target workbook in which you want to merge other files.
Specify exactly where to paste the tabs of the copied sheet. In our case, we select the “Move to End” option.
Check the Create Copy checkbox if you want the original spreadsheets to remain in the source file.
Click OK to complete the merge process.

When you copy sheets manually, please note the following limitation imposed by Excel: it is not possible to move or copy a group of sheets if one of them contains a table. In this case, you must either convert a table into a range or use one of the following methods that do not have this restriction.

Use the INDIRECT formula

The following method has some drawbacks and is a little more complicated. It works if your files are in a systematic order and only certain values need to be imported. You use the INDIRECT formula to build your file and cell references. This way, the original files are preserved and the INDIRECT formula only looks for values within those files. If you delete the files, you get #REF! errors.

Let’s take a closer look at how the formula is constructed. The INDIRECT formula has only one argument: the link to another cell, which can also be located in another workbook.

Copy the first source cell.
Paste it into your main file with the “Paste special” command (Ctrl computer_key_Ctrl+ Alt computer_key_Alt+ v computer_key_V). Instead of pasting it normally, click on “Link” in the lower left corner of the Paste special window. This way you extract the full path. In our case we have the following link: =[160615_Examples.xlsm]Thousands!$C$4
We will now wrap the INDIRECT formula around this path. We also separate it into file name, sheet name and cell reference. This way, we can easily change one of these references later, for example, for different versions of the same file. The complete formula is as follows: =INDIRECT(“‘”&$A3&$B3&”‘!”&D$2&$C3)
Important – please note: this function only works if the source folders are open.

Combining Excel files with VBA

If you want to combine the sheets from several workbooks in no time, it is best to write a simple VBA macro. This is especially useful if you perform this task regularly.

First, make sure that all the files you want to combine are in the same folder on your computer. Next, create a new Excel spreadsheet that merges all the files.

Go to the Developer tab and select Visual Basic. Click Insert > Module.
Copy and paste the following code from an ExtendOffice guide

Sub GetSheets()
Path = "C:[PATH TO FILES]"
Filename = Dir(Path & "*.xls")
Do While Filename <> ""
Workbooks.Open Filename:=Path & Filename, ReadOnly:=True
For Each Sheet In ActiveWorkbook.Sheets
Sheet.Copy After:=ThisWorkbook.Sheets(1)
Next Sheet
Workbooks(Filename).Close
Filename = Dir()
Loop
End Sub

Be sure to change the path to where the files are stored on your computer.

Then save your workbook as an XLSM file so that the macros are enabled. Then run the macro, and you should see that you have a single workbook that contains all the sheets of all the files in the folder.

https://techcommunity.microsoft.com/t5/excel/merge-multiple-excel-files-into-one-the-formatting-of-the-file/m-p/329681

BlogWolf