Combine Sheets with Different Layouts into One Table

Consolidating "the same" data from several sheets is easy when the sheets really are the same. More often each was built by a different person: the North branch puts a title and a date above its table and calls the column Qty, the South branch starts in row 1 with Units and lists the product before the date, the West branch adds a Comments column and a total row at the bottom, and one sheet says Net Sales where the others say Revenue. pd.concat on such sheets produces a wide, mostly empty table with a column for every spelling, total rows counted as sales and title text in the date column. This page combines inconsistent sheets properly: it finds each sheet's header row from known column names, maps the different spellings to one standard set, keeps the standard columns in a fixed order, drops total and note rows, records which sheet each row came from, and reports any column it could not map — so a new spelling shows up in the log rather than as silently lost data. It belongs to Extracting Data from Messy Excel Sheets.

Prerequisites

pip install pandas openpyxl

The workbook has four branch sheets with the same kind of data — date, product, quantity, revenue — in four layouts: a title block above the header, a different column order, a total row and a note, and a table starting in B3 with lower-case and upper-case headers and an extra Channel column.

Step 1 — Define the standard columns and their synonyms

import pandas as pd

STANDARD = ["date", "product", "qty", "revenue"]
SYNONYMS = {
    "date": {"date", "order date", "sale date"},
    "product": {"product", "item", "sku"},
    "qty": {"qty", "units", "quantity"},
    "revenue": {"revenue", "net sales", "sales £", "sales"},
}
LOOKUP = {alias: std for std, aliases in SYNONYMS.items() for alias in aliases}

def standard_name(header):
    return LOOKUP.get(str(header).strip().lower())

The synonym table is the single place where knowledge about the sheets' layouts lives: four standard names, each with the spellings seen so far. Matching is done on the trimmed, lower-cased header, so PRODUCT, Product and product are the same. When a branch invents a new spelling, adding it here is the whole fix.

Step 2 — Find each sheet's header row and map its columns

def read_sheet(path, sheet, min_matches=3):
    raw = pd.read_excel(path, sheet_name=sheet, header=None)
    for i, row in raw.iterrows():
        mapped = [standard_name(v) for v in row]
        if sum(m is not None for m in mapped) >= min_matches:
            break
    else:
        raise ValueError(f"{sheet}: no header row with {min_matches} known columns")
    body = raw.iloc[i + 1:].copy()
    body.columns = [standard_name(v) or (str(v) if pd.notna(v) else f"_blank{j}") for j, v in enumerate(row)]
    body = body.loc[:, [not c.startswith("_blank") for c in body.columns]]
    unmapped = [str(v) for v, m in zip(row, mapped) if m is None and pd.notna(v)]
    missing = [c for c in STANDARD if c not in body.columns]
    return body, i, unmapped, missing

with pd.ExcelFile("branches.xlsx") as xf:
    sheets = xf.sheet_names
for sheet in sheets:
    body, header_row, unmapped, missing = read_sheet("branches.xlsx", sheet)
    print(f"{sheet:<6} header at row {header_row + 1}, unmapped: {unmapped or '-'}, missing: {missing or '-'}")

Reading with header=None gives the raw grid, so the code can look for the header itself: the first row in which at least three cells are known column names. That skips the North sheet's title block and finds the East table at row 3 wherever it starts. Each column is renamed to its standard name; columns with no header at all, such as the empty column A on the East sheet, are dropped; columns with an unknown header keep their original name and are reported — Rep, Comments and Channel here — so nothing disappears without a trace. A sheet missing a standard column is reported too.

Step 3 — Clean, stack and keep the source

def clean(body, sheet):
    df = body[[c for c in STANDARD if c in body.columns]].copy()
    df = df.dropna(how="all")
    is_total = df["product"].astype(str).str.strip().str.lower().isin({"total", "grand total", "subtotal"})
    df = df[~is_total]
    df["date"] = pd.to_datetime(df["date"], errors="coerce")
    df[["qty", "revenue"]] = df[["qty", "revenue"]].apply(pd.to_numeric, errors="coerce")
    df = df.dropna(subset=["qty", "revenue"], how="all")          # notes and other text-only rows
    return df.assign(source=sheet)

