Python Excel AutomationPractical guides for developers
How-to
Getting Started With Python Excel AutomationDeep dive

Merge Repeated Group Labels into Single Cells with openpyxl

Turn a report where the region name repeats on every row into one merged, vertically centred label per group: find runs of equal values, merge them with openpyxl, handle nested groups so an inner label never spans two outer groups, keep borders tidy, and know when to hide repeats with conditional formatting instead so the sheet stays sortable.
openpyxlReport layouts & templatesCell styling & layout

A grouped report reads best when each group's label appears once: "North" spanning its four product rows, then "South" spanning its three. A DataFrame written with to_excel repeats the label on every row, which is right for data and noisy for a printed report. Excel users fix this by hand with Merge & Center; a script can do the same in a few lines of openpyxl by finding each run of identical values and merging it. This page shows the run detection, nested groups, the styling that makes merged labels look deliberate, what merging does to the values underneath — and the alternative that gives the same look without the drawbacks of merged cells. It belongs to Using openpyxl for Excel File Manipulation.

Prerequisites

Bash
pip install openpyxl pandas

sales_by_region.xlsx is a plain export with one row per region, category and product. Rows are already sorted by region and category, which merging depends on: a run is a block of consecutive equal values, so sort before you merge.

Step 1 — Find the runs of equal values

Python
from openpyxl import load_workbook

wb = load_workbook("sales_by_region.xlsx")
ws = wb["Sales"]

def runs_in_column(ws, col, first_row=2, last_row=None):
    """Return (start_row, end_row, value) for each block of consecutive equal values."""
    last_row = last_row or ws.max_row
    out, start = [], first_row
    for r in range(first_row + 1, last_row + 2):
        current = ws.cell(r, col).value if r <= last_row else object()
        if current != ws.cell(start, col).value:
            out.append((start, r - 1, ws.cell(start, col).value))
            start = r
    return out

print(runs_in_column(ws, 1))

The function walks down the column and closes a run whenever the value changes; the object() sentinel after the last row closes the final run. For the Region column it returns (2, 5, 'North'), (6, 8, 'South') and (9, 9, 'East') — the last one is a single row, which needs no merge.

Step 2 — Merge each run and centre the label

Python
from openpyxl.styles import Alignment

def merge_runs(ws, col, runs):
    for start, end, value in runs:
        if end > start:
            ws.merge_cells(start_row=start, start_column=col, end_row=end, end_column=col)
        ws.cell(start, col).alignment = Alignment(vertical="center", horizontal="left", indent=1)

merge_runs(ws, 1, runs_in_column(ws, 1))
print(ws.merged_cells.ranges)

The merged range takes its value and style from its top-left cell, so the alignment is set there. vertical="center" places the label in the middle of the group, which is what makes a merged label look intentional; without it, Excel draws the text at the bottom of the block. The single-row East group gets the same alignment without a merge, so every label lines up.

The region repeated on every row becomes one label per group On the left the Region column repeats North four times and South three times. On the right North is one merged cell spanning four rows and South one merged cell spanning three rows, each label vertically centred. A B C 1 Region Product Units 2 North Drills 120 3 North Saws 80 4 North Hoses 210 5 North Rakes 95 6 South Drills 90 7 South Hoses 160 A B C 1 Region Product Units 2 North Drills 120 3 Saws 80 4 Hoses 210 5 Rakes 95 6 South Drills 90 7 Hoses 160 before after merge_cells

Step 3 — Merge nested groups without crossing the outer ones

Category sits inside Region. Running the same function on column B would merge "Hardware" across the end of North and the start of South if both happened to be Hardware — here row 5 is North · Garden and row 6 is South · Hardware, but in other data the values could line up. Detect inner runs within each outer run instead:

Python
wb = load_workbook("sales_by_region.xlsx")
ws = wb["Sales"]
region_runs = runs_in_column(ws, 1)
category_runs = []
for start, end, _ in region_runs:
    category_runs += runs_in_column(ws, 2, first_row=start, last_row=end)
