Unmerge Cells and Fill the Values with openpyxl

Merged cells are the most common reason a sheet that looks like a table does not behave like one. A region name merged down over five rows reads fine on screen, but Excel stores it only once, in the top-left cell; the other four cells are empty. Sort the sheet and the region detaches from its rows; read it with a script and four rows have no region at all. The fix is to unmerge every range and copy the value into each cell it used to cover. This guide, part of Using openpyxl for Excel File Manipulation, does that with openpyxl, keeps the formatting, and covers the cases where merge information is not available.

Prerequisites

pip install openpyxl pandas

The example sheet has regions merged down column A and a note merged across D1:E1 — one vertical and one horizontal merge:

from openpyxl import Workbook, load_workbook
from openpyxl.styles import Alignment, Border, Side

wb = Workbook()
ws = wb.active
ws.title = "Reps"
for row in [["Region", "Rep", "Q1"], ["North", "Ana", 10], [None, "Ben", 12],
            [None, "Cy", 9], ["South", "Dee", 7], [None, "Eve", 8]]:
    ws.append(row)
ws["D1"] = "Provisional"
ws.merge_cells("A2:A4")
ws.merge_cells("A5:A6")
ws.merge_cells("D1:E1")
ws["A2"].alignment = Alignment(vertical="center")
ws["A2"].border = Border(left=Side(style="thin"))
wb.save("merged.xlsx")

Step 1 — See what openpyxl gives you for a merged range

Load the workbook normally and look at a cell inside a merged range:

ws = load_workbook("merged.xlsx")["Reps"]
print(type(ws["A3"]).__name__, ws["A3"].value)   # MergedCell None
print(sorted(r.coord for r in ws.merged_cells.ranges))
# ['A2:A4', 'A5:A6', 'D1:E1']

try:
    ws["A3"] = "North"
except AttributeError as exc:
    print(exc)                                    # 'MergedCell' object attribute 'value' is read-only

Every cell except the top-left of a range is a MergedCell — a placeholder with no value that refuses writes. ws.merged_cells.ranges lists the ranges as CellRange objects with min_row, max_row, min_col, max_col and a coord string, which is everything the fix needs.

Step 2 — Unmerge every range and fill it

For each range: remember the top-left value, unmerge, then write that value into every cell of the former range. Iterate over a copy of the range list, because unmerging removes entries from the collection you are looping over:

def unmerge_and_fill(ws) -> int:
    ranges = list(ws.merged_cells.ranges)
    for rng in ranges:
        top_left = ws.cell(rng.min_row, rng.min_col)
        value = top_left.value
        ws.unmerge_cells(rng.coord)
        for row in ws.iter_rows(min_row=rng.min_row, max_row=rng.max_row,
                                min_col=rng.min_col, max_col=rng.max_col):
            for cell in row:
                cell.value = value
    return len(ranges)

print(unmerge_and_fill(ws))                           # 3
print([ws.cell(r, 1).value for r in range(1, 7)])
# ['Region', 'North', 'North', 'North', 'South', 'South']
print(ws["E1"].value, ws.merged_cells.ranges)         # Provisional set()

The horizontal merge is handled by the same loop: D1:E1 becomes two cells that both read Provisional. Whether that is what you want depends on the range — for a label spanning two header columns, a duplicated label is usually right; for a title merged across a whole table, you probably want it in the first cell only. Step 4 shows how to choose per range.

Step 3 — Keep the formatting

Unmerging leaves the style on the top-left cell, but the newly ordinary cells take the default style, so a bordered, centred block can come out patchy. Copy the top-left cell's style to the rest of the range while filling:

from copy import copy

def unmerge_fill_styled(ws) -> None:
    for rng in list(ws.merged_cells.ranges):
        top_left = ws.cell(rng.min_row, rng.min_col)
        value = top_left.value
        styles = {a: copy(getattr(top_left, a)) for a in ("font", "fill", "border", "alignment", "number_format")}
        ws.unmerge_cells(rng.coord)
        for row in ws.iter_rows(min_row=rng.min_row, max_row=rng.max_row,
                                min_col=rng.min_col, max_col=rng.max_col):
            for cell in row:
                cell.value = value
                for attr, style in styles.items():
                    setattr(cell, attr, copy(style))

ws = load_workbook("merged.xlsx")["Reps"]
unmerge_fill_styled(ws)
print(ws["A3"].alignment.vertical, ws["A3"].border.left.style)   # center thin

Capture the styles before unmerging, while the top-left cell is guaranteed to hold them. The attribute-by-attribute copy is the same technique explained in Copy Cell Styles from One Cell to Another with openpyxl.

Step 4 — Decide per range: fill, keep first, or leave merged

Not every merge should be filled. A reasonable rule set: fill vertical merges inside the data area (those are keys), keep only the first cell for wide horizontal merges in the title rows (those are headings), and leave anything outside the table alone. Encode it as a small policy function:

