Fixing Python openpyxl That Merges Cells But Loses Data on Re-open

You run your script, merge a block of cells, save the workbook, and open the file in Excel. The cells are merged, the formatting looks right — but the value is gone. This is one of openpyxl's most quietly frustrating behaviours, and it catches almost everyone the first time they use merge_cells().

The fix is a single, specific rule: only the top-left cell of a merged region holds data. Once you internalize that, the bug becomes obvious and the solution is straightforward.

What you'll learn

• Why openpyxl silently drops data when you merge cells in the wrong order
• How to write a value to a merged range so it survives save and re-open
• How to safely read data back from a merged region
• How to unmerge cells while keeping the original value
• The most common mistakes developers make with merged ranges

Prerequisites

You need openpyxl installed (pip install openpyxl). The examples below use Python 3.8+ and openpyxl 3.x. No other dependencies are required. Basic familiarity with opening and saving workbooks is assumed.

What actually happens when you merge cells in openpyxl

In Excel's data model, merging cells does not create a single super-cell. It creates a merged region — a rectangular range where one anchor cell (always the top-left) owns the value, and every other cell in the range is treated as empty. Excel renders the anchor cell's value across the whole visual block.

openpyxl mirrors this model. When you call ws.merge_cells('B2:D4'), the library registers a merged range and nulls out every cell in that rectangle except B2. If you have already written a value to, say, C3, that value gets silently discarded during the merge operation. When Excel opens the file, it sees an empty anchor cell and displays nothing.

Why the data disappears after merging

The most common trigger is this sequence:

1. Write a value to a cell inside the intended range (but not the top-left corner).
2. Call merge_cells() on the range that includes that cell.
3. Save. The value is gone.

Here is the broken pattern:

import openpyxl

wb = openpyxl.Workbook()
ws = wb.active

# BUG: writing to C2 first, then merging B2:D2
ws['C2'] = 'Quarterly Report'
ws.merge_cells('B2:D2')   # this silently clears C2 and D2

wb.save('report.xlsx')

After the merge, C2 and D2 are erased. B2 — the anchor — was never assigned a value, so the file saves with an empty merged block.

A second trigger is the reverse: merging first, then writing to a non-anchor cell inside the range:

ws.merge_cells('B2:D2')
ws['C2'] = 'Quarterly Report'  # BUG: C2 is not the anchor; value is ignored

openpyxl does not raise an error either way. The assignment appears to work at runtime, but the value never makes it into the saved file.

The top-left cell rule — the only cell that counts

Before you write a single line of merge code, commit this rule to memory: the value, font, fill, alignment, and border of a merged region must be set on the top-left cell of that range. Every other cell in the range is a phantom — changes to them are dropped.

Given a range like 'B2:D4', the top-left cell is always B2. You can derive this programmatically if your range is dynamic:

from openpyxl.utils import range_boundaries

merge_range = 'B2:D4'
min_col, min_row, max_col, max_row = range_boundaries(merge_range)
# min_col=2, min_row=2 → top-left cell is ws.cell(row=2, column=2)

This matters whenever you are constructing ranges dynamically rather than hardcoding them as strings.

Writing a value to a merged region correctly

The fix is simple: merge first, then write to the top-left anchor cell. Never the other way around.

import openpyxl
from openpyxl.styles import Alignment

wb = openpyxl.Workbook()
ws = wb.active

# Step 1: merge the range
ws.merge_cells('B2:D2')

# Step 2: write to the top-left cell only
ws['B2'] = 'Quarterly Report'

# Step 3: apply alignment so the text centres across the merged region
ws['B2'].alignment = Alignment(horizontal='center', vertical='center')

wb.save('report.xlsx')

Open report.xlsx in Excel and the text will be centred across columns B, C, and D in row 2. No data loss.

If you are working with a dynamic range string, extract the anchor programmatically:

from openpyxl.utils import range_boundaries
from openpyxl.styles import Alignment

def write_merged_cell(ws, merge_range, value, horizontal='center'):
    ws.merge_cells(merge_range)
    min_col, min_row, _, _ = range_boundaries(merge_range)
    anchor = ws.cell(row=min_row, column=min_col)
    anchor.value = value
    anchor.alignment = Alignment(horizontal=horizontal, vertical='center')

Wrapping this in a helper function prevents the wrong-cell mistake from creeping back into a large script.

Reading data back from a merged range

When you load a workbook and iterate over a range that contains merged cells, openpyxl returns None for every cell except the anchor. This surprises developers who expect the value to be repeated across the visual span.

import openpyxl

wb = openpyxl.load_workbook('report.xlsx')
ws = wb.active

for row in ws['B2:D2']:
    for cell in row:
        print(cell.coordinate, cell.value)

# Output:
# B2 Quarterly Report
# C2 None
# D2 None

If your script needs to know which value governs a given cell, you can inspect the workbook's merged cell registry:

def get_effective_value(ws, row, col):
    """Return the value for a cell, accounting for merged ranges."""
    from openpyxl.utils import get_column_letter
    target = ws.cell(row=row, column=col)
    if target.value is not None:
        return target.value

    # Check if this cell falls inside a merged range
    coord = f"{get_column_letter(col)}{row}"
    for merged_range in ws.merged_cells.ranges:
        if coord in merged_range:
            # Return the anchor's value
            anchor = ws.cell(row=merged_range.min_row,
                             column=merged_range.min_col)
            return anchor.value
    return None

