Guide

Formatting And Charting Excel Reports With PythonDeep dive

Merge Cells and Centre a Report Title with openpyxl

Q: How do I remove a merge?

Call unmerge_cells with the same range reference. The anchor keeps the value and the other cells become ordinary empty cells, so you may want to fill them afterwards.

Add a merged, centred title banner to a generated report — merge_cells, styling the anchor cell, the MergedCell read-only trap, and why centre-across-selection is often better.

Every generated report wants a title: the report name, the period, maybe an as-at timestamp, spanning the width of the data and centred. In Excel that means merging a range and centring the text — two calls in openpyxl, plus a handful of behaviours that are surprising the first time. Only one cell in a merged range is real, borders do not span the merge on their own, and merging inside a data region breaks sorting for everyone downstream. This guide covers the banner properly, and the alternative that avoids merging altogether. It extends Styling Excel Cells with openpyxl.

Prerequisites

Bash

pip install openpyxl pandas

A report to add a banner to, written with the data starting two rows down:

Python

import pandas as pd

pd.DataFrame({
    "region": ["North", "South", "West"],
    "units": [412, 388, 265],
    "revenue": [5150.00, 4268.50, 3511.25],
    "target": [4500.00, 4500.00, 3000.00],
}).to_excel("report.xlsx", index=False, startrow=2)

Step 1 — Merge and centre

Write the value to the top-left cell, then merge:

Python

from openpyxl import load_workbook
from openpyxl.styles import Font, Alignment, PatternFill

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

ws["A1"] = "Regional Revenue Report"
ws.merge_cells("A1:D1")

anchor = ws["A1"]
anchor.font = Font(size=15, bold=True, color="4338CA")
anchor.alignment = Alignment(horizontal="center", vertical="center")
anchor.fill = PatternFill("solid", start_color="EBEBFD", end_color="EBEBFD")
ws.row_dimensions[1].height = 30

wb.save("report_titled.xlsx")

Write the value before or after merging, but always to the anchor. Merging discards the values in every cell except the top-left, so a value written to B1 and then merged is simply gone.

The read-only behaviour catches everyone once:

Python

ws.merge_cells("A1:D1")
ws["B1"] = "anything"
# AttributeError: 'MergedCell' object attribute 'value' is read-only

The cells inside a merge are MergedCell placeholders, not real cells. Only the anchor is writable, which also means a loop over the range needs guarding:

Python

from openpyxl.cell.cell import MergedCell

for row in ws["A1:D1"]:
    for cell in row:
        if isinstance(cell, MergedCell):
            continue                       # skip the placeholders
        cell.value = "written safely"

Step 2 — Border the whole range

A border set on the anchor draws around that one cell's original bounds — a box a quarter of the way across your banner. Borders have to be applied to every cell of the merge, placeholders included:

Python

from openpyxl.styles import Border, Side
from openpyxl.utils import range_boundaries

def style_merged_range(ws, ref, fill=None, border=None):
    """Apply a fill and border across every cell of a merged range."""
    min_col, min_row, max_col, max_row = range_boundaries(ref)
    for row in range(min_row, max_row + 1):
        for col in range(min_col, max_col + 1):
            cell = ws.cell(row=row, column=col)
            if fill is not None:
                cell.fill = fill
            if border is not None:
                cell.border = border

thin = Side(style="thin", color="CDD5E6")
style_merged_range(
    ws, "A1:D1",
    fill=PatternFill("solid", start_color="EBEBFD", end_color="EBEBFD"),
    border=Border(top=thin, bottom=thin, left=thin, right=thin),
)

Assigning a fill or border to a MergedCell works — it is only value that is read-only. That distinction is the thing to remember: styles apply to the placeholders, values do not.

Titles come in a predictable shape: a heading, a subtitle carrying the period and generation time, then a gap before the data. Wrap it once:

Python

from datetime import datetime, timezone
from openpyxl import load_workbook
from openpyxl.styles import Font, Alignment, PatternFill, Border, Side
from openpyxl.utils import get_column_letter, range_boundaries

def add_banner(ws, title, subtitle=None, columns=4, height=30):
    """Add a merged, centred title (and optional subtitle) above the data."""
    last = get_column_letter(columns)
    thin = Side(style="thin", color="CDD5E6")

    ws["A1"] = title
    ws.merge_cells(f"A1:{last}1")
    ws["A1"].font = Font(size=15, bold=True, color="4338CA")
    ws["A1"].alignment = Alignment(horizontal="center", vertical="center")
    ws.row_dimensions[1].height = height
    style_merged_range(
        ws, f"A1:{last}1",
        fill=PatternFill("solid", start_color="EBEBFD", end_color="EBEBFD"),
        border=Border(bottom=thin),
    )

    if subtitle:
        ws["A2"] = subtitle
        ws.merge_cells(f"A2:{last}2")
        ws["A2"].font = Font(size=10, italic=True, color="5B6780")
        ws["A2"].alignment = Alignment(horizontal="center", vertical="center")
        ws.row_dimensions[2].height = 18

    return ws

wb = load_workbook("report.xlsx")
ws = wb.active
stamp = datetime.now(timezone.utc).strftime("%Y-%m-%d %H:%M UTC")
add_banner(ws, "Regional Revenue Report",
           f"August 2026 · generated {stamp}", columns=4)
ws.freeze_panes = "A4"
wb.save("report_titled.xlsx")

freeze_panes = "A4" keeps the banner and the column headers visible as the reader scrolls — a small thing that makes a long report noticeably easier to use. Putting the generation time in the subtitle is worth the line too: it is the first question anybody asks about a report they were forwarded.

Step 4 — Consider centre-across-selection instead

