How to combine Excel files with different columns
By the Analistable team · Updated · 5 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.
Append Queries without touching M code
If you have only a handful of files, Append in the Excel interface avoids the From Folder trap entirely, because it unions every table's columns by name:
- Load each file with Data → Get Data → From File → From Workbook, choose the sheet and click Transform Data.
- In each query, promote headers and fix column names so the same field has the same name everywhere.
- Choose Home → Append Queries as New, pick Three or more tables and add every query.
- Check the result: any column that exists in only some files is filled with null for the others.
- Close & Load the appended query. Set the individual file queries to Connection Only so they don't each load to a sheet.
The downside is that a new file means adding a new query and editing the Append step. With more than a few files, the From Folder approach with Table.Combine scales better.
Keeping the file name with Table.Combine
To keep a column showing where each row came from, add it to every file's table before combining. Replace the final step with:
= Table.Combine(
Table.AddColumn(#"Removed Other Columns1", "Tagged",
each Table.AddColumn([Transform File], "Source", (row) => [Source.Name]))[Tagged]
)Each file's table gets a Source column holding its file name, and Table.Combine still unions all the columns by name.
In Python with pandas
pd.concat also matches columns by name and fills gaps with NaN, which makes it a reliable choice for many files or files too large for a worksheet:
from pathlib import Path
import pandas as pd
frames = []
for path in sorted(Path("exports").glob("*.xlsx")):
df = pd.read_excel(path, dtype={"Order ID": str})
df.columns = df.columns.str.strip() # "Amount " -> "Amount"
df.insert(0, "Source", path.name)
frames.append(df)
combined = pd.concat(frames, ignore_index=True)
combined.to_excel("combined.xlsx", index=False)Run on the two example files (sorted, so feb.xlsx comes first), the combined frame has the columns Source, Order ID, Discount, Amount and Channel, with Discount empty for January's rows and Channel empty for February's. Column order follows the first file read. Reading .xlsx files needs the openpyxl package. More in merging DataFrames in pandas.
Troubleshooting
| Symptom | Cause | Fix |
|---|---|---|
| Two Amount columns side by side | One header has a trailing space or different case | Trim and standardise headers before combining |
| A column from later files is missing | The From Folder expand step lists only the sample file's columns | Replace it with Table.Combine |
| Columns named Column1, Column2… | Headers weren't promoted in some files | Promote headers in Transform Sample File |
| Numbers become errors after the type step | One file stores the column as text with symbols (“£120”) | Replace the symbol before changing the type |
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.
- Why do I get two columns for the same field after combining?
- The headers differ slightly, for example a trailing space or different capitalisation. Trim and standardise header names in Power Query before the files are combined.