This is particularly useful when you are processing spreadsheets produced by others, where merged header rows are common. For a related read on hidden-row surprises, see how openpyxl skips hidden rows when reading cell data — the same kind of silent omission applies there.

Unmerging cells without losing the value

Calling ws.unmerge_cells() removes the merge but does not redistribute the anchor's value to the previously-merged cells. If you need each cell to hold the original value after unmerging, you have to copy it yourself before or after the unmerge.

import openpyxl
from openpyxl.utils import range_boundaries

wb = openpyxl.load_workbook('report.xlsx')
ws = wb.active

merge_range = 'B2:D2'
min_col, min_row, max_col, max_row = range_boundaries(merge_range)

# Read the anchor value before unmerging
anchor_value = ws.cell(row=min_row, column=min_col).value

# Unmerge the range
ws.unmerge_cells(merge_range)

# Fill every cell in the former range with the anchor value
for row in range(min_row, max_row + 1):
    for col in range(min_col, max_col + 1):
        ws.cell(row=row, column=col).value = anchor_value

wb.save('report_unmerged.xlsx')

This pattern is common when you receive a formatted report and need to unpivot it for further processing in Pandas or a database.

Merging across multiple rows and columns

Everything above applies equally to two-dimensional merges. A range like 'B2:D4' spans three columns and three rows; the anchor is still B2.

import openpyxl
from openpyxl.styles import Alignment, Font

wb = openpyxl.Workbook()
ws = wb.active

ws.merge_cells('B2:D4')
anchor = ws['B2']
anchor.value = 'Annual Summary'
anchor.font = Font(bold=True, size=14)
anchor.alignment = Alignment(horizontal='center',
                              vertical='center',
                              wrap_text=True)

# Set row height and column width so the merged block looks right
for col in ['B', 'C', 'D']:
    ws.column_dimensions[col].width = 15
for row_num in range(2, 5):
    ws.row_dimensions[row_num].height = 30

wb.save('summary.xlsx')

A common follow-on issue is that styles applied to the non-anchor cells inside the range are lost — exactly as with values. If you need a border that visually wraps the entire merged block, set it on the anchor cell with a border style that applies to all four sides. If you have hit that particular wall, the guide on openpyxl losing cell styles when copying rows between workbooks covers the underlying style model in detail.

Common pitfalls when working with merged cells

Merging a range that already contains a merged sub-range

If you try to merge a range that overlaps an existing merged region, openpyxl raises an InvalidFileException or silently produces a corrupt file depending on the version. Always check ws.merged_cells.ranges and unmerge any overlapping range first.

def safe_merge(ws, merge_range):
    """Unmerge any overlapping ranges before applying a new merge."""
    from openpyxl.utils import range_boundaries

    min_col, min_row, max_col, max_row = range_boundaries(merge_range)
    to_remove = []
    for existing in ws.merged_cells.ranges:
        if (existing.min_col <= max_col and existing.max_col >= min_col and
                existing.min_row <= max_row and existing.max_row >= min_row):
            to_remove.append(str(existing))
    for r in to_remove:
        ws.unmerge_cells(r)
    ws.merge_cells(merge_range)

Applying number formatting to merged cells

Number format must be applied to the anchor cell. Setting it on any other cell in the range has no effect. If you are running into openpyxl number formatting oddities more broadly, the deep-dive on openpyxl failing to apply number formatting is worth reading alongside this one.

Charts and merged headers

If a column header is a merged cell and you are building a chart from the surrounding data, openpyxl may not resolve the header label correctly. Prefer unmerged header rows for chart source ranges and only merge for visual decoration outside the data table. Related chart data issues are covered in the article on openpyxl chart data disappearing after saving.

Using iter_rows() on a sheet with many merged cells

Iterating a large sheet that has many merged regions is slower than iterating a clean sheet, because openpyxl must check each cell coordinate against the merged-ranges registry. If performance matters, collect the merged ranges into a lookup structure before your loop rather than letting the library check on every cell access.

Forgetting data_only=True on reload

When you reload a workbook to verify your merge, use openpyxl.load_workbook('file.xlsx', data_only=True) if the anchor cell contains a formula. Without that flag you will read the formula string, not the computed value — which can make it look like the data is missing when it is actually present.

Wrapping up

Merged cells in openpyxl follow one rule that, once learned, makes the entire API predictable: the top-left anchor cell owns everything. Violate that rule and your data disappears silently.

Here are five concrete steps to take right now:

1. Audit your existing merge code — check that every merge_cells() call is followed by a write to the top-left cell, not to a cell inside the range.
2. Extract a helper function like write_merged_cell(ws, range, value) so the correct order is enforced by convention rather than memory.
3. Use range_boundaries() for dynamic ranges instead of parsing the range string manually.
4. Check for overlapping merges with the safe_merge() pattern before writing to any sheet that is modified iteratively.
5. Reload with data_only=True when verifying saved output, so formula cells do not mislead you during debugging.