parts, report = [], []
for sheet in sheets:
    body, header_row, unmapped, missing = read_sheet("branches.xlsx", sheet)
    part = clean(body, sheet)
    parts.append(part)
    report.append({"sheet": sheet, "header row": header_row + 1, "rows": len(part), "unmapped": ", ".join(unmapped), "missing": ", ".join(missing)})
combined = pd.concat(parts, ignore_index=True)[STANDARD + ["source"]]
print(combined.to_string(index=False))
print(pd.DataFrame(report).to_string(index=False))

Only the standard columns are kept, in the standard order, so the stacked table has exactly four data columns plus source. Total rows — recognised by "Total" in the product column — are removed, and after the numeric columns are converted, rows with neither a quantity nor a revenue are dropped, which removes notes such as "Note: excludes returns" wherever they were typed; counting the West branch's total row as a sale would add 350 units that were already counted. Dates and numbers are converted with errors="coerce", so anything unparseable becomes missing rather than breaking the load. The source column means every row in the combined table can be traced to its sheet. Totals per branch can now be checked against what each branch reported — the West branch's own total row said 350 units and £108, which is exactly what its two remaining rows add up to.

Step 4 — Write the table and the report together

with pd.ExcelWriter("branches_combined.xlsx", engine="openpyxl", date_format="yyyy-mm-dd") as xw:
    combined.to_excel(xw, sheet_name="Combined", index=False)
    pd.DataFrame(report).to_excel(xw, sheet_name="Load report", index=False)
    combined.pivot_table(index="source", values=["qty", "revenue"], aggfunc="sum").to_excel(xw, sheet_name="By branch")
print(combined.groupby("source")[["qty", "revenue"]].sum().to_string())

The output workbook has the combined data, the load report — which header row was used on each sheet, how many rows were loaded, which columns were unmapped or missing — and a per-branch summary that each branch manager can check against their own figures. The load report is what keeps the process honest over time: if a branch renames Qty to Pieces next month, the sheet still loads, but qty appears under missing and Pieces under unmapped, and the fix is one line in the synonym table. Loading many workbooks rather than many sheets works the same way, as in Combine Multiple Excel Files into One in Python.

When to keep the extra columns

The approach above keeps only the standard columns, which is right when the combined table feeds a report with a fixed shape. Sometimes the extras matter — the Channel column from the East branch might be exactly what someone wants to analyse. Two options keep them without making the table ragged. Add the column to the standard set, with synonyms, and accept that it will be empty for branches that do not record it; the load report then shows which branches are missing it, which is useful information in itself. Or write the unmapped columns into a separate long table — sheet, row, column name, value — so nothing is lost and the main table stays clean. What to avoid is the default of pd.concat, which keeps every spelling as its own column and leaves readers to work out that Qty, Units and Quantity mean the same thing.

Common pitfalls

• Concatenating without renaming. Every spelling becomes its own mostly-empty column.
• Fixed header= per sheet. Find the header row from known names instead.
• Counting total rows as data. Remove rows labelled Total or Subtotal.
• Dropping unknown columns silently. Report them so new spellings are noticed.
• No source column. Without it, combined rows cannot be traced back.

Conclusion

To combine sheets that differ in layout, read each as a raw grid, find the header row as the first row with enough known column names, rename columns through a synonym map to a standard set, keep those columns in a fixed order, remove total and note rows, convert types and add the source sheet. Stack the results and write a load report of header rows, row counts and unmapped or missing columns, so changes in the sheets are visible and fixed in the synonym map.

Frequently asked questions

How do I combine Excel sheets with different column names in pandas? Rename each sheet's columns to a standard set with a dictionary of synonyms, keep only the standard columns in a fixed order, then concatenate.

How do I find the header row when it differs between sheets? Read the sheet with header=None and take the first row in which several cells match known column names.

How do I stop total rows being added to the combined data? Filter out rows whose label column says Total, Subtotal or Grand Total before concatenating.

What should happen to columns that only some sheets have? Either add them to the standard set and accept empty values for other sheets, or report them in a load log, but do not let them become extra ragged columns by accident.

Related

• Up one level: Extracting Data from Messy Excel Sheets — getting data out of untidy sheets.
• Read Multiple Tables from One Excel Sheet — several tables on one sheet.
• Remove Subtotal and Total Rows from an Excel Export — totals in more detail.
• Standardise Excel Column Names with pandas — cleaning header text.