Best ways to combine multiple Excel files into one

Most advice assumes every file has the same columns in the same order. Files that come from different people rarely do. Different tools are best for different consolidation problems, and this guide helps you pick the right one for yours.

What “consolidate Excel files” usually means

People use consolidate, merge and combine for three different jobs:

• Stacking rows. Every row from every file ends up in one long table, such as twelve monthly order exports becoming one year of orders. This guide is about this job.
• Summarising. You want totals or averages across files, not the individual rows. Excel’s built-in Consolidate command is designed for this.
• Joining. Two different tables share a key, such as orders and a customer list, and you want to look values up from one into the other. That is a lookup or merge-query job.

If you are not sure which one you need, What is spreadsheet consolidation? explains the difference with examples.

Which method fits your files?

Start from the files you actually receive, not from the tool you already know.

The main ways to combine Excel files, and where each one breaks

How the options compare

Combining Excel files with Python

If you work in code, pandas lines files up by column name and keeps every column:

import pandas as pd
from pathlib import Path

frames = []
for path in sorted(Path("reports").glob("*.xlsx")):
    df = pd.read_excel(path)
    df.columns = [str(c).strip() for c in df.columns]
    df["Source File"] = path.name
    frames.append(df)

combined = pd.concat(frames, ignore_index=True, sort=False)
combined.to_excel("combined.xlsx", index=False)

pd.concat matches columns by exact name, so “Order ID” and “order_id” still become two columns until you rename them, and read_excel assumes the headings are on the first row. Both are solvable in code; the question is whether someone wants to own that code.

When a simple merge is enough

Plain copy and paste, or a basic Power Query folder import, is fine when all of these are true:

• every file uses exactly the same headings, spelled the same way;
• the columns are in the same order;
• the headings are in the same row and there are no title rows, notes or totals around the table;
• dates and numbers are stored the same way in every file.

If you control the template and the files really are identical, you probably don’t need anything more.

What goes wrong when the columns don’t match

Copying by position assumes column B means the same thing in every file. Here is what happens when feb.xlsx from the example above is pasted underneath jan.xlsx:

The same thing happens in quieter ways:

• A missing column shifts everything to its right one place to the left.
• An extra column is either lost or pushes other values out of line.
• A heading spelled differently, such as “Order ID” and “order_id”, becomes two half-empty columns in tools that match names exactly.
• Title rows, blank rows and totals end up in the data, and a totals row counts everything twice.

None of these produce an error message. The result looks finished, which is why they are easy to miss.

How to consolidate Excel files with different columns

The step that takes judgement is the fourth. A column that only one file has can usually be kept and left blank elsewhere. Two headings that sound alike, such as “Gross Sales” and “Sales”, need someone who knows the data to say whether they really are the same. Combine Excel files with different columns goes deeper into those decisions.

Doing it in ConsoliSheet

Jobs with up to five business columns are free, with no account. Source File doesn’t count towards the five. Larger jobs can be checked and previewed free; the complete file is $11.99 one-time, or included with Pro at $19.99 a month. See pricing for details.

When Power Query is the better choice

If you combine the same set of identically structured files every month and you are comfortable building a query, Power Query is hard to beat: it is already in Excel for Windows, it refreshes with one click and it can reshape the data as it goes. Power Query vs ConsoliSheet compares the two side by side if you are deciding between them.

Checks before you rely on the result

• Row count. The rows in the output should equal the data rows in all the source files added together.
• Blanks in key columns. A blank Order ID or Date often means a column was not matched.
• Duplicates. The same record sent in two files is a business question, not something to delete automatically. ConsoliSheet keeps duplicates and warns you about them.
• Dates. 01/02/2026 is 1 February in the UK and 2 January in the US. Check files from different sources agree.
• Totals rows. Make sure no subtotal or grand total rows have been counted as data.

Common questions