How to merge excel files into one

Often, when working in a spreadsheet editor, it happens that the necessary information is located on separate worksheets or files. Users are faced with the task of combining several sheets and files into a single whole. You can, of course, implement the merging procedure by copying cells from one document and pasting them into another file, but this is inconvenient and inefficient. In the article, we will consider in detail several effective methods that allow you to implement the merging of spreadsheet documents into one.

Merging worksheets into one in a spreadsheet editor

Initially, consider such a procedure as combining worksheets into one document. Detailed instructions look like this:

1. We are opening worksheets, which we plan to combine into one file.
2. We move to the subsection, which has the name “Home”. Here in the “Format” command block we find an element called “Move or copy sheet” and click on it with the left mouse button.
3. In the list that opens, left-click on the “(new book)” button.
4. After completing all the steps, click on “OK”.
5. Similar operations must be performed with the remaining sheets of the spreadsheet document.

Combining information into one file

Often there are situations when it is necessary to combine some pieces of information into a single file. This procedure is performed quite easily using the spreadsheet editor tools. The most important thing is that the information located in the plates must be formatted in advance so as not to spend a huge amount of time on bringing the general plate to a normal appearance in the future. Detailed instructions look like this:

1. For example, we have the following data that needs to be transferred into one file. The merging process will only work properly if a few specific points are met. The worksheets that will go through the consolidation procedure should be brought to a single format with identical headings and information formats. In addition, the combined information should not contain empty lines and columns.

2. After we bring information from different workspaces to a single format, we need to implement the creation of a new worksheet. This can be done by clicking the left mouse button on a small dark plus sign located at the bottom of the spreadsheet editor interface next to the tabs of other sheets.

On a note! The merging procedure can also be implemented on the area where the necessary information is already located, but this can get confused due to the large amount of initial data.

3. At the next stage, we move to the section called “Data”. You can find it at the top, in the main menu of the spreadsheet editor. Here we find an element called “Consolidation” and click on it with the left mouse button.

4. In the list that appears, left-click on the elements “Amount”. Next, enter the coordinates of the cells that we plan to merge.

5. We perform similar actions with other information that we plan to combine into one file.
6. After carrying out all the necessary actions, click on “OK” to confirm the changes made.

Important! This method does not work well with text view information. To better combine data into one file, use the following method, which involves the use of macros.

Combining Files with VBA

Macros written in VBA are excellent for implementing the procedure for combining several spreadsheet documents into a single file. The procedure is not as complicated as it seems at first glance. The main thing is to follow the detailed instructions:

1. Initially, you need to make sure that all the necessary spreadsheet documents are located in one place on your personal computer.
2. At the next stage, you need to go into the Excel spreadsheet editor and create a new book in it, which will serve as a tool for combining the rest of the spreadsheet documents.
3. With the help of a special combination of hot keys “Alt + F11” we move to “Visual Basic”.

4. First, click on “Insert”, and then in the list that appears, click on the element with the name “Module” with the left mouse button.
5. Now here you need to write the following small code:

 Sub GetSheets()

Path = «your путь»

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

6. In the Path variable, you need to write the path to the place on the personal computer where the spreadsheet documents to be combined are located.
7. We save the spreadsheet document in the “XLSM” format in order to activate the created macros.
8. Let’s run the macro.
9. Ready! We have combined all the files in the folder into one spreadsheet document.

Conclusion

We found out that there are many ways to implement the combination of several files into one spreadsheet document. It is advisable to use the function called Consolidation only if the files contain only numerical information, since the function is not able to work correctly with text format data. The most effective way to qualitatively merge spreadsheet documents into one file without losing information is to use the integrated Visual Basic programming language and activate special macros. However, each user will be able to choose a more convenient way to combine spreadsheet documents.