How to combine Excel files with different column names

The difficult part is rarely opening the files. It is deciding which columns actually mean the same thing before you combine a single row.

A worked example: two files, the same data, different headings

Two regional teams send this month’s sales. Both started from the same template, and both describe the same kind of record: who bought what, how many, and for how much.

Pasting south.xlsx under north.xlsx appears to work, because the columns happen to be in the same order. That is luck rather than a method. The moment west.csv arrives with revenue, customer, sku, quantity, Region, position-based copying puts revenue figures under Customer and customer names under SKU, and nothing warns you.

There are really two separate questions here. Which columns are the same column written differently, and which are different columns that happen to look alike? The first is mechanical. The second needs someone who knows the data.

The kinds of column difference, and which are safe to resolve automatically

Why files from different people stop matching

Shared templates drift. One person renames a heading to match the language their team uses, another inserts a column for their own notes, a third exports from a system that names things its own way. Each change is reasonable locally. The problem only appears when the files are combined. This gradual divergence is often called spreadsheet schema drift, and it is the main reason a process that worked in January breaks by April.

Similar names are not proof of identical meaning

Capitalisation and spacing differences are usually safe to normalise automatically. Business terms are a different matter: “Gross Sales” and “Net Sales” look related but describe different figures, and treating them as interchangeable will quietly corrupt a report.

• Revenue and Sales. In one team “Sales” means invoiced value including VAT; in another it is net of returns. Merged into one column, the total is meaningless, yet it looks perfectly plausible.
• SKU and Product Code. If one supplier uses your internal codes and another uses its own catalogue numbers, a merged column joins to the wrong products later.
• Wholesale Price, Trade Price and Cost. These can be three different numbers for the same item. Similar wording is exactly where automatic matching is most tempting and most dangerous.

A good test: look at five sample values from each column side by side. If you cannot say with confidence that they measure the same thing in the same unit, keep the columns separate until someone who owns the data decides.

Method 1: by hand in Excel

For a handful of files, this is entirely reasonable.

It works, but it is slow, and the copying step is where values land in the wrong column. If you do this every month, use one of the methods below.

Method 2: Power Query

Power Query’s From Folder import is excellent when every file shares one structure. When headings differ, the reliable pattern is: rename each file’s columns to your standard names first, then combine. Table.Combine lines tables up by column name, keeps every column, and fills missing ones with null.

let
    Files    = Folder.Files("C:\Reports\Sales"),
    Xlsx     = Table.SelectRows(Files, each [Extension] = ".xlsx"),
    Renames  = {{"Client", "Customer"}, {"Product Code", "SKU"},
                {"Qty", "Quantity"}, {"Sales", "Revenue"}},
    Prepared = Table.AddColumn(Xlsx, "Data", (f) =>
        let
            Sheet   = Excel.Workbook(f[Content], true){0}[Data],
            Renamed = Table.RenameColumns(Sheet, Renames, MissingField.Ignore)
        in
            Table.AddColumn(Renamed, "Source File", each f[Name])),
    Combined = Table.Combine(Prepared[Data])
in
    Combined

• MissingField.Ignore lets one rename list cover every file, even files that don’t contain some of the old names.
• The rename list is your mapping decision, written down. Add to it when a new variation appears.
• A file containing both Client and Customer makes the rename fail, because the column already exists. Decide which one wins first.
• {0} takes the first sheet or table in each workbook, and the headings must be on its first row. If files have title rows above the headings, remove them before promoting headers.

If you would rather not maintain the query, or the files change shape every month, a guided tool can do the same job. Power Query Combine Files problems covers the common errors in detail.

Method 3: ConsoliSheet

ConsoliSheet is built for exactly this situation: spreadsheets from different people or systems that describe the same data in slightly different shapes.

