How to Consolidate Multiple Excel Files Into One (4 Methods)

Most likely, you know the ways to consolidate multiple sheets into one in Excel. Sometimes, you may need to consolidate two or more Excel files into one file. It gives your work pace and accelerates your efficiency. Generally, it is needed when you record your data month or year-wise into several Excel files. But lastly, you need to merge them into one worksheet. You may think it is a tough job to do. But wait a bit! Hold your nerve and go through this article. In this article, we’re going to show you how to consolidate multiple Excel files into one. So, let’s get started.


Download Practice Workbook

Download the following practice workbook. It will help you to realize the topic more clearly.


4 Methods to Consolidate Multiple Excel Files into One in Excel

To consolidate the files, first, you need to create two or more files and put them into the same folder. The first and foremost object here is to save the files into one specific folder. Otherwise, the operation can’t be done. We have taken two sheets ofSales of January and Sales of February in Excel.

Dataset

Not to mention, we have used the Microsoft 365 version. You can use any other version at your convenience.


1. Using Consolidate Tool

You can use the Consolidate tool to merge multiple sheets into one sheet. In our very first method, we won’t directly merge the sheets; instead, we’ll merge the sales using a function to get every Sales Rep’s total sales over two months. Follow the steps to do it.

📌 Steps:

  • Firstly, go to any cell where you want to consolidate the files.
  • Then, move to the Data tab >> Data Tools >> Consolidate.

Using Consolidate Tool to consolidate multiple excel files into one

  • Select the function from the Function drop-down box that you want to apply. Here we apply the Sum to get the arithmetic sum.

Note: The SUM function is the default function in the Consolidate feature.

  • Next, click on the up arrow icon from the Reference If you have sheets in other files then you will have to click Browse.

Consolidate window to consolidate multiple excel files into one

  • Later, go to the first source sheet and select the data range.
  • Then hit ENTER.

Adding Reference to consolidate multiple excel files into one

  • After that, click the Add button to add that reference and check the Left column as Use labels in.

  • Following the same procedures, select the data range from the other sheet.

  • Finally, just mark the Left column from the Use labels in section to set the labels and link to the source data and hit the Add button.

Now you can see, the sales from the two files are merged using the Sum function.

Using consolidate tool to consolidate multiple excel files into one

Read More: How to Consolidate Data in Excel from Multiple Workbooks (2 Methods)


2. Applying Copy and Paste link Option

There are options in Excel that allow users to refer to cells in other Excel files or workbooks. This enables the combination of Excel files by referencing them in the master file. For this, you have to use the Paste Link option. We have demonstrated to you the steps to do it.

📌 Steps:

  • Firstly, we must open both Excel files. Then, copy the first cell from the source worksheet to which we wish to refer.
  • For copying, select the first cell and under the Home tab, pick up the Copy command.

Applying Paste Link option to consolidate multiple excel files into one

Note: You can also copy the cell by pressing CTRL + C on the keyboard.

  • Consequently, jump into the master file where you want to paste the files. Right-click on the cell and choose Paste Link under Paste Options.

Paste Link option

  • This will provide the correct formula. It will look like this in the image.
  • To see the additional cells, alter the formula from absolute to relative cell referencing by deleting the dollar signs ($). See the image’s formula box to get a proper idea.

Reference cell to consolidate multiple excel files into one

  • Now, drag the Fill Handle tool both horizontally and vertically to get the value of the other cells.

Fill Handle

Finally, you have pasted the data from one file, just like the image below.

  • Now, we want to add the Sales of Feb here in the consolidated file. For doing this we need to apply the same procedure we have mentioned here by using the Paste Link option from the Paste Options menu.
  • You have to remove the absolute reference otherwise it will link up only the copied value.

Eventually, you will get the result shown in the above image.

Read More: How to Automate Consolidation in Excel (with Easy Steps)


Similar Readings


3. Employing the Power Query Feature

To consolidate multiple sheets into one, the Excel Power Query feature is the most helpful one. Though it takes a large number of steps, it is more sophisticated than the other methods. In other words, you will have enormous options to find if you use the feature. Follow the below steps to do that.

📌 Steps:

  • Firstly, in the present worksheet where you want to consolidate the files.
  • Secondly, go to the Data tab >> choose Get Data >> From File >> pick From Folder.

Employing Power Query feature to consolidate multiple excel files into one

  • A window appears showing the two files that you want to merge. Select Combine & Transform Data.

Combine and Transforming data to consolidate multiple excel files into one

  • Eventually, it opens a Combine Files window. Move to Parameter2[1] and press OK.

  • Subsequently, it takes you to the Power Query Editor. Choose the Table of your source file and hit Close & Load.

Close and Load Window

Apparently, the table of your source dataset has been loaded into the new worksheet. See the image below.

  • For loading the other file, move to the Queries and Connections. Pick up the Exceldemy folder.

Queries and Connection

  • Automatically, it loaded the other Excel file in your folder and hit Close & Load again.

Consequently, the other file has been also loaded into the new worksheet just like the image below.

Read More: How to Consolidate Data from Multiple Workbooks in a Single Worksheet


4. Using VBA to consolidate Multiple Excel Files

We can use the VBA Macros to consolidate multiple Excel files into one workbook. To do this, we have chosen two diverse ways. In a first way, you have to specify the File Path (the location of your desired Excel files on your PC) inside the VBA code. On the other approach, you’ll select the files from File Explorer after running the VBA code. Both methods will add the files to your consolidated workbook.


