What is the fastest way to combine multiple files in an Excel folder at once?

On the Data tab, use Get Data > From File > From Folder to connect the folder with Power Query, then select Combine & Transform to merge multiple files at once. Afterward, add new files to the folder and select Refresh All to update automatically.

Can I fix a Power Query merge when files have different numbers of columns?

Yes. Define the list of columns you ultimately need, then use Table.SelectColumns(…, MissingField.UseNull) in Power Query (M). Columns that do not exist in a given file are filled with Null, creating a stable schema.

Can files be combined automatically whenever I add a new file?

After creating a folder-combining query, you can automate it by enabling Refresh data when opening the file or setting Refresh every X minutes in Query Properties. By default, you can also add a file to the folder and select Refresh All to update immediately.

Korean characters are garbled when I combine multiple CSV files. Can I fix that too?

Yes. In the Power Query Text/CSV preview, select UTF-8 or Korean (949) as the File Origin (encoding) and verify that the file is read correctly. When possible, standardizing files as UTF-8 makes the combining pipeline more reliable.

Can I combine multiple files without Power Query?

If you have only a few files with identical structures, you can combine tables with formulas such as VSTACK. If macros are your organization's standard, you can use VBA to loop through and append files in a folder. For high-volume or batch automation, Python (pandas) is commonly used to read folder files, concatenate them, and save the output.

CONTENTS

Combine Multiple Excel Files: The Most Reliable Way to Automatically Merge Files from a Folder

Monthly sales files, branch inventory files, team performance files… If you repeatedly copy and paste them into one worksheet, columns eventually become misaligned, headers are duplicated, numbers turn into text, and you have to start over whenever a new file is added. This guide covers the standard Power Query workflow for combining multiple Excel files: set it up once, add files to a folder afterward, and refresh once. It also addresses real-world issues such as mismatched columns (schemas), Korean CSV encoding, and performance.

Quick Fix: Combine Multiple Excel Files in 3 Minutes

1) Folder Preparation Rules (Most Important)

Put only the source files you want to combine in one folder (temporary files, backups, and mixed file types increase the chance of errors).
When possible, standardize column names (headers) and the number of columns (schema).
Convert source ranges to tables (Ctrl+T) before saving them (tables handle structural changes better than worksheet ranges).
Use a consistent file-naming convention (for example, Sales_2025-01.xlsx).

2) Connect to the Folder with Power Query (From Folder)

English UI: Data → Get Data → From File → From Folder
Korean UI: Data → Get Data → From File → From Folder

3) Combine & Transform, Then Load

On the file list screen, select Combine → Combine & Transform Data.
In the Power Query Editor, select Close & Load to load the table.

4) Add Files and Refresh

Add a new file to the folder, then select Data → Refresh All to include it.

Why Is Power Query Ideal for Combining Multiple Files?

Copy and Paste vs. Power Query vs. Formulas (VSTACK)

Copy and paste: Fast at first, but creates major issues with repetition, human error, broken columns, and maintenance.
Formulas (such as VSTACK): Clean when there are only a few files and their structures are identical, but limited for automatically collecting files from a folder.
Power Query: Best for ongoing use with automatic folder-based combining, saved cleanup rules, and refresh-based updates.

For combining tables or ranges: Excel VSTACK, HSTACK, TOCOL, TOROW, TAKE, and DROP: Combine and Reshape Tables

How the “Transform Sample File” Is Created Automatically

When you combine a folder, Power Query creates transformation rules from a representative file (sample), saves those rules as a function, applies the function to every file in the folder, and then combines the results into one table.

Practical Example: Combine 12 Monthly Sales Files into One Table

Sample Data (Reproducible)

Date	Store	SKU	Qty	Amount
2025-01-03	Seoul	A001	2	40000
2025-01-05	Busan	B120	1	15000

Step-by-Step Click Path (Windows/Mac)

Open a new workbook.
Data → Get Data → From File → From Folder
Select the folder and review the file list.
Select Combine → Combine & Transform Data.
Check column names and data types.
Select Close & Load.

