Combine Excel sheets into one table with a source-sheet column

01Define exactly what will be merged

The workflow combines selected worksheets from one workbook into a new workbook. Each source sheet must have exactly the same header row, including column order and capitalization. The output adds source_sheet as its first column so every record keeps its origin.

Blank rows are ignored. Nonblank rows are copied in worksheet order and then in their original row order. The source workbook is opened read-only, and the result is written to a separate folder beneath outputs.

02Create a synthetic workbook

The workbook below is synthetic and was written specifically for this article. Save the setup script as create_excel_merge_demo.py. It creates three sheets named East, West, and Online. Each uses the columns order_id, customer, and amount.

from pathlib import Path
from openpyxl import Workbook

SOURCE_DIR = Path("outputs") / "excel_merge_demo"
SOURCE = SOURCE_DIR / "regional_orders.xlsx"
HEADERS = ["order_id", "customer", "amount"]
DATA = {
    "East": [
        ["E001", "Ana", 120],
        ["E002", "Ben", 80],
    ],
    "West": [
        ["W001", "Cara", 150],
        ["W002", "Dan", 70],
    ],
    "Online": [
        ["O001", "Emi", 90],
        [None, None, None],
        ["O002", "Finn", 110],
    ],
}

SOURCE_DIR.parent.mkdir(parents=True, exist_ok=True)
SOURCE_DIR.mkdir()  # Stop if the synthetic source folder already exists.

workbook = Workbook()
for index, (sheet_name, rows) in enumerate(DATA.items()):
    if index == 0:
        sheet = workbook.active
        sheet.title = sheet_name
    else:
        sheet = workbook.create_sheet(sheet_name)
    sheet.append(HEADERS)
    for row in rows:
        sheet.append(row)

workbook.save(SOURCE)

python create_excel_merge_demo.py

There are 6 nonblank data records in total. Their amount values sum to 620: 120 + 80 + 150 + 70 + 90 + 110. Online deliberately contains one completely blank row between its two records so the merge logic can demonstrate that blank rows are skipped.

03Work out the expected combined rows by hand

The script explicitly lists the sheets as East, West, and Online. That order determines the order of the merged records. Within each sheet, records remain in their original top-to-bottom order.

The output therefore has 4 columns and 7 worksheet rows when the header is included: 1 header row plus 6 data rows. The source_sheet values also make a useful manual count: East appears twice, West appears twice, and Online appears twice.

04Merge the sheets into a new workbook

Save the following script as excel_merge_sheets.py. It opens the source workbook in read-only mode, validates the selected sheet names and headers, ignores completely blank rows, and rejects formula cells so that formulas are not silently detached from their original workbook context.

from pathlib import Path
from openpyxl import Workbook, load_workbook

SOURCE = Path("outputs") / "excel_merge_demo" / "regional_orders.xlsx"
OUTPUT_DIR = Path("outputs") / "excel_merge_result"
OUTPUT = OUTPUT_DIR / "merged_orders.xlsx"
SHEETS = ("East", "West", "Online")
OUTPUT_SHEET = "Combined"
SOURCE_COLUMN = "source_sheet"


def read_sheet_rows(sheet, expected_header=None):
    first_row = next(
        sheet.iter_rows(min_row=1, max_row=1, values_only=True),
        None,
    )
    if first_row is None:
        raise ValueError(f"Sheet is empty: {sheet.title}")

    header = tuple(first_row)
    if any(not isinstance(name, str) or not name.strip() for name in header):
        raise ValueError(f"Invalid header in sheet: {sheet.title}")
    if len(set(header)) != len(header):
        raise ValueError(f"Duplicate header name in sheet: {sheet.title}")
    if SOURCE_COLUMN in header:
        raise ValueError(f"Reserved column already exists: {SOURCE_COLUMN}")
    if expected_header is not None and header != expected_header:
        raise ValueError(f"Header mismatch in sheet: {sheet.title}")

    rows = []
    for excel_row, values in enumerate(
        sheet.iter_rows(min_row=2, max_col=len(header), values_only=True),
        start=2,
    ):
        if all(value is None for value in values):
            continue
        if any(isinstance(value, str) and value.startswith("=") for value in values):
            raise ValueError(
                f"Formula found in {sheet.title} row {excel_row}; "
                "this example merges literal values only."
            )
        rows.append(tuple(values))

    return header, rows


