Python Excel AutomationPractical guides for developers
Start learning
Guide
Getting Started With Python Excel AutomationDeep dive

Handle Merged Cells When Reading Excel with pandas

Merged cells read as one value and a run of blanks. Fix them in pandas with forward fill, detect merges with openpyxl, and unmerge a workbook before importing it.

A merged cell looks like one cell holding one value. In the file it is nothing of the sort: Excel stores the value in the top-left cell of the range and leaves every other cell genuinely empty. pandas reads exactly that, so a tidy-looking sheet with a merged Region column arrives as one label followed by three NaNs — and if you group by that column, three-quarters of the rows fall out. This guide covers detecting merges, filling them correctly, and unmerging a workbook at the source. It extends Reading Excel Files with pandas.

What a merged cell looks like in Excel and in a DataFrame On the left, an Excel sheet where the Region cell for North spans three rows as a single merged block covering three branch rows. On the right, the same data read into pandas: the first row carries North and the next two rows carry NaN, because the file only ever stored the value once. A group-by on region would therefore see one North row rather than three. in Excel in pandas Region Branch North merged A2:A4 Branch 1 Branch 2 Branch 3 read_excel region branch North NaN NaN Branch 1 Branch 2 Branch 3 group by region and two of the three branches disappear

Prerequisites

Bash
pip install pandas openpyxl

A workbook with a vertical merge, so every example has something real to work on:

Python
from openpyxl import Workbook

wb = Workbook()
ws = wb.active
ws.append(["Region", "Branch", "Revenue"])
for row in [
    ["North", "Branch 1", 5150.00], [None, "Branch 2", 4268.00],
    [None, "Branch 3", 3511.25], ["South", "Branch 4", 2980.10],
    [None, "Branch 5", 3140.75],
]:
    ws.append(row)

ws.merge_cells("A2:A4")      # North spans three branch rows
ws.merge_cells("A5:A6")      # South spans two
wb.save("merged.xlsx")

Step 1 — Confirm the blanks really come from merges

Do not assume. A NaN in a label column might be a merge, or it might be genuinely missing data — and the fixes are opposite. openpyxl tells you definitively:

Python
from openpyxl import load_workbook

wb = load_workbook("merged.xlsx")
ws = wb.active

for rng in ws.merged_cells.ranges:
    print(rng, "->", ws.cell(rng.min_row, rng.min_col).value)
# A2:A4 -> North
# A5:A6 -> South

Classify them by orientation, because vertical and horizontal merges need different handling:

Python
from openpyxl import load_workbook
from openpyxl.utils import get_column_letter

def describe_merges(path, sheet_name=None):
    """Summarise the merged ranges in a sheet by orientation."""
    wb = load_workbook(path)
    ws = wb[sheet_name] if sheet_name else wb.active

    report = {"vertical": [], "horizontal": [], "block": []}
    for rng in ws.merged_cells.ranges:
        tall = rng.max_row > rng.min_row
        wide = rng.max_col > rng.min_col
        kind = "block" if (tall and wide) else ("vertical" if tall else "horizontal")
        report[kind].append({
            "ref": str(rng),
            "column": get_column_letter(rng.min_col),
            "value": ws.cell(rng.min_row, rng.min_col).value,
        })
    return report

info = describe_merges("merged.xlsx")
print(f"{len(info['vertical'])} vertical, {len(info['horizontal'])} horizontal")

Vertical merges are label columns and forward-fill correctly. Horizontal merges are usually group headers and belong in the header handling covered by skipping rows and setting the header. Block merges are title banners and should be excluded from the data range entirely.

Step 2 — Forward-fill the label column

For vertical merges, ffill restores what the sheet visually implies:

Python
import pandas as pd

df = pd.read_excel("merged.xlsx")
print(df["Region"].tolist())
# ['North', nan, nan, 'South', nan]

df["Region"] = df["Region"].ffill()
print(df["Region"].tolist())
# ['North', 'North', 'North', 'South', 'South']

Fill only the columns you know are merged. A blanket df.ffill() propagates values across every column, which invents data in numeric fields — a missing revenue silently becomes the previous branch's revenue:

Python
# Right: named columns only.
MERGED_LABELS = ["Region", "Category"]
df[MERGED_LABELS] = df[MERGED_LABELS].ffill()

# Wrong: fills revenue gaps with the row above.
df = df.ffill()

Two guards make the fill safe. First, a leading NaN has nothing above it to inherit, which means the sheet did not start where you thought:

Python
if df["Region"].isna().iloc[0]:
    raise ValueError(
        "The first row has no Region — the header row is probably wrong."
    )

Second, cap how far a value may propagate. An unbounded fill will happily carry a label across a hundred rows if the sheet has a gap in it:

Python
# A merge realistically spans a handful of rows, not fifty.
df["Region"] = df["Region"].ffill(limit=20)

still_missing = df["Region"].isna().sum()
if still_missing:
    print(f"warning: {still_missing} rows still have no Region after filling")

Step 3 — Unmerge at the source instead

Filling in pandas is a workaround. If the same file arrives every month, flattening the workbook once is cleaner — every downstream reader then gets a rectangular sheet with no special handling at all.

Flattening a merged sheet in three moves Three ordered steps applied to each merged range. First the top-left cell's value is captured, because removing the merge would otherwise lose it. Second unmerge_cells removes the merge definition, leaving one populated cell and the rest empty. Third the captured value is written into every cell of the former range, producing a rectangular sheet that any reader handles without special cases. 1 · capture read the top-left value before touching the merge it is the only copy 2 · unmerge ws.unmerge_cells(ref) one cell keeps the value the rest are empty 3 · fill write it to every cell the sheet is now rectangular no reader needs a special case always write to a copy — flattening destroys the layout the original was designed for
Python
from openpyxl import load_workbook
from openpyxl.utils import range_boundaries

def unmerge_and_fill(src, dest, sheet_name=None):
    """Flatten every merged range so each cell carries its own value."""
    wb = load_workbook(src)
    sheets = [wb[sheet_name]] if sheet_name else wb.worksheets
    flattened = 0

    for ws in sheets:
        # Copy the list: unmerging mutates the collection we are iterating.
        for ref in [str(r) for r in ws.merged_cells.ranges]:
            min_col, min_row, max_col, max_row = range_boundaries(ref)
            value = ws.cell(min_row, min_col).value

            ws.unmerge_cells(ref)
            for row in range(min_row, max_row + 1):
                for col in range(min_col, max_col + 1):
                    ws.cell(row, col, value)
            flattened += 1

    wb.save(dest)
    return flattened

print(f"flattened {unmerge_and_fill('merged.xlsx', 'flat.xlsx')} ranges")

Two details matter. The list comprehension around ws.merged_cells.ranges takes a snapshot before iterating — unmerging modifies that collection, and iterating it directly skips ranges or raises. And the value must be captured before the unmerge, because the merge is the only thing keeping it addressable as a single logical cell.

Write to a new file. Flattening is lossy in the other direction: the original layout was designed for human reading, and you cannot reconstruct which ranges were merged once they are gone.

Step 4 — Handle merged headers

Merge orientation decides the fix Three shapes. A tall narrow merge spanning several rows in one column is a label such as a region name, and forward-filling down restores it. A wide flat merge spanning several columns in one row is a group header, and filling rightwards across the upper header level restores it. A merge spanning both rows and columns is a title banner and should be excluded from the data range entirely rather than filled. vertical North a label column ffill downwards horizontal Q1 a group header fill rightwards block Regional report a title banner exclude, do not fill

A group header spanning three columns leaves two Unnamed: names. Read the header as a list of rows and forward-fill the upper level across:

Python
import pandas as pd

raw = pd.read_excel("quarterly.xlsx", header=[0, 1])

groups = (
    pd.Series([None if str(a).startswith("Unnamed:") else a
               for a, _ in raw.columns])
    .ffill()                       # spread the group name rightwards
)
details = [b for _, b in raw.columns]

raw.columns = [
    f"{g}_{d}" if pd.notna(g) else str(d) for g, d in zip(groups, details)
]
print(raw.columns.tolist())
# ['Q1_Units', 'Q1_Revenue', 'Q1_Margin', 'Q2_Units', ...]

