How do you combine multiple Excel files into one Excel file?

Often, we have to deal with multiple Excel files, which is inconvenient. Things get easier if we can combine those Excel files into a single workbook. In this article, well show yo

How do you combine multiple Excel files into one Excel file?

Often, we have to deal with multiple Excel files, which is inconvenient. Things get easier if we can combine those Excel files into a single workbook. In this article, well show you the effective ways to Combine Multiple Excel Files into One Workbook with Separate Sheets.


Table of Contents hideDownload Practice WorkbookDataset Introduction4 Ways to Combine Multiple Excel Files into One Workbook with Separate Sheets1. Apply Move or Copy Operation to Combine Multiple Excel Files into One Workbook with Separate Sheets2. Combine Multiple Excel Files into One Workbook with Paste Link Feature3. Use Power Query to Combine Multiple Files into One Workbook with Separate Sheets4. Excel VBA to Combine Multiple Files into One Workbook with Separate SheetsConclusionRelated Articles

Download Practice Workbook

To practice by yourself, download the following workbook.Combine Excel Files.xlsx


Dataset Introduction

To illustrate, Im going to use a sample dataset as an example. For instance, the following figure represents three different Excel Files and each of them has a different Sheet.

Combine Multiple Excel Files into One Workbook with Separate Sheets

4 Ways to Combine Multiple Excel Files into One Workbook with Separate Sheets

1. Apply Move or Copy Operation to Combine Multiple Excel Files into One Workbook with Separate Sheets

Excel provides many different Features and we use them to perform various operations. One of such kinds is the Move or Copy. In our first method, well use this feature to Combine Multiple Excel Files into One Workbook with Separate Sheets. Therefore, follow the steps below to perform the task.

STEPS:

  • First, open the second Excel file (Combine Excel files 2).
  • Next, select the sheet (Sheet2) and right-click on the mouse.
  • Then, click Move or Copy.
Apply Move or Copy Operation to Combine Multiple Excel Files into One Workbook with Separate Sheets
  • As a result, the Move or Copy dialog box will pop out.
  • There, select Combine Excel files.xlsx from To book options and select (move to end) in the field Before sheet.
  • After that, press OK.
Apply Move or Copy Operation to Combine Multiple Excel Files into One Workbook with Separate Sheets
  • Again, open the third Excel file (Combine Excel files 3).
  • Select sheet (Sheet3) and right-click on the mouse.
  • Subsequently, select Move or Copy.
Apply Move or Copy Operation to Combine Multiple Excel Files into One Workbook with Separate Sheets
  • Then, in the To book field, select Combine Excel files.xlsx, and in the Before sheet, select (move to end).
  • Press OK.
  • Finally, youll see the combined Excel files in a single workbook but separate sheets.

Read More: How to Combine Multiple Excel Files into One Worksheet Using Macro


Excel offers multiple pasting options in the worksheet. Paste Link is one of them. We use this feature to link different worksheets from a single workbook or different workbooks. Here, well use this feature in this method. So, learn the steps below to know how to Combine Multiple Files into One Workbook.

STEPS:

  • Firstly, copy cell B2 from Sheet2 in the Combine Excel files 2.
Combine Multiple Excel Files into One Workbook with Paste Link Feature
  • Then, go to the destination workbook. In this example, the destination is Combine Excel files.
  • Here, select cell B2 or any other cell you want.
  • After that, select Paste Link from the Paste Options.
Combine Multiple Excel Files into One Workbook with Paste Link Feature
  • As a result, itll create a formula by itself like its shown in the below image.
Combine Multiple Excel Files into One Workbook with Paste Link Feature
  • Next, remove all the $ signs present in the formula and use the AutoFill tool to complete the series.
  • Consequently, itll return the source worksheet just like the way its shown in the following picture.
  • Now, repeat the steps for the third Excelfile.
  • Lastly, youll get your desired single workbook with separate sheets.

Read More: How to Combine Multiple Excel Sheets into One Using Macro (3 Methods)