Merging is right for a banner above the data. Inside a data region it causes real problems, and there is an alternative that looks identical and causes none.

Python

from openpyxl.styles import Alignment

ws["A1"] = "Regional Revenue Report"
# No merge. The text is drawn centred across A1:D1.
for row in ws["A1:D1"]:
    for cell in row:
        cell.alignment = Alignment(horizontal="centerContinuous")

centerContinuous centres the anchor's text across every adjacent cell that also carries the setting, stopping at the first cell that does not. The cells stay independent, so a reader can sort, filter and pivot the sheet normally — and pandas reads it without the blank-run problem described in handling merged cells when reading Excel.

Use merging for a title above the data, where nothing will ever sort it. Use centerContinuous anywhere a merge would sit inside or beside a data region.

Step 5 — Unmerge

Removing a merge takes the same reference:

Python

from openpyxl import load_workbook

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

ws.unmerge_cells("A1:D1")
print([str(r) for r in ws.merged_cells.ranges])

The anchor keeps its value and the former placeholders become ordinary empty cells. When you are flattening a whole sheet rather than one range, snapshot the ranges before iterating — unmerging mutates the collection you would otherwise be looping over:

Python

for ref in [str(r) for r in ws.merged_cells.ranges]:
    ws.unmerge_cells(ref)

Common pitfalls and fixes

Symptom	Cause	Fix
`AttributeError: 'MergedCell' ... read-only`	Writing to a non-anchor cell	Write to the top-left cell only.
Border stops a quarter of the way across	Border set on the anchor only	Apply it to every cell in the range.
Title value disappeared after merging	Value was not in the anchor	Put it in the top-left cell.
Sorting stopped working	Merge inside the data region	Use `centerContinuous` instead.
pandas reads blanks after the title	Merged range read normally	Skip the banner rows with `skiprows`.
`RuntimeError` while unmerging in a loop	Iterating the live ranges	Snapshot the refs into a list first.
Title cut off vertically	Row height still the default	Set `row_dimensions[1].height`.
Banner scrolls out of view	No frozen panes	`ws.freeze_panes = "A4"`.

Performance and scale notes

Merging is cheap — a merged range is one entry in the sheet's range collection, regardless of how many cells it spans. The costs are elsewhere.

Styling the range is per-cell. style_merged_range touches every cell, so a banner across ten columns costs ten style assignments. That is nothing for a title; it would matter if you merged thousands of ranges, which is itself a sign the design is wrong.

Merges slow Excel's own operations. A sheet with many merged ranges is noticeably slower to scroll, sort and recalculate, because Excel checks the merge collection for every affected cell. A report with a handful of banner merges is fine; one with a merge per group heading across ten thousand rows is not — use centerContinuous, or repeat the group value on every row and let a pivot do the grouping.

Merges cannot be created in write_only mode. Streaming writes emit rows as they go and never hold the sheet, so a large report needing both streaming and a banner has to be written in two passes: stream the data with the approach in writing large DataFrames with write-only mode, then re-open it normally to add the banner. Since the banner touches only the first two rows, that second pass is the cheapest part of the job.

If you are creating a report from scratch rather than modifying one, the xlsxwriter equivalent is a single call and slightly faster:

Python

import pandas as pd

with pd.ExcelWriter("report.xlsx", engine="xlsxwriter") as writer:
    df.to_excel(writer, sheet_name="Report", index=False, startrow=2)
    book, sheet = writer.book, writer.sheets["Report"]

    title = book.add_format({
        "bold": True, "font_size": 15, "font_color": "#4338CA",
        "bg_color": "#EBEBFD", "align": "center", "valign": "vcenter",
        "bottom": 1, "border_color": "#CDD5E6",
    })
    sheet.merge_range("A1:D1", "Regional Revenue Report", title)
    sheet.set_row(0, 30)
    sheet.freeze_panes(3, 0)

merge_range writes the value, merges and applies the format across the whole range in one call — including the border, which is the part openpyxl makes you do by hand.

Conclusion

A merged title banner is merge_cells plus an alignment on the anchor, with two behaviours to remember: only the top-left cell accepts a value, and borders must be applied to every cell of the range or they stop partway across. Set the row height and freeze the panes so the banner stays visible. And keep merges above the data, never inside it — where you want the look without the consequences, centerContinuous centres text across columns while leaving every cell independent, so sorting, filtering and pandas all keep working.

Frequently asked questions

Why does writing to a merged cell raise AttributeError? Only the top-left cell of a merged range is a real cell; the rest are MergedCell objects that are read-only. Write to the anchor cell — the top-left one — and the value displays across the whole range.

How do I style the whole merged range? Set the value and font on the anchor cell, but apply borders and fills to every cell in the range. A border set only on the anchor draws around that one cell's original bounds, leaving the rest of the merge unbordered.

Should I merge cells at all? For a title banner, yes — it is what readers expect. For anything inside a data region, no: merged cells break sorting, filtering and pivot tables, and they read back into pandas as one value plus a run of blanks.

What is centre across selection? An alignment option that visually centres text over several columns without actually merging them. It looks the same to a reader and leaves the cells independent, so sorting and filtering keep working.

How do I remove a merge? Call unmerge_cells with the same range reference. The anchor keeps the value and the other cells become ordinary empty cells, so you may want to fill them afterwards.

Up to the parent: Styling Excel Cells with openpyxl — fonts, fills and borders in depth.
Apply a Reusable Style Theme Across an Excel Report — where the banner styles belong.
Handle Merged Cells When Reading Excel with pandas — the reading-side consequences.
Freeze the Header Row in Excel with openpyxl — keeping the banner in view.
Set Column Width and Row Height in openpyxl — sizing the banner row.