Copy Cell Styles from One Cell to Another with openpyxl

A generated report usually inherits its look from somewhere: a template row you want new data to match, a header cell styled by a designer, the format of last month's sheet. openpyxl has no "Format Painter", but every piece of a cell's appearance is an attribute you can copy — font, fill, border, alignment, number format and protection. This guide, part of Using openpyxl for Excel File Manipulation, shows the safe way to copy them, the shortcut that works only inside one workbook, and how to extend a template's formatting to rows that do not exist yet.

Prerequisites

pip install openpyxl

The examples start from a small styled source sheet — a header cell and a formatted data row, the kind of thing a template provides:

from copy import copy

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

wb = Workbook()
ws = wb.active
ws.title = "Template"

ws["A1"] = "Region"
ws["A1"].font = Font(bold=True, color="FFFFFF")
ws["A1"].fill = PatternFill("solid", fgColor="4338CA")
ws["A1"].alignment = Alignment(horizontal="center")
ws["A1"].border = Border(bottom=Side(style="medium", color="1E1B4B"))

ws["B2"] = 0.125
ws["B2"].number_format = "0.0%"
ws["B2"].font = Font(color="0B6157")
ws["B2"].border = Border(bottom=Side(style="thin", color="CDD5E6"))
wb.save("template.xlsx")

Step 1 — Copy a style attribute by attribute

The dependable pattern is one helper that copies each of the six style attributes with copy(). It works for any two cells, in the same sheet, another sheet, or another workbook:

STYLE_ATTRS = ("font", "fill", "border", "alignment", "number_format", "protection")

def copy_style(source, target) -> None:
    for attr in STYLE_ATTRS:
        setattr(target, attr, copy(getattr(source, attr)))

copy_style(ws["A1"], ws["B1"])
ws["B1"] = "Share"
print(ws["B1"].font.b, ws["B1"].fill.fgColor.rgb)   # True 004338CA

number_format is a plain string, so copying it is trivial; the others are style objects that openpyxl treats as immutable and shares between cells. copy() gives the target its own reference, which is what makes the helper safe across workbooks. To copy only part of a style — say the border but not the fill — pass a shorter tuple of attributes, or modify a copy: target.font = copy(source.font) followed by target.font = Font(**{...}) is the usual way to tweak one property while keeping the rest.

Step 2 — The _style shortcut, and where it breaks

Every cell also has a private _style attribute: a compact record of indexes into the workbook's shared tables of fonts, fills and borders. Copying it moves the whole style in one assignment, and inside one workbook it is safe and fast:

ws["C1"]._style = copy(ws["A1"]._style)
print(ws["C1"].font.b)    # True

Across workbooks the indexes point into a different set of tables, and the result depends on what the target workbook happens to contain at those positions:

other = Workbook()
other.active["A1"].font = Font(italic=True)       # the target has its own style table
other.active["C3"]._style = copy(ws["A1"]._style)
copied = other.active["C3"].font
print(copied.b, copied.i)    # False True  <- not the source font at all

No error, and a wrong result: index 1 in the source workbook's font table is the bold white header font, but index 1 in the target's table is the italic font that was added first. The copied cell comes out italic and not bold. With a target whose tables are shorter than the index, the same line fails instead with IndexError: list index out of range the moment the style is read. Both failures come from the same cause, and the silent one is worse, because a report with the wrong fonts looks plausible until someone compares it with the template. Use _style only when source and target are in the same workbook; use the attribute helper everywhere else.

Step 3 — Carry a template row down onto new data

The most common job: a template has one formatted data row, and the script writes fifty rows of data that should all look like it. Copy the template row's style to every row you write, plus the row height:

def extend_row_format(ws, template_row: int, first: int, last: int) -> None:
    height = ws.row_dimensions[template_row].height
    for row in range(first, last + 1):
        for col in range(1, ws.max_column + 1):
            copy_style(ws.cell(template_row, col), ws.cell(row, col))
        if height:
            ws.row_dimensions[row].height = height

shares = [0.125, 0.31, 0.07, 0.495]
for i, share in enumerate(shares, start=2):
    ws.cell(i, 1, f"Region {i - 1}")
    ws.cell(i, 2, share)
extend_row_format(ws, template_row=2, first=3, last=len(shares) + 1)
print([ws.cell(r, 2).number_format for r in range(2, 6)])  # ['0.0%', '0.0%', '0.0%', '0.0%']

Write the values first and the styles second, or the other way round — it does not matter, because value and style are independent. What matters is copying from the template row every time rather than from the row above, so a mistake in one row cannot cascade down the sheet.