Add a File Name (Source) Column

Keep the Name (file name) column from the file list step through to the final output in the Power Query Editor to easily trace the source of each row.

How to Combine Files When Columns (Schemas) Differ

When Files Have Extra or Missing Columns

Columns may disappear because the schema is fixed based on the sample file. It is safer to define the final column list and enforce it.

let
    Source = Excel.CurrentWorkbook(){[Name="Combined"]}[Content],
    KeepCols = {"Date","Store","SKU","Qty","Amount","Channel"},
    Fixed = Table.SelectColumns(Source, KeepCols, MissingField.UseNull)
in
    Fixed

When Header Rows Are in Different Locations

In the sample-file transformation steps, apply Remove Top Rows and then Use First Row as Headers so the same steps are applied to every file.

Power Query fundamentals: Excel Power Query Basics Guide

Combine Multiple CSV Files and Fix Korean Character Encoding

CSV files break more often because of encoding, delimiters, and automatic type detection. In the preview, change the File Origin (encoding) to UTF-8 or Korean (949) and verify the results. When possible, standardize files as UTF-8.

Three Alternatives for Combining Files Without Power Query

(1) VSTACK/HSTACK: When You Have Only a Few Tables

=VSTACK(TAKE(TblJan,1), DROP(TblJan,1), DROP(TblFeb,1), DROP(TblMar,1))

(2) VBA Macro: When It Is Your Company’s Standard

Sub MergeFilesInFolder()
    Dim folderPath As String, fileName As String
    Dim wb As Workbook, ws As Worksheet
    Dim master As Worksheet, nextRow As Long
    
    folderPath = "C:DataSales"
    Set master = ThisWorkbook.Worksheets("Master")
    nextRow = master.Cells(master.Rows.Count, 1).End(xlUp).Row + 1
    
    fileName = Dir(folderPath & "*.xlsx")
    Do While fileName <> ""
        Set wb = Workbooks.Open(folderPath & fileName)
        Set ws = wb.Worksheets(1)
        
        ws.UsedRange.Copy master.Cells(nextRow, 1)
        nextRow = master.Cells(master.Rows.Count, 1).End(xlUp).Row + 1
        
        wb.Close SaveChanges:=False
        fileName = Dir
    Loop
End Sub

(3) Python (pandas): For Large Volumes and Batch Automation

import glob
import pandas as pd

files = glob.glob(r"C:DataSales*.xlsx")
dfs = [pd.read_excel(f) for f in files]
out = pd.concat(dfs, ignore_index=True)
out.to_excel(r"C:DataSalesmerged.xlsx", index=False)

Automatic Refresh and Performance Optimization Checklist

In Query Properties, set Refresh data when opening the file or Refresh every X minutes.
Remove unnecessary columns early to sharply reduce processing volume.
Handle data type changes in a single step.
Remove unnecessary files from the folder.

Troubleshooting Table (Symptom | Cause | Solution)

Symptom	Cause	Solution
Some columns disappear after combining	Columns are fixed based on the sample file schema	Use SelectColumns + MissingField.UseNull to force columns to remain
Headers are imported as data	Header positions differ between files	Remove top rows, then use the first row as headers in the sample file
Korean characters are garbled (CSV)	Encoding mismatch	Select UTF-8/Korean (949) during import; standardize as UTF-8 when possible
Too slow	Too many unnecessary columns or steps, or extra files in the folder	Remove columns early, simplify steps, and clean up the folder

If duplicates are a concern after combining: The Easiest Way to Remove Duplicate Values in Excel

Conclusion

Once you set up this folder-combining process correctly, next month you only need to put files in the folder and refresh. For further automation and performance improvements, see the related articles above.

사람들이 “진짜 공중부양”에 성공했다는 이야기, 어디까지가 사실일까? (검증된 사례 vs 착시/사기 구분법 + 엑셀 계산 예제)

Combine Multiple Excel Files: Automatically Merge Files from a Folder with Power Query