One Header Row and No Merged Cells in Import Templates

The most common reason a spreadsheet does not load is the one that makes it look good. A company title in the first row, a logo, a merged heading "Q1 Sales" over three columns, a region name merged down five rows. To a reader, that is structure. To a loader, it is cells in the wrong places, empty cells where values should be and a header that is not where it should be. This article explains exactly what merging does to the file, how to detect it and how to fix files that arrive with it. It is one of the layout rules in building Excel templates that import cleanly and fits the controls in controlling what goes into a spreadsheet.

What merging does to the file

In the sheet XML, a merge is recorded as a list of ranges in a mergeCells element: <mergeCell ref="A2:A6"/>. The value, the format and any formula live in the top left cell of the range, A2. The other cells, A3 to A6, remain in the grid but are empty. Excel draws the merged range as one big cell, so a reader sees one value spanning five rows.

A program that reads cell by cell sees something different. Row 2 has the region "North". Rows 3 to 6 have an empty region. If the table is read into a data frame, those four rows have a missing region, though the sheet shows "North" next to all five. Summing by region then attributes one row to North and four to nothing.

Horizontal merges do the same across columns. A heading "Q1" merged over B1:D1 has its text in B1, with C1 and D1 empty. A header row read from that sheet has the names "Q1", None and None, and the columns collapse into duplicates or unnamed fields.

Excel's own features dislike merges too. Sorting a range containing merged cells fails with "all merged cells need to be the same size". Filtering shows only the first row of a merged group. Pivot tables refuse a source with merged headers. Copying and pasting merged cells spreads the problem.

The header must be the first row

A loader looks for the header at a fixed place, and the easiest place is row 1. Anything above it is a problem. A title ("Monthly sales, Acme Ltd"), a date stamp, a logo (an image floating over cells, which the loader ignores but which often sits on top of the header) or a blank spacer row each push the header down.

If you must tolerate titles, the loader can search for the header: scan the first 20 rows for the row that contains the expected column names. That works and it is fragile; a title that happens to contain a column name fools it. It is better to design the template with the header in row 1 and to make the instructions say so. For incoming files from third parties whom you cannot control, search for the header and log what you did (handling Excel files with title rows and skipping header junk when reading Excel cover the reader side).

Detect merged cells in code

openpyxl exposes merged ranges, so a loader can check:

from openpyxl import load_workbook

wb = load_workbook("incoming.xlsx")
ws = wb["data"]
merged = [str(r) for r in ws.merged_cells.ranges]
if merged:
    raise ValueError(f"Merged cells are not allowed: {', '.join(merged[:10])}")

ws.merged_cells.ranges is a collection of CellRange objects, whose string form is like A2:A6. Reporting the first few ranges tells the sender where to look. This check needs the sheet loaded in normal mode; in read_only mode the merge information is not available, so do this check on a normal load or with a quick scan of the sheet XML for <mergeCell.

You may prefer to allow merges outside the data area (a title block on an instructions sheet is harmless) and reject them inside it. Check whether any merged range overlaps the table's rectangle:

from openpyxl.utils import range_boundaries

min_col, min_row, max_col, max_row = range_boundaries(table_ref)    # e.g. "A1:H200"
for r in ws.merged_cells.ranges:
    if r.min_row <= max_row and r.max_row >= min_row and r.min_col <= max_col and r.max_col >= min_col:
        raise ValueError(f"Merged cells inside the table: {r}")

Repair a file that arrives merged

Sometimes the file is what it is, and the loader has to cope. The standard repair: unmerge each range and fill the value into every cell of the range, which restores the meaning the reader saw.

for rng in list(ws.merged_cells.ranges):
    min_col, min_row, max_col, max_row = rng.min_col, rng.min_row, rng.max_col, rng.max_row
    value = ws.cell(row=min_row, column=min_col).value
    ws.unmerge_cells(str(rng))
    for row in ws.iter_rows(min_row=min_row, max_row=max_row, min_col=min_col, max_col=max_col):
        for cell in row:
            cell.value = value

For a vertical merge of "North" over five rows, every row now says "North". For a horizontal heading, filling the heading across columns produces duplicates, which you must resolve by combining with the lower header row. Note: after the unmerge, openpyxl leaves the non-anchor cells empty, which is why the loop writes the value. In pandas, the equivalent for vertical merges is a forward fill: df["region"] = df["region"].ffill(). Use it only for columns where blanks mean "same as above", never for a column where a blank is a real blank. See Excel merged cells and hidden rows and handling merged cells when reading with openpyxl for more.

Flatten two-level headers

A report-style header often has two rows: a group heading and a column heading.

To make it loadable, combine the levels into one name per column: customer, billing_street, billing_city, shipping_street, shipping_city. In code, read the two header rows, forward-fill the group row across blank cells (which is what the merge meant), and join:

rows = list(ws.iter_rows(min_row=1, max_row=2, values_only=True))
group, last = [], None
for g in rows[0]:
    last = g if g is not None else last
    group.append(last)
names = ["_".join(str(x).strip().lower() for x in (g, c) if x) for g, c in zip(group, rows[1])]

For the example this yields customer, billing_street, and so on (with the first column's group blank). Flattening in the template is better than in the loader: give the sender a single header row to begin with.

Alternatives to merging for appearance

People merge for looks. Give them a way to get the same look without damage.

Center across selection. Format Cells, Alignment, Horizontal: Center Across Selection centers a heading over several columns without merging them. The text stays in the left cell, and the cells beside it are left alone. It is the right tool for report headings.

Group in the column name. billing_city says it all.

Fill color and borders. Visual grouping with a fill or a border line needs no merge.

Separate the presentation. Keep the data sheet plain and build a presentation sheet that reads from it with formulas, where merging does no harm because no loader reads that sheet.

Add a guard to the template

Make the template resist merging. Protect the data sheet and leave merging disallowed (the "Format cells" permission includes merging, so leaving it off prevents it), keep the instructions sheet as the only one with merged title cells, and state the rule in a sentence: "Do not merge cells or add rows above the header." A guard in the template plus the check in the loader covers both sides (locking down shared input sheets without breaking them).

Test the check

Build fixtures with openpyxl: a clean file, a file with a vertical merge in the data, a file with a horizontal merge in the header, a file with a title row above the header and a file with a merge on the instructions sheet only. Assert that the loader accepts the first and last, rejects the middle three with messages that name the range, and repairs the second when repair mode is on. The fixtures also document what the template forbids.

When a multi-row header cannot be avoided

Some sources can only produce a report layout: a legacy system's export, a regulator's form, a vendor's price sheet. You cannot change them, so write a specific reader for each, with the header rows, the merged heading handling and the title skipping documented in code, and test it against a sample file in the repository. Treat the reader as a translation layer whose output is the clean table. Do not let the messy layout leak further into the system. If the file is a form, not a table, read the cells you need by address rather than as a grid. And where the source is changing and the layout shifts each month, ask for a data export instead of the report. The openpyxl documentation on merged cells lists the API, and the ECMA-376 specification defines the mergeCells element.