merge_runs(ws, 1, region_runs)
merge_runs(ws, 2, category_runs)
print([f"{s}-{e} {v}" for s, e, v in category_runs])

Each Region block is scanned separately for Category runs, so an inner label can never span two regions — the same rule a pivot table follows. The approach extends to any depth: scan level three inside each level-two run, and so on.

Region and category merged as two nested levels The finished sheet. North spans rows 2 to 5 with Hardware merged over rows 2 and 3 and Garden over rows 4 and 5. South spans rows 6 to 8 with Hardware on row 6 and Garden merged over rows 7 and 8. East is a single row. A B C D E 1 Region Category Product Units Revenue 2 North Hardware Drills 120 9,600 3 Saws 80 4,000 4 Garden Hoses 210 3,150 5 Rakes 95 1,425 6 South Hardware Drills 90 7,200 7 Garden Hoses 160 2,400 8 Rakes 70 1,050 9 East Garden Hoses 130 1,950

Step 4 — Draw borders that follow the groups

Gridlines inside a merged block disappear, but borders on the neighbouring columns still cut across each group row by row. A heavier line under the last row of every outer group makes the structure obvious:

Python
from openpyxl.styles import Border, Side, Font

thin, heavy = Side(style="thin", color="CDD5E6"), Side(style="medium", color="5B5CF0")
for start, end, _ in region_runs:
    for r in range(start, end + 1):
        for c in range(1, ws.max_column + 1):
            cell = ws.cell(r, c)
            if type(cell).__name__ == "MergedCell":
                continue
            cell.border = Border(bottom=heavy if r == end else thin)
for c in ws[1]:
    c.font = Font(bold=True)
ws.column_dimensions["A"].width = 12
ws.column_dimensions["B"].width = 13
wb.save("sales_merged.xlsx")
print("saved", ws.merged_cells.ranges)

The loop skips MergedCell objects, the read-only placeholders that fill the covered positions of a merge; their appearance comes from the top-left cell, and assigning to them raises an error. To give a merged block an outline of its own, style its top-left cell's border and the cells around it, or see Add Borders to a Range with openpyxl.

Step 5 — What merging does to the values underneath

Merging is not cosmetic. openpyxl clears every covered cell when it creates the merge, exactly as Excel does after its "only the upper-left value is kept" warning:

Python
check = load_workbook("sales_merged.xlsx")["Sales"]
print(repr(check["A3"].value), type(check["A3"]).__name__)
import pandas as pd
print(pd.read_excel("sales_merged.xlsx").head(4).to_string(index=False))

A3 is now a MergedCell with value None, and reading the file back gives NaN in the Region column for every row except the first of each group. Anyone who sorts, filters or builds a pivot from the merged sheet gets broken results — Excel refuses to sort a range containing merged cells of different sizes, and a filter on "North" returns one row. Keep the unmerged data in a separate sheet or file, and treat the merged sheet as presentation only. When a merged sheet arrives as input, undo it with Unmerge Cells and Fill Values with openpyxl.

Step 6 — The filter-friendly alternative: hide repeats instead

To keep every value in place yet show each label once, leave the cells unmerged and hide repeated labels with a conditional format whose font colour matches the background:

Python
from openpyxl.formatting.rule import FormulaRule

wb = load_workbook("sales_by_region.xlsx")
ws = wb["Sales"]
hide = Font(color="FFFFFF")
last = ws.max_row
ws.conditional_formatting.add(f"A3:A{last}", FormulaRule(formula=["A3=A2"], font=hide))
ws.conditional_formatting.add(f"B3:B{last}", FormulaRule(formula=["AND(A3=A2,B3=B2)"], font=hide))
wb.save("sales_hidden_repeats.xlsx")
print([str(cf.sqref) for cf in ws.conditional_formatting])

