Populate an Excel Template Without Losing Formatting

The promise of a template is that its formatting is permanent — you fill in numbers and the fonts, fills, borders, number formats, and print layout come along for free. Whether that promise holds depends entirely on how you write the data. This guide, a companion to Generating Excel Reports from Templates, is about preservation: the one Python approach that keeps formatting, the one that destroys it, and precisely what openpyxl does and does not carry through a round-trip.

Prerequisites and install

pip install openpyxl pandas

Why pandas.to_excel destroys template styling

pandas.to_excel(path) is built to serialize a DataFrame, not to edit a file. Even when the target path already exists, pandas does not open and patch it — it constructs a new worksheet and writes a plain grid of values and column headers. Every style your template carried is gone: the title banner, the header fill, merged cells, borders, conditional formatting, the print area, the totals formula.

The demonstration below makes the loss visible. We build a styled template, round-trip it through pandas, and check the header cell's fill:

from openpyxl import Workbook, load_workbook
from openpyxl.styles import Font, PatternFill
import pandas as pd

# A styled template
wb = Workbook()
ws = wb.active
ws["A1"] = "Region"
ws["A1"].font = Font(bold=True, color="FFFFFF")
ws["A1"].fill = PatternFill("solid", fgColor="4472C4")
ws["A2"] = "North"
wb.save("styled.xlsx")

# Round-trip through pandas
df = pd.read_excel("styled.xlsx")
df.to_excel("via_pandas.xlsx", index=False)

# Inspect the result
check = load_workbook("via_pandas.xlsx")["Sheet1"]["A1"]
print("Fill after pandas:", check.fill.fgColor.rgb)   # 00000000 — styling lost

The header fill comes back as the default 00000000. Pandas rebuilt the sheet from scratch.

Why openpyxl load-and-edit keeps formatting

openpyxl's load_workbook() parses the workbook's actual XML and hands you the real cells, with their styles attached. When you assign cell.value, you change one thing — the value — and leave the style record alone. Save, and every untouched style is written straight back out. The same template, edited in place:

from openpyxl import load_workbook

wb = load_workbook("styled.xlsx")    # the styled template from above
ws = wb.active

ws["A2"] = "South"                   # change the value only

wb.save("via_openpyxl.xlsx")

check = load_workbook("via_openpyxl.xlsx")["Sheet"]["A1"]
print("Fill after openpyxl:", check.fill.fgColor.rgb)   # 004472C4 — preserved

The fill survives because you never touched it. This is the whole technique: write only cell.value, leave cell styling untouched.

What openpyxl preserves — and what it can drop

openpyxl is reliable for cell-level formatting and less so for some embedded objects. Know the boundary before you build a pipeline on it:

The practical rule: write only data cells, then re-save, and keep templates focused on layout and formulas rather than pivots and exotic objects. Always open one generated file in Excel before trusting the pipeline, especially if the template carries charts, images, or pivots.

Set the value without touching the style

The reason in-place editing preserves formatting is that value and style are separate attributes on a cell. Assigning the value never mutates the style:

from openpyxl import load_workbook

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

cell = ws["A1"]
before = cell.font.bold
cell.value = "Territory"     # value changes...
after = cell.font.bold       # ...style does not

print(f"bold before={before}, after={after}")   # both True
wb.save("retitled.xlsx")

cell.style is effectively read-mostly: it names a registered named-style and is not where you make ad-hoc tweaks. To restyle, set the specific attributes (cell.font, cell.fill, cell.number_format) rather than assigning to cell.style. For most template work you set none of them — that is the point.

Copy a style to a new cell when you must extend a region

Sometimes you add rows beyond the template's pre-styled block and want the new cells to match. Styles are shared, immutable objects, so copy with copy() before assigning, to avoid mutating the source cell's style by reference:

from copy import copy
from openpyxl import load_workbook

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

src = ws["A2"]               # a pre-styled data cell
dst = ws["A3"]               # a new row to match it
dst.value = "West"
dst.font = copy(src.font)
dst.fill = copy(src.fill)
dst.border = copy(src.border)
dst.number_format = src.number_format   # number_format is a plain string

wb.save("extended.xlsx")
print("Copied style to A3")

Better still, size the template's styled region for your largest run so you rarely need to copy styles at all.

Common pitfalls

A note on verification

Preservation is "usually" reliable, not "always." The cheap insurance is to open one generated workbook in Excel after any template or library change and confirm conditional formatting, data validation dropdowns, charts, and the print area all survived. Once verified for a given template and openpyxl version, the pipeline is dependable until one of those changes.

Frequently asked questions

Can I use pandas at all in a template pipeline? Yes — for the data, not the writing. Build and transform your DataFrame with pandas, then hand the values to openpyxl cell by cell. Never let pandas write to the template file.

Does keep_vba=True affect formatting preservation? It is orthogonal. keep_vba=True preserves the VBA macro project in .xlsm/.xltm files; cell formatting is preserved by editing in place regardless. Use both for a macro-enabled template.

Why does my number show as text after editing? You likely assigned a string. Number formats apply to numeric values — assign an int/float (or datetime) and the template's number format renders it correctly.

Is there a faster bulk way that still preserves styles? No streaming shortcut preserves an existing template's styles — those modes write fresh files. For template injection, in-place load_workbook editing is the correct and only style-safe path.

Conclusion

Formatting is preserved when you treat the template as a file to patch, not a sheet to regenerate. openpyxl's load_workbook hands you real cells with their styles attached; assign cell.value and the styling rides through the save untouched. pandas.to_excel rebuilds the sheet and discards all of it. Know the small set of objects openpyxl can drop — pivots and, on older versions, charts and images — write only data cells, and verify one output in Excel after any change.

Where to go next

• Generating Excel Reports from Templates — the cluster overview tying preservation into the full template pipeline.
• Fill an Excel Template with Python and openpyxl — the step-by-step mechanics of writing the data cells you preserve formatting around.
• Styling Excel Cells with openpyxl — how fonts, fills, and borders are constructed when you do build styling in code.