How to combine and merge files, sheets and data on Microsoft Excel

Occasionally, you may find that the data you require in Microsoft Excel is distributed across multiple sheets or even several files. In such situations, consolidating all this information into a single document can be significantly more convenient.

A simple solution is manually copying and pasting the necessary cells onto a single sheet. However, depending on the volume of data you’re dealing with, this approach can be time-consuming and labor-intensive.

Alternatively, there are more efficient methods to achieve the same objective. These techniques enable you to quickly and effortlessly merge sheets or files in Excel.

Combining Multiple Microsoft Excel Files

Suppose you possess multiple Excel files, each potentially containing numerous sheets. You can merge them into one file using the “Move or Copy Sheet” command. While this method has its limitations, it is a swift and straightforward approach.

Here are the steps to follow:

1. Open the sheets you wish to merge into the same workbook.
2. Go to the Home tab on the top ribbon.
3. Within the “Cells” group, select Format.
4. Choose Move or Copy Sheet. 
5. This action opens the “Move or Copy” window. In the “To book” dropdown, you can specify the target spreadsheet to consolidate all your sheets. You can select “(new book)” to create a new file or opt for an existing file.
6. Utilize the “Before sheet” box to designate where to position the target sheet (this will be blank if you create a new book). When you’re ready, click OK.
7. You can choose “Create a copy.” if you want to keep the original file intact.
8. Repeat this process for all the sheets you wish to merge. Push down the “Ctrl” key on your keyboard while clicking the tabs to select multiple sheets to expedite the process.
9. After completing these steps, save your new master document. You can also reverse this process by splitting a large CSV file into separate files.

Combining Excel Sheets Into One File With VBA

Instead of performing the aforementioned manual combination, a quicker approach is to use a VBA macro to combine your Microsoft Excel files. This method is particularly useful for merging multiple Microsoft Excel files. You’ll need to enable the “Developer” tab to use VBA.

Follow these steps:

• Make sure all files to combine are in the same folder.
• In the spreadsheet, navigate to the Developer tab. Within the “Code” section, select Visual Basic to access VBA.
• In the VBA window, go to ThisWorkBook, open the Insert menu, and go to the Module option.
• This will produce a new module where you should input the following code:

 

Sub Merge_Multiple_Sheets_Column_Wise()

Dim Work_Sheets() As String
ReDim Work_Sheets(Sheets.Count)

For i = 0 To Sheets.Count - 1
Work_Sheets(i) = Sheets(i + 1).Name
Next i

Sheets.Add.Name = "Combined Sheet"

Dim Column_Index As Integer
Column_Index = Worksheets(1).UsedRange.Cells(1, 1).Column

Dim Row_Index As Integer
Row_Index = 0

For i = 0 To Sheets.Count - 2
Set Rng = Worksheets(Work_Sheets(i)).UsedRange
Rng.Copy
Worksheets("Combined Sheet").Cells(Row_Index + 1, Column_Index).PasteSpecial Paste:=xlPasteAllUsingSourceTheme
Row_Index = Row_Index + Rng.Rows.Count + 1
Next i

Application.CutCopyMode = False

End Sub

 

Excelcademy inspires the code.

• Now, run the process.

Close the Visual Basic window to return to your spreadsheet and view the result. Remember to save the changes!

Merging Excel Data Into One Sheet

Sometimes, you may want to consolidate multiple datasets and present them as a single sheet. This is relatively simple in Microsoft Excel, provided your data is organized and formatted correctly beforehand.

There are two critical conditions for this process to work seamlessly:

1. The sheets you’re consolidating must have the same layout, with identical headers and data types.
2. There should be no blank rows or columns in the data.

Follow these steps:

1. Organize your data to meet these specifications.
2. Create a new worksheet (though you can perform this in an existing sheet with existing data, using a new sheet is typically more straightforward).
3. In the new sheet, select the upper-left cell where you want to place the consolidated data.
4. Go to the Data tab.
5. Within the “Data Tools” section, click Consolidate.
   
6. In the “Function” dropdown, select your preferred summary function (the default is “Sum,” which adds values together).
7. Click the up arrow button in the “Reference” field. After this select the required area.
8. If the data is in another file, use the “Browse” button. 
9. Highlight the range you wish to consolidate.
10. Click Add to add the range to “All references.” 
11. You can also choose which labels to carry using the “Use labels in” checkboxes.
12. Repeat step five until you’ve selected all the data you want to consolidate.
13. Finally, click OK.

Excel will now consolidate and display the data. Note that the rows are expandable; you can open each to view values from different sheets.

In the consolidation window, you can select “Create links to source data” if you plan to continue updating the data in other sheets and want this new sheet to reflect those changes. 

Merging Excel Data With Style

Merging data isn’t just a technical task; it’s a strategic decision that affects your workflow, analysis, and overall efficiency. While the methods discussed here can help you quickly merge your data, combining sheets and files in Microsoft Excel can still be complex and messy.

This underscores one of the essential lessons about Microsoft Excel: planning is always best. Avoiding the need to merge different datasets is preferable. Still, if you must, now you have a couple of ways to merge Microsoft Excel data.

---