How to Combine Excel Files Using Power Query

This is how to combine Excel files using Power Query. Combining multiple files into a single dataset can be a time-consuming task, especially when dealing with large amounts of data. Fortunately, Excel's Power Query provides a powerful and efficient way to combine files into one cohesive dataset. Whether you're working with multiple Excel workbooks, CSV files, or even text files, Power Query can help you merge them with just a few clicks. In this blog post, we'll walk you through the process of using Excel Power Query to combine files seamlessly.

Every Method to Combine Excel files Explained with Step by Step Instructions

If you have multiple Excel files that need to become one, you are in the right place. Whether you are consolidating monthly reports, merging data from different departments, or combining exports from different systems, Excel gives you several ways to do it. The right method depends on how your data is structured, how often you need to do it, and whether you need the combined file to update automatically when the source files change.

This guide covers every method for combining Excel files, from the simplest copy and paste approach to Power Query automation, with clear step by step instructions for each one.

Method 1: Copy and Paste

The simplest way to combine Excel files is to copy data from one file and paste it into another. This works well when you are doing a one-time combination of a small number of files with identical column structures.

When to use this method: You have two to five files, the columns match exactly across all files, and you only need to do this once.

Step 1: Open all the Excel files you want to combine.

Step 2: Go to the first source file and click the row number of your first data row to select the entire row. Hold Shift and click the last row of data to select everything. Press Ctrl+C to copy.

Step 3: Switch to your destination file. Click the first empty cell in the first empty row below your existing data. Press Ctrl+V to paste.

Step 4: Repeat for each additional source file.

The limitation: This method is entirely manual. Every time your source files update, you have to repeat the process from scratch. For anything you need to do regularly, use one of the methods below.

Method 2: Move or Copy Sheets Into One Workbook

If your Excel files each contain separate sheets and you want to bring all those sheets into a single workbook, the Move or Copy function handles this cleanly without touching the data itself.

When to use this method: Each source file contains one or more sheets you want to preserve as separate sheets inside a single combined workbook.

Step 1: Open all the source Excel files and your destination workbook.

Step 2: In the first source file, right-click the sheet tab at the bottom of the screen. Select Move or Copy from the menu.

Step 3: In the Move or Copy dialog, click the dropdown under To book and select your destination workbook. Under Before sheet, choose where you want the sheet to be placed. Check the Create a copy box if you want to keep the original file intact. Click OK.

Step 4: Repeat for every sheet in every source file.

The result: All sheets from all files now exist inside a single workbook, each as its own tab. The data on each sheet is unchanged.

The limitation: Like copy and paste, this is a manual process. It does not create a live connection to the source files.

Method 3: Consolidate Using Excel's Built-In Consolidate Tool

Excel's Consolidate function is designed specifically for combining data from multiple ranges or files into one summary. It is particularly useful when you want to sum, average, or otherwise aggregate data from multiple files rather than simply stack it.

When to use this method: You want to combine data mathematically, such as summing sales figures from multiple regional files into a single total, rather than just appending rows together.

Step 1: Open your destination workbook and click the cell where you want the consolidated data to start.

Step 2: Go to the Data tab on the ribbon and click Consolidate.

Step 3: In the Consolidate dialog, choose your function from the dropdown. Sum is the default and most commonly used. Other options include Average, Count, Max, Min, and more.

Step 4: Click in the Reference field, then switch to your first source file and select the data range you want to include. Click Add. The reference appears in the All references list.

Step 5: Repeat for each source file, adding each reference in turn.

Step 6: If your data has labels in the top row or left column, check the appropriate boxes under Use labels in. If you want the consolidated data to update automatically when source files change, check Create links to source data.

Step 7: Click OK.

The result: Excel creates a consolidated summary in your destination sheet. If you checked Create links to source data, the consolidated sheet contains formulas linking back to the source files and will update when those files change.

The limitation: This method works best for numerical summary data. It is not designed for simply combining rows of data from multiple files into one continuous dataset.

Method 4: Combine Excel Files With Power Query- The Best Method for Regular Use

Power Query is the most powerful and most flexible way to combine Excel files, and it is the method you should learn if you do this regularly. It creates a live connection to your source files, automatically stacks the data from all files into one table, and refreshes with a single click whenever your source files update.

Power Query is available in Excel 2016 and all later versions including Microsoft 365. If you are on Excel 2010 or 2013, you can download it as a free add-in from Microsoft.