Columns that only some files have are kept, with blanks for the rest. ConsoliSheet can suggest Client → Customer, Product Code → SKU and Qty → Quantity, but the default is to keep them separate until you accept. Other pairs, such as Sales → Revenue and Territory → Region, are not suggested; you may combine them manually after checking their meaning. A refused or untouched suggestion leaves both columns separate. Review the proposed columns and preview the result before downloading.

A tested example: five monthly reports that don’t quite match

To show exactly what happens, we built five small monthly sales reports with the problems described above, ran them through ConsoliSheet and published both the files and the result. Every customer and product code is invented.

Select any screenshot to open it at full size.

The problem: five months, five slightly different files

What ConsoliSheet resolves automatically

• It finds each table: April’s data starts below its title rows, and May’s notes block is not treated as data.
• It lines up February’s headings with January’s, despite the different capitals, spacing and column order.
• It keeps Sales Rep and Notes, which only April has, and leaves them blank for the other months. Region is blank for May.
• It leaves out May’s total row, because it is a SUM of the rows above, and tells you it has done so.
• It reads March’s semicolon-separated CSV alongside the Excel files.

Before you make any decision, the five files produce twelve columns, because March’s five differently worded headings are kept apart:

Three possible matches, suggested but not applied

ConsoliSheet recognises a small, deliberately cautious set of wording differences. Here it suggests three, all from March: Client → Customer, Product Code → SKU and Qty → Quantity. Each suggestion starts on Keep separate, so doing nothing leaves the columns apart. Many synonyms are not recognised at all; those are left for you to decide.

We checked a few values in each pair and chose Use for all three. That leaves nine columns.

Two decisions only a person can make

Sales → Revenue and Territory → Region are not suggested. In many businesses Sales and Revenue are different figures, for example gross and net, and a territory is not always the same thing as a region. In these files they do mean the same thing, so we combined them ourselves with Combine two columns yourself. Each manual combination is listed with its file and can be undone.

ConsoliSheet refuses a combination that would put two values in one place, for example when the same file already has both columns. It keeps both columns and explains why.

The result: seven business columns plus Source File

The completed workbook has 30 rows: six from each file, in file order and in each file’s original row order. Source File on every row names the spreadsheet it came from.

• Left out: May’s SUM total row, May’s notes block and April’s title rows. Nothing else.
• Kept: every data row and every filled value from the five files. This sample contains no exactly repeated rows, so it does not test duplicates; ConsoliSheet’s handling of those (exact duplicate rows are kept, not removed) was confirmed in separate production acceptance testing.
• Blank on purpose: Region for May, and Sales Rep and Notes for every month except April.
• Worth knowing: March’s numbers arrive as text, for example 440.0. ConsoliSheet does not yet convert numbers in CSV files, so convert that column in Excel before you calculate with it. A total typed in as a plain number, rather than a formula, would be kept as an ordinary row.
• Not remembered: these decisions apply to this consolidation only. ConsoliSheet does not save them for next month’s files.

The completed workbook was produced by ConsoliSheet’s processor, the version that runs on consolisheet.com, from exactly these files and decisions. When we ran the same files on the live site, its preview showed the same first rows.

What is free

Source File does not count toward the Free plan’s five business columns. This result has seven, so on consolisheet.com the complete download is a paid result; uploading, reviewing, confirming and a preview of the first rows are free. With five business columns or fewer, the complete download is free.

Download the example

Download the files and try the same steps yourself at Quick Consolidate. Every file is invented sample data.

• The five sample files: January.xlsx, February.xlsx, March.csv, April.xlsx and May.xlsx (about 5 KB each)
• The completed workbook: ConsoliSheet-example-consolidated.xlsx (6 KB), as produced by ConsoliSheet’s processor
• Notes: README.txt explains each decision; SHA256SUMS.txt lists the file checksums

Which method should you use?

For a fuller comparison, including Python and online mergers, see the best ways to combine multiple Excel files.

Keep a trace to the source

Add a Source File column to the combined output. When a number looks wrong later, this is what lets you go back to the exact spreadsheet it came from instead of re-checking everything.

Common questions