How to Combine Excel Files Automatically Using Power Query

Introduction

Many businesses store important data across multiple Excel files. This might include weekly reports, sales exports, financial records, or operational spreadsheets.

The problem is that combining these files into a single report often becomes a manual and time-consuming process. Teams frequently copy and paste data from multiple workbooks, increasing the risk of errors and inconsistent reporting.

The good news is that Excel provides several ways to automatically combine multiple files into one dataset, saving significant time and effort.

In this guide, we’ll show you how businesses can automate this process using built-in Excel tools.

Why Combining Excel Files Manually Causes Problems

Many organisations still consolidate spreadsheets manually.

A typical workflow might look like this:

• Open several Excel files

• Copy data from each file

• Paste the data into a master spreadsheet

• Adjust formatting and formulas

• Repeat this process every reporting cycle

This approach creates several challenges.

Time consuming

Manual consolidation can take hours, especially when dealing with dozens of files.

High risk of errors

Copying and pasting data often introduces mistakes such as missing rows or incorrect formatting.

Difficult to scale

As businesses grow and more files are generated, the process becomes increasingly inefficient.

Method 1: How to Combine Excel Files Automatically Using Power Query

One of the most effective ways to combine Excel files automatically is by using Power Query.

Power Query allows Excel to import and transform data from multiple files stored in a folder.

Once the setup is complete, new files can simply be added to the folder and Excel will automatically include them in the combined dataset.

How it works

1. Place all Excel files into a single folder

2. Open Excel and go to Data → Get Data → From Folder

3. Select the folder containing your files

4. Choose Combine and Transform Data

5. Power Query automatically merges the files into a single table

This approach is ideal for monthly reports, sales exports, or operational data files.

Once configured, refreshing the query will automatically update the dataset with the latest files.

Method 2: Combine Files Using VBA Automation

Another approach is to use VBA macros to automate the consolidation process.

A macro can open multiple files, copy the required data, and append it to a master worksheet.

For example:

Sub CombineFiles()
Dim wb As Workbook
Dim ws As Worksheet
Dim FolderPath As String
Dim FileName As String
FolderPath = "C:\Reports\"FileName = Dir(FolderPath & "*.xlsx")
Do While FileName <> ""Set wb = Workbooks.Open(FolderPath & FileName)
wb.Sheets(1).Range("A1:D100").Copy _ThisWorkbook.Sheets("Master").Cells(Rows.Count,1).End(xlUp).Offset(1,0)
wb.Close 
FalseFileName = DirLoopEnd 
Sub

This macro automatically loops through files in a folder and copies the data into a single worksheet.

VBA automation is particularly useful when businesses require custom consolidation logic or formatting rules.

When Businesses Use This Type of Automation

Combining Excel files automatically is particularly useful in situations such as:

• Consolidating weekly sales reports

• Merging departmental spreadsheets

• Combining financial data from multiple sources

• Aggregating exported system reports

• Collecting data from different teams

Instead of manually merging spreadsheets, automation ensures the data is updated quickly and consistently.

Benefits of Automating Excel File Consolidation

Businesses that automate this process often see immediate improvements.

Significant time savings

Reports that once required hours of manual work can be generated in minutes.

Reduced errors

Automation eliminates manual copying and pasting mistakes.

Consistent data structure

Every file is processed using the same logic and formatting rules.

Scalable reporting workflows

As the number of files increases, the automation continues to work efficiently.

Final Thoughts

Excel is often used to store data across many separate files, but manually combining these files can quickly become inefficient.

By using tools like Power Query or VBA macros, businesses can automate the consolidation process and dramatically reduce the time required to prepare reports.

With the right setup, new data can be added automatically, allowing teams to focus on analysis rather than manual spreadsheet work.

Need Help Automating Your Excel Processes?

If your business regularly consolidates data from multiple spreadsheets, automation can simplify the entire process.

We help organisations build automated Excel reporting systems, dashboards, and data workflows tailored to their business needs.

If you're looking to streamline reporting, see our guide on how to automate Excel reports in 10 minutes.

👉 Contact us to Request a free spreadsheet automation review today and see where your Excel processes can be improved.