Step 4 — Copy column widths and row heights too

A copied cell style does not include the width of its column or the height of its row; those live in column_dimensions and row_dimensions on the sheet. When you clone a layout onto another sheet, copy them explicitly:

def copy_dimensions(src_ws, dst_ws) -> None:
    for key, dim in src_ws.column_dimensions.items():
        dst_ws.column_dimensions[key].width = dim.width
        dst_ws.column_dimensions[key].hidden = dim.hidden
    for idx, dim in src_ws.row_dimensions.items():
        if dim.height:
            dst_ws.row_dimensions[idx].height = dim.height

ws.column_dimensions["A"].width = 22
report = wb.create_sheet("March")
for row in ws.iter_rows():
    for cell in row:
        target = report.cell(cell.row, cell.column, cell.value)
        copy_style(cell, target)
copy_dimensions(ws, report)
print(report.column_dimensions["A"].width)   # 22.0

If you only need an identical copy of a sheet inside the same workbook, wb.copy_worksheet(ws) does all of this in one call — values, styles, dimensions and merged cells — and is covered in Copy a Sheet Between Excel Workbooks with Python, along with the cross-workbook case it does not handle.

Step 5 — Use a NamedStyle for many cells

When the same look goes on hundreds or thousands of cells, define it once as a NamedStyle, register it, and assign it by name. Every cell then points at one shared record:

from openpyxl.styles import NamedStyle

percent_cell = NamedStyle(name="percent_cell")
percent_cell.number_format = "0.0%"
percent_cell.font = Font(color="0B6157")
percent_cell.border = Border(bottom=Side(style="thin", color="CDD5E6"))
wb.add_named_style(percent_cell)

for row in range(2, 1002):
    report.cell(row, 3).style = "percent_cell"

wb.save("styled_copy.xlsx")
print(load_workbook("styled_copy.xlsx")["March"]["C500"].style)   # percent_cell

Named styles also show up in Excel's Cell Styles gallery, so whoever edits the report later can apply the same look by hand. A whole style system built this way is described in Apply a Reusable Style Theme Across an Excel Report.

Common pitfalls

• Copying _style between workbooks. It raises IndexError or applies a wrong style. Copy attributes instead.
• Forgetting that conditional formatting is separate. It lives on the worksheet as rules over ranges; add a rule covering the new rows rather than copying cells.
• Copying merged cells cell by cell. Only the top-left cell of a merged range holds the value and full style; re-create the merge with ws.merge_cells() on the target.
• Theme colours. Some templates use theme colours (fgColor.theme) rather than RGB. They copy correctly within a workbook but render in the target workbook's theme, which may differ.

Performance and scale notes

Style assignment is cheap per cell but adds up: copying six attributes to 100,000 cells is 600,000 Python-level assignments and takes several seconds. openpyxl deduplicates identical styles when saving, so the file does not bloat, but the loop is still slow. For large ranges prefer a NamedStyle, style whole columns once in the template so new rows inherit them where Excel allows it, or generate the report with xlsxwriter, where a format object is attached at write time at no extra cost — see Write a Formatted Excel Report with xlsxwriter.

Conclusion

Copy a cell's look by copying its six style attributes with copy() — a small helper that works everywhere. Use _style only as a fast path inside one workbook, remember that widths, heights, merges and conditional formats live on the sheet rather than the cell, and switch to a registered NamedStyle when one look covers many cells. Always copy from a fixed template cell or row, never from the previous row, so the formatting stays consistent however long the report grows.

Frequently asked questions

Why can't I just assign target.font = source.font? In openpyxl style objects are immutable and shared, and assigning the same object works within one workbook, but copying with copy() is the documented, safe pattern — and it is required when the target lives in a different workbook.

Is copying cell._style faster? Yes, and within one workbook it is safe, because _style holds indexes into that workbook's style tables. Across workbooks those indexes point at the wrong or missing entries, and openpyxl raises IndexError or applies the wrong format.

Does copying a cell style copy conditional formatting? No. Conditional formatting rules belong to the worksheet and apply to ranges, not to individual cells. Add the rule to the new range instead.

How do I copy a style to a whole range quickly? Create a NamedStyle once, register it with the workbook, and assign its name to each cell. Every cell then shares one style record, which keeps the file small and the loop fast.

Related

• Up one level: Using openpyxl for Excel File Manipulation — the openpyxl object model.
• Styling Excel Cells with openpyxl — building styles from scratch.
• Populate an Excel Template Without Losing Formatting — where copied row formats matter most.
• Unmerge Cells and Fill the Values with openpyxl — handling merged ranges when copying.