def main() -> None:
    source_path = SOURCE.resolve(strict=True)
    if not source_path.is_file():
        raise ValueError("SOURCE must be an Excel file.")
    if len(set(SHEETS)) != len(SHEETS):
        raise ValueError("SHEETS contains a duplicate sheet name.")

    source_workbook = load_workbook(
        source_path,
        read_only=True,
        data_only=False,
    )
    try:
        missing = [name for name in SHEETS if name not in source_workbook.sheetnames]
        if missing:
            raise ValueError(f"Missing sheets: {missing}")

        expected_header = None
        combined_rows = []
        for sheet_name in SHEETS:
            sheet = source_workbook[sheet_name]
            header, rows = read_sheet_rows(sheet, expected_header)
            if expected_header is None:
                expected_header = header
            for row in rows:
                combined_rows.append((sheet_name, *row))
    finally:
        source_workbook.close()

    OUTPUT_DIR.parent.mkdir(parents=True, exist_ok=True)
    OUTPUT_DIR.mkdir()  # Refuse to reuse an existing output folder.

    output_workbook = Workbook()
    output_sheet = output_workbook.active
    output_sheet.title = OUTPUT_SHEET
    output_sheet.append([SOURCE_COLUMN, *expected_header])
    for row in combined_rows:
        output_sheet.append(row)
    output_workbook.save(OUTPUT)

    check_workbook = load_workbook(OUTPUT, read_only=True, data_only=False)
    try:
        if check_workbook.sheetnames != [OUTPUT_SHEET]:
            raise RuntimeError("Unexpected output worksheet structure.")
        check_sheet = check_workbook[OUTPUT_SHEET]
        written = list(check_sheet.iter_rows(values_only=True))
    finally:
        check_workbook.close()

    expected = [(SOURCE_COLUMN, *expected_header), *combined_rows]
    if written != expected:
        raise RuntimeError("Saved workbook does not match the expected rows.")

    print(f"Merged {len(SHEETS)} sheets into {len(combined_rows)} data rows.")
    print(f"Verified {len(expected_header) + 1} columns and {len(combined_rows)} data rows.")
    print(f"Output: {OUTPUT.as_posix()}")


if __name__ == "__main__":
    main()

python excel_merge_sheets.py

05Compare the expected result

For the synthetic workbook, the output should contain exactly one worksheet named Combined. Its first row should be source_sheet, order_id, customer, amount, followed by the 6 records calculated earlier.

The expected console output below was derived from the code and synthetic data by hand. It is not a captured execution log.

Merged 3 sheets into 6 data rows.
Verified 4 columns and 6 data rows.
Output: outputs/excel_merge_result/merged_orders.xlsx

06Check the workbook before using real data

1. Open merged_orders.xlsx and confirm that there is one worksheet named Combined.
2. Confirm that source_sheet is the first column and that the original three columns follow in the same order.
3. Count the records by source_sheet. Each of East, West, and Online should appear exactly twice.
4. Add the amount column manually or with Excel. The expected total is 620.
5. Confirm that the blank row from Online did not become a blank record in the merged output.
6. Run the script again without changing OUTPUT_DIR. It should stop with FileExistsError rather than replace the previous result.

For real workbooks, change SHEETS explicitly instead of automatically merging every worksheet. This reduces the chance of accidentally including lookup tables, notes, hidden sheets, or summary sheets that happen to be stored in the same file.

07Recognize common errors

If saving or verification fails after OUTPUT_DIR has been created, the folder or workbook may remain as a partial result. Treat it as unverified and use a new destination after correcting the problem.

08Understand the limits

The script is intended for rectangular data sheets with one header row. It does not handle multirow headers, merged header cells, pivot tables, charts, images, or worksheets where several unrelated tables share the same sheet.

Only cell values are consolidated. Number formats, fonts, fills, conditional formatting, validation rules, hyperlinks, comments, formulas, row heights, column widths, and worksheet-level settings are not reproduced. Literal date and numeric values can be carried as values, but their original display formatting is not copied.

The script also loads all merged row tuples into Python memory before creating the output workbook. That is convenient for this small verified workflow but may not suit very large workbooks. For large files, a streaming design with write-only output and separate incremental validation would reduce memory use.

Execution and verification record

ChatGPT Python tool · Python 3.13.5 · openpyxl 3.1.5

• The source workbook contained 2 nonblank East rows, 2 West rows, and 2 Online rows; the deliberately blank Online row was skipped.
• The source amount totals were East=200, West=220, Online=200, for 620 overall.
• The main script printed exactly: 'Merged 3 sheets into 6 data rows.', 'Verified 4 columns and 6 data rows.', and 'Output: outputs/excel_merge_result/merged_orders.xlsx'.
• The output workbook contained exactly one worksheet named Combined.
• The output header was exactly source_sheet, order_id, customer, amount.
• The six output records were in the expected order: East E001/E002, West W001/W002, Online O001/O002.
• Each of East, West, and Online appeared exactly twice in source_sheet.
• The merged amount total was 620.
• The output contained 4 columns and 6 data rows, or 7 worksheet rows including the header.
• The SHA-256 hash of regional_orders.xlsx was unchanged after the merge, confirming the source workbook was not rewritten.
• A second run with outputs/excel_merge_result already present raised FileExistsError before replacing the previous result.

References

The explanations and examples were written for this site. See the official sources below for the related behavior and concepts.

• Official openpyxl documentation ↗ (New tab)
• Official openpyxl documentation · Tutorial ↗ (New tab)
• Official Python 3.12 documentation · pathlib ↗ (New tab)