Combining Multiple Files from a Folder Using Power Query

This approach is ideal when you have multiple files in the same folder that all have the same structure, such as monthly sales reports where each month is a separate file.

Step 1: Put all the Excel files you want to combine into a single folder. Make sure the folder contains only the files you want to combine and nothing else.

Step 2: Open a new Excel workbook. Go to the Data tab and click Get Data. Choose From File and then From Folder.

Step 3: Browse to the folder containing your files and click Open. Power Query will scan the folder and show you a list of all the files it found.

Step 4: Click the Combine dropdown button and select Combine and Transform Data. Excel will ask you to select which sheet or table within the files to import. If all your files have a sheet named Sheet1 or a table named Table1, select that. Click OK.

Step 5: Power Query opens the query editor showing a preview of your combined data. Review the columns to make sure everything looks correct. Remove any columns you do not need by right-clicking the column header and selecting Remove.

Step 6: Click Close and Load to import the combined data into your workbook as a table.

Step 7: Any time you add a new file to the folder or update an existing file, go to the Data tab and click Refresh All. Power Query will automatically incorporate the new or updated data.

The result: A single table containing all the data from all the files in your folder, updated automatically whenever you refresh.

Combining Specific Files Using Power Query

If your files are not all in the same folder or you only want to combine specific files rather than everything in a folder, use this approach instead.

Step 1: Open a new Excel workbook. Go to Data, Get Data, From File, From Workbook. Select your first source file and click Import.

Step 2: The Navigator panel shows all sheets and tables in that file. Select the sheet or table you want and click Transform Data to open it in Power Query editor.

Step 3: In Power Query editor, go to Home and click New Source, then File, then Workbook again. Select your second source file and add the sheet or table you want.

Step 4: With both queries visible in the left panel of Power Query editor, select the first query. Go to Home and click Append Queries. Choose Two tables if combining two files or Three or more tables if combining more. Select your queries and click OK.

Step 5: Power Query creates a new combined query that stacks all the data together. Click Close and Load to bring it into your workbook.

Method 5: Combine Excel Files Using a Macro

For users comfortable with Excel's built-in programming environment, a VBA macro can automate the process of combining multiple files into one. This is useful when you need to combine files on a schedule or when the Power Query approach does not fit your specific data structure.

When to use this method: You need full control over exactly how the data is combined, you are comfortable with VBA, or you need to automate the process beyond what Power Query's refresh button provides.

Step 1: Open the workbook where you want to combine the data. Press Alt+F11 to open the Visual Basic editor.

Step 2: Go to Insert and select Module.

Step 3: Paste the following macro into the module:

Sub CombineExcelFiles()
    Dim FolderPath As String
    Dim FileName As String
    Dim SourceWB As Workbook
    Dim DestWS As Worksheet
    Dim LastRow As Long
    
    FolderPath = "C:\YourFolderPath\"
    Set DestWS = ThisWorkbook.Sheets(1)
    LastRow = 1
    
    FileName = Dir(FolderPath & "*.xlsx")
    
    Do While FileName <> ""
        Set SourceWB = Workbooks.Open(FolderPath & FileName)
        SourceWB.Sheets(1).UsedRange.Copy DestWS.Cells(LastRow, 1)
        LastRow = DestWS.Cells(DestWS.Rows.Count, 1).End(xlUp).Row + 1
        SourceWB.Close False
        FileName = Dir()
    Loop
    
    MsgBox "Files combined successfully."
End Sub

Step 4: Change C:\YourFolderPath\ to the actual path of the folder containing your files.

Step 5: Close the VBA editor. Back in Excel, go to the Developer tab and click Macros. Select CombineExcelFiles and click Run.

The result: The macro opens every .xlsx file in your specified folder, copies the used range from the first sheet of each file, and pastes it into your destination workbook one after another.

Method 6: Combine Files Using VSTACK Formula

If you are using Microsoft 365 or Excel 2021 and later, the VSTACK function offers a formula-based way to combine data from multiple sheets or ranges vertically into a single array without needing Power Query or a macro.

When to use this method: You have data in multiple sheets within the same workbook that you want to combine into one view, and you are on a recent version of Excel that supports dynamic array functions.

The formula:

=VSTACK(Sheet1!A:D, Sheet2!A:D, Sheet3!A:D)

