How to Merge Excel Files With Different Columns?
When combining data from several Excel files, it is common to find that the column structure is not the same across all files. Some files may contain extra fields, renamed headings, or missing data. If you use the standard Get Data From Folder method in Power Query, Excel usually expands only the columns found in the first file. This can cause missing data without any warning.
Fortunately, you can make one small change to your Power Query steps so that every column from every file is always included, even when new files contain additional fields.
In this guide, we will explain the issue, show a simple example, and demonstrate the one-line adjustment that makes the merge process safe and complete.
Example Scenario
Imagine you have three Excel files containing quarterly sales data:
- Sales_Q1.xlsx
- Sales_Q2.xlsx
- Sales_Q3.xlsx
Each file contains similar information, but the number of product categories changes over the year.
| File Name | Columns Present |
|---|---|
| Sales_Q1.xlsx | Date, Product A, Product B |
| Sales_Q2.xlsx | Date, Product A, Product B, Product C |
| Sales_Q3.xlsx | Date, Product B, Product D |
If you directly combine these files in Power Query using the default steps, the final merged table will only show the columns from the first file (Product A and Product B). The Product C and Product D fields from later files will not appear at all.
This results in incomplete reporting and misleading totals.
Why the Columns Go Missing
During the Combine & Transform process, Power Query uses a sample file (the first file in the folder) to shape the output. The expand step then uses only the column headings detected in that sample file.
| Step | Behavior |
|---|---|
| Sample File Selection | Uses the first file in the folder |
| Column Expansion | Expands only the sample file’s columns |
| Effect | Extra columns in later files are ignored |
To prevent this, you need Power Query to scan all files, extract every unique column name, and expand using that complete list instead of just the sample.
Step-by-Step: Safe Merge Method Using Power Query
Step 1: Load Files Using “Get Data From Folder”
- Open Excel
- Go to Data
- Select Get Data → From File → From Folder
- Select the folder with the sales files
- Click Combine & Transform
This loads the folder contents into Power Query.
Step 2: Find the Step Before Columns Expand
Once the query loads:
- Look for the step just before Power Query expands the columns.
- Rename this step to PreExpand so it is easy to reference later.
Step 3: Create a List of Unique Column Names Across All Files
- Right-click the PreExpand step
- Select Drill Down
You will now see a list of tables (one table per file).
- Replace the formula with this one:
= List.Union(List.Transform(PreExpand, each Table.ColumnNames(_)))Press Enter.
This transforms each table into a list of column names and merges all the lists into one unified list.
Step 4: Restore the Original Table Structure
- Click the fx button to add a new step.
- Replace the formula with:
= PreExpandRename this step to ReadyToExpand.
Step 5: Apply the Column Expansion Safely
- Click the expand icon in the column header.
- Instead of selecting columns manually, Power Query will now expand all columns using the unified list from Step 3.
Your merged table now shows every column from every file, including those that appear only in later files.
Result After Safe Expansion
| Date | Product A | Product B | Product C | Product D |
|---|---|---|---|---|
| Data | Data | Data | null | null |
| Data | Data | Data | Data | null |
| Data | null | Data | null | Data |
null simply means that column did not exist in that file, which is correct.
Optional: Convert to a Tidy “Long Format”
If you need better charting, unpivot the product columns:
- Select Date
- Right-click → Unpivot Other Columns
This changes your table into:
| Date | Product | Value |
|---|---|---|
| 2023-01-05 | Product A | 150 |
| 2023-01-05 | Product B | 90 |
| 2023-02-10 | Product D | 120 |
This format works well for:
- PivotTables
- Power BI dashboards
- Trend analysis
Why This Method is Reliable
| Feature | Standard Merge | Safe Merge Method |
|---|---|---|
| Handles changing column sets | No | Yes |
| Keeps new fields in future data | No | Yes |
| Prevents incomplete reports | No | Yes |
| Supports automation | Limited | Strong |
Once this is set up, you can simply drop new files into the folder and refresh—every column will be included automatically.
Final Thoughts
When merging Excel files that do not share the exact same columns, the default Power Query behavior can cause missing data. By generating a unified list of column names and expanding based on that list, you ensure that all fields are preserved, even when files change over time.
This makes reporting more accurate, improves data transparency, and eliminates manual cleanup work.
This safe consolidation approach is especially useful for:
- Sales reports across quarters
- Inventory sheets from multiple warehouses
- Monthly departmental submissions
- Multi-export system data
With one small adjustment, your Excel merges become reliable, consistent, and scalable.
Frequently Asked Questions
Why does Power Query ignore some columns when merging files?
Power Query uses the first file in the folder as a sample to determine the structure. If later files contain additional columns, they are not expanded unless you update the query to include all column names from all files.
Can I merge Excel files that have different column names?
Yes. However, you may need to standardize column names before merging or use a Power Query method that collects and expands all unique column names from every file.
What happens if a file is missing some columns?
Those missing column values will display as null in the merged table. This is normal and indicates that the data did not exist in that file.
Do I need coding experience to use the safe merge method?
No. The method only requires adding a simple one-line formula in Power Query. Once set up, it will automatically handle future files.
Can this method work with CSV files instead of Excel files?
Yes. The safe merge approach works with any file type that Power Query can import, including CSV, TXT, XLSX, and XML files.
Can I refresh the merged data automatically when new files are added?
Yes. After you set up the query, simply place new files in the same folder and click Refresh in Excel to update the merged table.
Vaishvi Desai is the founder of Excelsamurai and a passionate Excel enthusiast with years of experience in data analysis and spreadsheet management. With a mission to help others harness the power of Excel, Vaishvi shares her expertise through concise, easy-to-follow tutorials on shortcuts, formulas, Pivot Tables, and VBA.