How to Merge Two or Multiple Excel Files Into One [2024 Guide]

When you are working on multiple Excel files at once, you may waste significant time flipping between Excel sheets to find information. However, if you merge Excel files into one, you can save up time and work more efficiently. Besides that, merging multiple Excel files is also useful when presenting data, such as presenting a sales report of multiple products. Therefore, this guide will talk in detail about how to merge Excel files in different ways. So, let's head right to it!

Part 1. How to Merge Two or Multiple Excel Files Into One without VBA

If you want to merge Excel files to present data effectively, then you can do that easily without VBA. All you have to do is use UPDF and utilize its merge feature.

UPDF is a powerful document merger and converter tool that can instantly merge two or multiple Excel files into one. Its "batch combine" feature lets you select all the Excel files you want to merge and then instantly merge them into a single file.

The key features of UPDF are as follows:

1. Offers a click-based interface to merge Excel files into one file.
2. Can merge dozens of Excel files at once.
3. Allows you to rearrange the position of Excel files before merging.
4. Can create a single PDF file of merged Excel files for easy sharing.
5. Lets you share the merged Excel file as a shareable link or via email.
6. The merging feature is available in Windows and Mac systems.

In short, UPDF offers the simplest yet powerful way to merge Excel files into one. Therefore, hit the below download button to download UPDF and merge two or multiple Excel files instantly.

Follow the below steps to learn how to merge Excel files with UPDF:

Step 1. Convert the Excel files to PDFs by clicking File > Save as > PDF.

Step 2. Download and install UPDF on your PC or Mac. Launch the tool and click "Batch" from the main interface.

Step 3. Click the "Combine" option.

Step 4. Click "Add Files" and select all the PDF-based Excel files you want to merge.

Step 5. Rearrange the order of files if you want, and then click "Apply". Once done, UPDF will merge all the Excel files into a single PDF file.

Step 6. To save the merged file back as Excel, click the "Export PDF" tab from the right sidebar and select "Excel".

That's it! This way, you can quickly merge Excel files with UPDF and easily present and share them with others. The best thing is that UPDF is more than an Excel merging tool. To learn more, read on to Part 4.

Part 2. How to Merge Two Excel Files into One By Copying Sheets

If you want to merge two Excel files into one, you can simply copy sheets from one file and paste them into another. For instance, if you have separate sales sheets for January and February, you can combine the sheets into one Excel file manually.

Here are the steps on how to merge two Excel files by copying sheets:

Step 1. Open the Excel workbooks you want to merge.

Step 2. Select the sheets from the workbook you want to merge into the main workbook. You can select multiple sheets using one of the below ways:

1. To select adjacent sheets, click the first sheet tab you intend to copy, press and hold the Shift key, and then tap the last tab. This way, all the sheets will be selected.
2. To select non-adjacent sheets, click the "Ctrl" key and then individually tap each sheet to select them.

Step 3. Once all the sheets are selected, right-click any one of the sheets and click "Move or Copy".

Step 4. In the pop-up window, perform the following actions:

1. Under "Move selected sheets to book", select the main workbook where you want to merge the files.
2. Under the "Before sheet", select where you want to insert it. Mostly, we prefer the "move to end" option.
3. Tick the "Create a copy" checkbox if you still want to keep the sheets in their original workbooks.

Once done, click "OK" to complete the merge.

This way, you can manually merge two Excel files into one by copying sheets.

Part 3. How to Merge Excel Files with VBA (Complicated)

Visual Basic for Applications (VBA) is a programming language that allows you to automate tasks within Excel. So, if you want to merge multiple Excel files, you can automate the process through VBA macro.

Below is a VBA code that will automatically copy all sheets from all Excel files and merge them into one workbook.