This is the horizontal mirror of the vertical fill: the merge stored Q1 once, so the two columns to its right inherit it.

Common pitfalls and fixes

SymptomCauseFix
Label column full of NaNVertical mergesffill() on that column only.
Numeric gaps filled with the row aboveBlanket df.ffill()Fill named label columns only.
Header half Unnamed:Horizontal merges in the headerRead header=[0,1] and fill the upper level.
First row's label is NaNHeader index wrongPeek with header=None and fix the index.
RuntimeError while unmergingIterating the live ranges collectionSnapshot the refs into a list first.
Value lost after unmergingUnmerged before reading the valueCapture the top-left value first.
Fill spans far too many rowsUnbounded ffill over a real gapPass a limit, then check what remains.
MergedCell is read-onlyWriting to a non-anchor cell of a mergeUnmerge the range first, then write.

Performance and scale notes

Merge handling costs little in pandas — ffill is a vectorised pass — but the openpyxl side is where a large workbook can hurt. unmerge_and_fill writes a value into every cell of every former range, and on a sheet with tens of thousands of small merges that is a lot of individual cell assignments.

Two ways to keep it manageable. Do the fill in pandas rather than in the workbook when you only need the data, not a flattened file. A single ffill over a column is orders of magnitude faster than writing the same values cell by cell:

Python
import pandas as pd

# Fast: one vectorised pass, no workbook rewrite.
df = pd.read_excel("merged.xlsx")
df[["Region"]] = df[["Region"]].ffill()

Restrict the flatten to the columns that need it when you do want a flattened file. Most sheets merge one or two label columns and nothing else, so filtering the ranges first avoids touching the rest:

Python
from openpyxl.utils import column_index_from_string, range_boundaries

def unmerge_columns(ws, letters):
    """Flatten merges only in the named columns."""
    wanted = {column_index_from_string(c) for c in letters}
    for ref in [str(r) for r in ws.merged_cells.ranges]:
        min_col, min_row, max_col, max_row = range_boundaries(ref)
        if min_col not in wanted:
            continue
        value = ws.cell(min_row, min_col).value
        ws.unmerge_cells(ref)
        for row in range(min_row, max_row + 1):
            ws.cell(row, min_col, value)

One structural note: merged cells cannot be read at all in openpyxl's read_only mode — the merge definitions are not materialised, so ws.merged_cells.ranges comes back empty. That means the fast streaming path described in speeding up openpyxl with read-only mode cannot detect merges, and a large merged workbook must be opened normally at least once. The pragmatic answer for a recurring feed is to flatten it once at ingest, as described in handling Excel file formats and conversions, and let everything downstream read a rectangular file at full speed.

Conclusion

Merged cells are not a pandas bug — Excel really does store the value once and leave the rest of the range empty. Confirm with openpyxl's merged_cells.ranges that the blanks come from merges rather than missing data, then forward-fill the specific label columns, with a limit so a real gap cannot propagate a label down the whole sheet. For a file that arrives every month, flatten it once with unmerge-and-fill and write to a copy, so every downstream reader sees a rectangular sheet and no special case is needed again.

Frequently asked questions

Why do merged cells come back as NaN in pandas? Excel stores a merged range's value in its top-left cell only; every other cell in the range is genuinely empty in the file. pandas reads what is there, so you get one value followed by blanks.

Is ffill always the right fix? Only for vertical merges in a label column, and only after you have confirmed the blanks come from merges rather than from genuinely missing data. Forward-filling real gaps invents values, which is worse than leaving them blank.

How do I see which ranges are merged? Open the workbook with openpyxl and read ws.merged_cells.ranges. It gives every merged range as a coordinate string, which you can group by orientation to see whether the merges are vertical labels or horizontal headers.

Can I unmerge without opening Excel? Yes. openpyxl's unmerge_cells removes the merge, and you then write the top-left value into every cell of the former range so the data survives. Do it on a copy, not the original.

My header row is half Unnamed: — is that merged cells too? Almost certainly. A group header spanning three columns stores its text once, so the two columns to its right read as blank and pandas names them Unnamed:. Read with header set to a list and forward-fill the upper level.