Similar Readings:

  • How to Combine Two Scatter Plots in Excel (Step by Step Analysis)
  • Combine Name and Date in Excel (7 Methods)
  • How to Combine Two Bar Graphs in Excel (5 Ways)
  • Combine Graphs with Different X Axis in Excel
  • How to Combine Columns into One List in Excel (4 Easy Ways)

3. Use Power Query to Combine Multiple Files into One Workbook with Separate Sheets

Excel Power Query Editor is helpful in many cases. We can merge multiple Excel files into one file using this feature. Hence, follow the below process to combine the files.

STEPS:

  • In the beginning, open the first workbook (Combine Excel files).
  • Then, go to Data Get Data From File From Workbook.
Use Power Query to Combine Multiple Files into One Workbook with Separate Sheets
  • As a result, the Import Data window will pop out. Here, select Combine Excel files 2 and press Import.
Use Power Query to Combine Multiple Files into One Workbook with Separate Sheets
  • After that, the Navigator window will pop out. There, press Load.
  • Consequently, itll add Sheet2 from the second workbook as a Table.
  • Again repeat the process to get Sheet3 from the third workbook.
  • In the end, youll get all the sheets from different Excel files in your chosen workbook.

Related Content: How to Combine Rows from Multiple Sheets in Excel (4 Easy Methods)


4. Excel VBA to Combine Multiple Files into One Workbook with Separate Sheets

If you dont want to go through all the details mentioned in the previous methods, you can use a single VBA Code to merge all the Excel files you want. Well use Excel VBA in our last method to combine multiple Excel files into a single workbook with separate sheets. Therefore, learn the process given below to carry out the task.

STEPS:

  • Firstly, open the destination workbook. Here, its Combine Excel files.
  • Next, select Visual Basic from the Developertab.
Excel VBA to Combine Multiple Files into One Workbook with Separate Sheets
  • Then, select Module in the Inserttab.
Excel VBA to Combine Multiple Files into One Workbook with Separate Sheets
  • As a result, the Module window will pop out.
  • There, insert the code given below.Sub CombineFiles() Dim FilesSelected, i As Integer Dim tempFile As FileDialog Dim MainBook, sourceBook As Workbook Dim Sheet As Worksheet Set MainBook = Application.ActiveWorkbook Set tempFile = Application.FileDialog(msoFileDialogFilePicker) tempFile.AllowMultiSelect = True FilesSelected = tempFile.Show For i = 1 To tempFile.SelectedItems.Count Workbooks.Open tempFile.SelectedItems(i) Set sourceBook = ActiveWorkbook For Each Sheet In sourceBook.Worksheets Sheet.Copy after:=MainBook.Sheets(MainBook.Worksheets.Count) Next Sheet sourceBook.Close Next i End Sub
Excel VBA to Combine Multiple Files into One Workbook with Separate Sheets
  • After that, close the Visual Basicwindow.
  • Now, under the Developer tab, select Macros.
Excel VBA to Combine Multiple Files into One Workbook with Separate Sheets
  • Consequently, the Macro dialog box will pop out, and select CombineFiles in the Macro name.
  • Press Run.
  • As a result, a Browse window will pop out. There, select the files you want to combine and press OK.
  • Eventually, youll get all your desired workbooks in a single Excel file with separate sheets.

Read More: Excel VBA: Combine Date and Time (3 Methods)


Conclusion

Henceforth, you will be able to Combine Multiple Excel Files into One Workbook with Separate Sheets using the above-described methods. Keep using them and let us know if you have any more ways to do the task. Dont forget to drop comments, suggestions, or queries if you have any in the comment section below.


  • How to Combine Sheets in Excel (6 Easiest Ways)
  • Merge Multiple Excel Files into One Sheet (4 Methods)
  • How to Merge Multiple Sheets into One Sheet with VBA in Excel (2 Ways)
  • Combine Columns into One List in Excel (4 Easy Ways)
  • How to Merge Columns in Excel (4 Ways)

Video liên quan