This formula stacks the data from columns A through D on Sheet1, Sheet2, and Sheet3 into a single continuous range starting from the cell where you enter the formula.

The advantage: The result updates automatically when any of the source ranges change. No refresh button needed.

The limitation: VSTACK works across sheets within the same workbook but not across separate workbook files. For combining separate files, use Power Query.

Which Method Should You Use?

One time combination of a few files: Copy and paste or Move or Copy sheets.

Combining data to summarize it mathematically: Consolidate tool.

Regular combination of files that update over time: Power Query from folder. This is the method most people should learn because it scales, automates, and handles changes without manual work.

Combining sheets within the same workbook on a recent Excel version: VSTACK formula.

Full automation with custom logic: VBA macro.

Common Problems When Combining Excel Files and How to Fix Them

Duplicate header rows appearing in the combined data. This happens when each source file includes a header row and all of them get stacked together. In Power Query, use the Use First Row as Headers option under the Transform tab. In copy and paste, copy only the data rows from the second file onward, not the header.

Columns in different orders across files. Power Query handles this automatically if you use Combine from Folder and your files have consistent column names. The query matches columns by name rather than position. For copy and paste, you need to manually reorder columns in each source file before combining.

Data types not matching. If one file stores a date as a number and another stores it as a formatted date, the combined data will look inconsistent. In Power Query, select the column, go to Transform, and choose Data Type to standardize. In a manual combination, format all cells in the relevant column consistently before or after combining.

File path errors in Power Query. If you move your source files to a different folder, Power Query will not be able to find them and will show an error on refresh. Fix this by going to Data, Get Data, Data Source Settings and updating the file path.

Large files making the workbook slow. When combining many large files, the combined workbook can become slow to open and refresh. Consider using Power Query to load only the columns you actually need rather than importing everything, and turn off automatic refresh so the query only runs when you choose.

Frequently Asked Questions

How do I combine multiple Excel files into one?

The best method depends on how often you need to do it. For a one-time combination, copy the data from each source file and paste it into a single destination file. For regular combinations that need to stay updated automatically, use Power Query. Go to Data, Get Data, From File, From Folder, select the folder containing your files, and choose Combine and Transform Data. Power Query stacks all the data into one table and refreshes with a single click when your files update.

How do I combine Excel files without opening them all manually?

Use Power Query's From Folder option. Go to Data, Get Data, From File, From Folder and point Power Query to the folder containing your files. Power Query reads and combines all the files in that folder automatically without you needing to open any of them individually. Refresh the query any time to incorporate new or updated files.

How do I merge two Excel files with the same columns?

If both files have the same column structure and you want to stack the rows together, copy the data from the second file and paste it below the data in the first file. For an automated approach, use Power Query's Append Queries function which combines two or more tables with matching columns into one continuous dataset.

How do I combine Excel files and remove duplicates?

First combine the files using any method above. Then select your combined data, go to the Data tab, and click Remove Duplicates. Choose which columns to use for identifying duplicates and click OK. In Power Query, you can also remove duplicates directly in the editor by right-clicking a column header and selecting Remove Duplicates.

Why is Power Query the best way to combine Excel files?

Power Query creates a live connection to your source files and updates automatically when you click Refresh. Unlike copy and paste or manual consolidation, you do not have to redo the work every time your source files change. It also handles mismatched column orders by matching on column names, processes files from an entire folder at once, and gives you tools to clean and transform the data before it lands in your workbook.

Can you combine Excel files with different column structures?

Yes, but it requires more work. Power Query handles this best. When you combine files with different columns, Power Query creates a column for every unique column name across all files and fills missing values with null where a file does not have that column. You can then remove unwanted columns and fill or handle the nulls in the query editor before loading the data.

How do I combine Excel sheets within the same workbook into one sheet?

Use the VSTACK formula if you are on Microsoft 365 or Excel 2021. Enter =VSTACK(Sheet1!A:D, Sheet2!A:D, Sheet3!A:D) replacing the ranges with your actual sheet names and column ranges. For older Excel versions, use a VBA macro or manually copy the data from each sheet and paste it into a consolidated sheet.

How many Excel files can Power Query combine at once?

Power Query can combine any number of files from a folder in a single query. There is no hard limit on the number of files. The practical limit is your computer's memory and processing power. Combining hundreds of large files may be slow but it will work. For very large combinations, consider importing only the columns you need to reduce the data volume.