4.1 Specifying the File Path

In this step, we have specified the file path in the VBA code. This indicates that after running the VBA code, it adds the files available in your same folder automatically.

📌 Steps:

  • Initially, go to the Developer tab >> Choose Visual Basic.

Using VBA to consolidate Multiple Excel Files into One

  • Sequentially, move to the Insert tab >> Module >> Module1.

  • Write up the VBA code in the General box.
Sub Merge_Sheets()
FilePath = "G: \Exceldemy\"
FileAddress = Dir(FilePath & "*.xlsx")
Do While FileAddress <> ""
Workbooks.Open Filename:=FilePath & FileAddress, ReadOnly:=True
For Each FileSheet In ActiveWorkbook.Sheets
FileSheet.Copy After:=ThisWorkbook.Sheets(1)
Next FileSheet
Workbooks(FileAddress).Close
FileAddress = Dir()
Loop
End Sub
  • Press the F5 key.

VBA code by Specifying the File Path

Finally, your previous Excel files have been added to the new one. See the image, where you can see that the sheet name is similar to the file names.

Read More: Data Validation and Consolidation in Excel (2 Examples)


4.2 Selecting Files from File Explorer

In this method, after running the code, the system takes you to the File Explorer of your PC. You can choose the desired files from there. Then, it consolidates into the master file.

📌 Steps:

  • First of all, go to your General box by following the steps of method 4.1.
  • Secondly, in the General box write up the VBA code and press the F5 key to run the code.
Sub ConsolidateFiles()
Dim FileList, CurrentF As Variant
Dim nFiles, nSheets As Integer
Dim cSh As Worksheet
Dim cWB, sWB As Workbook
FileList = Application.GetOpenFilename(FileFilter:="Microsoft Excel Workbooks (.xls;.xlsx;.xlsm),.xls;.xlsx;.xlsm", Title:="Choose Excel files to merge", MultiSelect:=True)
If (vbBoolean <> VarType(FileList)) Then
If (UBound(FileList) > 0) Then
nFiles = 0
nSheets = 0
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Set cWB = ActiveWorkbook
For Each CurrentF In FileList
nFiles = nFiles + 1
Set sWB = Workbooks.Open(Filename:=CurrentF)
For Each cSh In sWB.Sheets
nSheets = nSheets + 1
cSh.Copy after:=cWB.Sheets(cWB.Sheets.Count)
Next
sWB.Close SaveChanges:=False
Next
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End If
End If
End Sub

VBA code by Selecting Files from File Explorer

  • After that, the system takes you to File Explorer where you put your Excel files.

  • Finally, the files have been added to your consolidated worksheet. It will take the number 2 as it is a duplicate of our previous files.

Read More: How to Consolidate Multiple Worksheets into One PivotTable (2 Methods)


Conclusion

That’s all about today’s session. And these are some easy methods to consolidate multiple Excel files into one. Please let us know in the comments section if you have any questions or suggestions. For a better understanding, please download the practice sheet. Visit our website, Exceldemy, a one-stop Excel solution provider, to find out about diverse kinds of Excel methods. Thanks for your patience in reading this article.


Related Articles

Fahim Shahriyar Dipto

Fahim Shahriyar Dipto

Hello! Welcome to my Excel blog! I am a big fan of MS Excel. I am learning new and exciting things in Excel and writing the process here. I think this will be helpful for you to get used to Excel. Keep visiting our website for new and updated Excel methods.

2 Comments
  1. Hello fahim!! You have described the methods such a nice way. Especially the two VBA coded help me to consolidate multiple files. But I want to know if there is any way to merge multiple files by using windows command prompt. It will be very helpful for me because I have tried it but couldn’t succeed. Can you help me in this thing?

    • Reply
      Fahim Shahriyar Dipto Nov 7, 2022 at 2:57 PM

      First of all, Thanks Mr. Smith for your valuable feedback. its means a lot to us. For your query, we have attached the steps with the image. Hopefully, you will understand this.
      To merge numerous Excel files into one, first, convert the XLSX files to CSV files. Navigate to the File Tab. Select the Save As option and then click the menu icon next to the Save Option. Then, in the list, you will find many file formats; explore them and select CSV UTF-8 (Comma delimited) from the list of other formats. Additionally, rename the file from Sales of January to 1-Sales of January (the serial numbers preceding the file name will arrange the files serially according to which serial we want to merge them). Finally, select the Save option.

      Then we’ll have a new file with the new format, 1-Sales of January.csv.

      To begin, Copy the path to the folder Multiple Files where we have saved our Excel files in CSV format to be combined.

      When you press the WINDOWS key + R, the Run window will appear. To launch the command prompt, type cmd in the Open box and hit OK.

      We’ve opened the CMD, or Command Prompt, as you can see.

      Enter cd and a space after it. To paste the copied path of our Multiple Files folder, press CTRL+V or right-click on your mouse.

      After clicking ENTER, you will be sent to the directory specified in the preceding line.
      Now, in the new line, put copy *.csv Consolidate.csv (where Merged Worksheet is our new filename), followed by

      After clicking ENTER, the following command lines will be created automatically, where we can see the two file names that we wish to combine.

      After you close the CMD window, navigate to the Multiple Files folder, where you will find the new merged file titled Consolidate.csv.

      When we view the Consolidate.csv file, we notice the Sales Record of January heading first, followed by the equivalent numbers from the February file.
      Hopefully, we think the solution is helpful for you

Leave a reply

ExcelDemy
Logo