def policy(rng, header_rows: int = 1) -> str:
    if rng.max_row <= header_rows and rng.max_col - rng.min_col >= 1:
        return "first"          # a heading spanning columns
    if rng.min_col == rng.max_col:
        return "fill"           # a key merged down one column
    return "fill"

def unmerge_with_policy(ws) -> dict:
    counts = {"fill": 0, "first": 0}
    for rng in list(ws.merged_cells.ranges):
        action = policy(rng)
        value = ws.cell(rng.min_row, rng.min_col).value
        ws.unmerge_cells(rng.coord)
        if action == "fill":
            for row in ws.iter_rows(min_row=rng.min_row, max_row=rng.max_row,
                                    min_col=rng.min_col, max_col=rng.max_col):
                for cell in row:
                    cell.value = value
        counts[action] += 1
    return counts

ws = load_workbook("merged.xlsx")["Reps"]
print(unmerge_with_policy(ws))              # {'fill': 2, 'first': 1}
print(ws["D1"].value, ws["E1"].value)       # Provisional None

The heading now lives only in D1, while both region blocks are filled. Adjust header_rows to the height of your title block.

Writing the policy down as code, rather than unmerging everything the same way, also documents your understanding of the layout. When a new version of the sheet adds a merged footer, the policy makes you decide what it means instead of quietly filling it into cells that downstream code will read as data.

When you only need the data: the pandas route

If the goal is a DataFrame rather than a fixed workbook, you do not need openpyxl at all. pandas reads a vertical merge as a value followed by missing cells, and forward-filling that column restores it:

import pandas as pd

df = pd.read_excel("merged.xlsx", sheet_name="Reps", usecols="A:C")
print(df["Region"].tolist())               # ['North', nan, nan, 'South', nan]
df["Region"] = df["Region"].ffill()
print(df["Region"].tolist())               # ['North', 'North', 'North', 'South', 'South']

This is quicker, but it cannot tell a merged blank from a genuinely empty cell — ffill fills both. When some blanks must stay blank, use the openpyxl route, which knows exactly which cells were merged. The pandas side, including multi-row headers, is covered in Handle Merged Cells When Reading Excel with pandas.

Common pitfalls

• Read-only mode has no merge information. load_workbook(..., read_only=True) returns worksheets without merged_cells, so the ranges cannot be found. Load normally for this job.
• Mutating the range set while iterating. Always loop over list(ws.merged_cells.ranges).
• Formulas in merged cells. The top-left cell may hold a formula. Copying it verbatim into the other cells copies the formula text, whose relative references then point at the wrong cells. Copy the cached value (load with data_only=True) or rewrite the formula per cell.
• Merges that cross the table edge. A title merged across columns A:H above a table of five columns will be filled into three extra cells if you use plain fill. Use a policy as in Step 4.

Performance and scale notes

The work is proportional to the number of cells covered by merges, not to the size of the sheet, so even large sheets unmerge in well under a second unless they are merged almost everywhere. Loading the workbook without read_only is the real cost on big files; if the sheet is huge and the merges only affect one key column, the pandas ffill route avoids loading styles at all. For whole-sheet clean-ups of human-built layouts — merged headers, subtotal rows, stacked tables — see Extracting Data from Messy Excel Sheets.

Conclusion

Merged ranges keep their value in the top-left cell only. With openpyxl, list the ranges from ws.merged_cells.ranges, loop over a copy, unmerge each one, write the saved value into every cell and copy the top-left style so the block keeps its look. Decide per range whether a heading should be duplicated or kept once, and use pandas ffill instead when a DataFrame is all you need and every blank in the key column really is a merge.

Frequently asked questions

Why do merged cells read as None in openpyxl? Excel stores the value only in the top-left cell of a merged range. The other cells are placeholders, which openpyxl represents as MergedCell objects whose value is always None and cannot be set.

How do I unmerge every merged cell in a sheet? Loop over a copy of ws.merged_cells.ranges — list(...) — and call ws.unmerge_cells(rng.coord) for each. Copying the collection first matters because unmerging changes it while you iterate.

Does read_only mode tell me which cells are merged? No. A read-only worksheet has no merged_cells attribute, so you cannot find merged ranges while streaming. Load the workbook normally for this job.

Should I fill merged values with pandas instead? If the merges run straight down one column, reading with pandas and calling ffill on that column is quicker and gives the same result. Use openpyxl when merges run across columns, when you need to save the workbook back, or when blanks that are not merges must stay blank.

Related

• Up one level: Using openpyxl for Excel File Manipulation — the openpyxl object model.
• Forward Fill Blank Cells in Grouped Excel Reports — the same shape without merges.
• Merge Cells and Centre a Report Title with openpyxl — when merging is the right call.
• Read Excel with Multi-Row Headers in pandas — merged header rows on the pandas side.