Sub MergeExcelFiles()
  Dim fnameList, fnameCurFile As Variant
  Dim countFiles, countSheets As Integer
  Dim wksCurSheet As Worksheet
  Dim wbkCurBook, wbkSrcBook As Workbook
  fnameList = Application.GetOpenFilename(FileFilter:="Microsoft Excel Workbooks (*.xls;*.xlsx;*.xlsm),*.xls;*.xlsx;*.xlsm", Title:="Choose Excel files to merge", MultiSelect:=True)
  If (vbBoolean <> VarType(fnameList)) Then
    If (UBound(fnameList) > 0) Then
      countFiles = 0
      countSheets = 0
      Application.ScreenUpdating = False
      Application.Calculation = xlCalculationManual
      Set wbkCurBook = ActiveWorkbook
      For Each fnameCurFile In fnameList
          countFiles = countFiles + 1
          Set wbkSrcBook = Workbooks.Open(Filename:=fnameCurFile)
          For Each wksCurSheet In wbkSrcBook.Sheets
              countSheets = countSheets + 1
              wksCurSheet.Copy after:=wbkCurBook.Sheets(wbkCurBook.Sheets.Count)
          Next
          wbkSrcBook.Close SaveChanges:=False
      Next
      Application.ScreenUpdating = True
      Application.Calculation = xlCalculationAutomatic
      MsgBox "Processed " & countFiles & " files" & vbCrLf & "Merged " & countSheets & " worksheets", Title:="Merge Excel files"
    End If
  Else
      MsgBox "No files selected", Title:="Merge Excel files"
  End If
End Sub

Code Source: Ablebits

You can add the above VBA macro to the Excel workbook by following the below steps:

1. Click Alt + F11 to launch the Visual Basic Editor.
2. From the left pane, right-click "ThisWorkbook" and tap Inset > Module.
3. Paste the code.

Afterward, you can use the macro by following the below steps:

1. Open the Excel workbook in which you want to merge sheets.
2. Click Alt + F8 to launch the Macro dialog.
3. Select "MergeExcelFiles" and click "Run".
4. Select all the workbooks you want to merge and tap "Open". To select multiple files, press and hold "Ctrl" and then select the file names.

Once done, wait for a few seconds for the macro to complete. Once done, you will see that all the selected files are merged into the main workbook.

Part 4. How to Combine Excel Files into One with Ultimate Suite

If you want an easier solution to merge Excel files compared to VBA, then Ultimate Suite by Ablebits is another useful way to do it. It is an Excel add-in that can assist in different day-to-day activities, including merging Excel files.

Follow the below steps to learn how to merge Excel files with Ultimate Suite:

Step 1. Install the Ultimate Suite add-in in Excel.

Step 2. Open the main Excel workbook. Click the "Ablebits Data" tab from the top and tap Merge group > Copy Sheets > Selected Sheets to one Workbook.

Step 3. Select all the Excel files you want to merge and click "Next".

Step 4. Select the additional options if you want and then tap "Copy".

Once done, all the selected Excel files will merge into the main Excel workbook.

Part 5. Bonus Tip: A Useful Excel Converter

UPDF not only dominates as a powerful Excel merging tool but also as a reliable Excel converter tool. With UPDF, you can convert Excel files to other formats and make other customizations. Below is a quick glimpse of what else you can get with UPDF:

• Convert Excel Files to Other Formats: It can convert Excel files to various other formats, like PDF, HTML, image, text, PPT, CSV, etc.
• AI Chatbot to Analyze Excel Sheets: It provides an AI chatbot (UPDF AI), which helps you ask questions about Excel sheets in human-like conversations.
• Protect Excel Files: It can password-protect your merged/converted Excel files to protect them from unauthorized access.
• Annotate Excel Files: It allows you to highlight, draw, mark, add stamps, and do other annotations with Excel files.

Overall, UPDF is your all-in-one tool to merge, convert, and customize Excel files. So, download and purchase UPDF and leverage its features in your day-to-day work.

Conclusion

Merging Excel files is useful for many reasons. It helps to easily work on multiple files at once and also makes it easy to present/share files with others. That's why this guide discussed in detail multiple ways to merge Excel files. So, you can choose any of the above methods that suit you. However, if your main purpose is to merge Excel files for presentation, then UPDF is the best tool. Its instant and PDF-based merging makes it easy to merge two or multiple Excel files and then easily share them with others.