How to combine Excel files with different columns
By the Analistable team · Updated · 3 min read
Combine files with different columns by matching on header names, not positions. Power Query's Append does this, filling missing columns with null. Watch out: From Folder → Combine only expands the columns of the first (sample) file, so columns that exist only in later files are silently dropped. Fix it by replacing the final expand step with Table.Combine.
Part of our guide: How to merge Excel files: every method compared
Three kinds of “different columns”
| Difference | Example | Fix |
|---|---|---|
| Same headers, different order | Date, Amount vs Amount, Date | Match by name (Power Query, tools) |
| Extra or missing columns | One file adds “Discount” | Match by name; missing values become blank |
| Same data, different header names | “Order ID” vs “OrderID” | Rename to one standard first |
If the files hold different information about the same records — customers in one, orders in another — you need a join, not a stack. See joining spreadsheets.
Why copy-paste and VSTACK fail here
Copy-paste and VSTACK place columns by position. If file 2 has Amount where file 1 has Date, the merged column mixes dates and amounts with no error to warn you. Use one of the name-matching methods below.
Power Query: the trap in From Folder
When you use Data → Get Data → From Folder → Combine, Power Query builds the expand step from the sample file's columns:
= Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File", Table.ColumnNames(#"Transform File"(#"Sample File")))Table.ColumnNames(...Sample File) is the column list of the first file only. Replace the whole step with a combine that unions every file's columns:
= Table.Combine(#"Removed Other Columns1"[Transform File])Step names vary between files; use whatever your previous step is called. You lose the Source.Name column with this change, so add it inside the Transform File function if you need it, or keep it by adding a custom column before combining.
Align header names before combining
Different spellings of the same column must be renamed first. In Power Query, add a step in Transform Sample File that cleans every header:
= Table.TransformColumnNames(#"Promoted Headers", each Text.Lower(Text.Trim(Text.Replace(_, " ", ""))))That turns “Order ID”, “order id” and “OrderID ” into orderid. For names that differ more (“Qty” vs “Quantity”), use Table.RenameColumns with a list of pairs, and add MissingField.Ignore so files without that column don't fail:
= Table.RenameColumns(#"Lowered Headers", {{"qty", "quantity"}, {"cust", "customer"}}, MissingField.Ignore)Worked example
| Source | Order ID | Amount | Discount | Channel |
|---|---|---|---|---|
| jan.xlsx | 1001 | 120 | Web | |
| jan.xlsx | 1002 | 80 | Store | |
| feb.xlsx | 1003 | 95 | 5 | |
| feb.xlsx | 1004 | 140 | 0 |
January had no Discount column and February had no Channel column. Both survive, with blanks where a file didn't have the field.
Without M code
The Analistable combiner matches columns by header name and keeps every column from every file, adding a source column. It runs in your browser, so the files stay on your computer.
Frequently asked questions
- Can Power Query combine files with different columns?
- Yes. Append and Table.Combine match columns by name and fill missing ones with null. The From Folder Combine wizard only keeps the first file's columns unless you change the expand step.
- How do I combine Excel sheets with columns in a different order?
- Use a method that matches by header name, such as Power Query Append, or reorder the columns before copying.
- How do I merge two Excel sheets by a column instead of stacking them?
- That is a join: use XLOOKUP or Power Query Merge Queries on the shared column.