The rule for column A hides a label when it equals the one above it; the rule for column B also requires the region to be the same, which gives the nested behaviour of Step 3. Because the rules are formulas, the display updates when someone sorts or filters: the first visible row of each group always shows its label. The values are all still there for pivots and lookups. Conditional formats are explained in Applying Conditional Formatting with openpyxl.

Merge the labels or hide the repeats Merging gives a centred label per group but clears the covered values and breaks sorting and filtering. Hiding repeats with a conditional format keeps every value, survives sorting and filtering, but leaves the label at the top of each group rather than centred. merge_cells One centred label per group Classic printed-report look Covered cells lose their values Sorting, filtering and pivots break Conditional format Every value stays in its cell Sort and filter still work Labels reappear after filtering Label sits at the top, not centred

Step 7 — Merge while writing with xlsxwriter

When the report is generated from scratch rather than edited, xlsxwriter can write each merged label in the same pass as the data with merge_range, which takes the corner coordinates, the value and a format:

Python
import xlsxwriter

df = pd.read_excel("sales_by_region.xlsx")
book = xlsxwriter.Workbook("sales_xlsxwriter.xlsx")
sheet = book.add_worksheet("Sales")
label = book.add_format({"valign": "vcenter", "indent": 1, "border": 1})
sheet.write_row(0, 0, df.columns, book.add_format({"bold": True}))
for r, row in enumerate(df.itertuples(index=False), start=1):
    sheet.write_row(r, 1, row[1:])
for region, idx in df.groupby("Region", sort=False).groups.items():
    first, last = idx.min() + 1, idx.max() + 1
    if last > first:
        sheet.merge_range(first, 0, last, 0, region, label)
    else:
        sheet.write(first, 0, region, label)
book.close()
print("written", len(df), "rows")

groupby(..., sort=False).groups maps each region to the DataFrame index positions of its rows, which become the first and last worksheet rows of the merge (plus one for the header). xlsxwriter refuses to merge a single cell, hence the write branch for one-row groups, and it raises OverlappingRange if two merges overlap — a useful guard against the crossing-groups mistake from Step 3. The data columns are written first and the label column afterwards, because writing a value into a cell that belongs to a merged range would overwrite part of it.

Common pitfalls

  • Unsorted data. Runs are consecutive equal values; an unsorted column produces many small merges and repeated labels.
  • Writing to covered cells. Assigning a value to a MergedCell raises AttributeError: 'MergedCell' object attribute 'value' is read-only; write to the top-left cell.
  • Inner groups crossing outer ones. Detect inner runs within each outer run, never over the whole column.
  • Merging, then writing more data. If a later step appends rows or rewrites the sheet with pandas, merges are lost or misplaced; merge as the last step before saving.
  • Equal-looking values that differ. "North" and "North " start separate runs; strip text first.

Conclusion

Find runs of consecutive equal values, merge each with merge_cells, and centre the label vertically in its top-left cell; for nested groups, look for inner runs within each outer run. Finish with borders under each group, and remember that merging clears the covered values — keep the raw data elsewhere, or use a conditional format that hides repeated labels when the sheet must stay sortable and filterable.

Frequently asked questions

How do I merge cells with the same value in a column using openpyxl? Sort the data, walk down the column collecting runs of consecutive equal values, and call ws.merge_cells(start_row=..., start_column=col, end_row=..., end_column=col) for each run longer than one row. Set the alignment on the top-left cell of each run.

How do I vertically centre text in a merged cell? Set the alignment on the merged range's top-left cell: cell.alignment = Alignment(vertical="center"). The merged block takes its formatting from that cell.

Does merging cells delete data in openpyxl? Yes. merge_cells clears every cell in the range except the top-left one, which becomes the merged cell's value. Reading the file back gives empty values for the covered rows.

How can I show a group label once without merging cells? Leave every label in place and add a conditional formatting rule such as =A3=A2 on the label column that sets the font colour to the background colour. Repeats are hidden, and sorting and filtering keep working.

More on openpyxl

More on openpyxl →