Editing Existing Excel Templates

Most of the time, a spreadsheet is just a data source: read it with pandas.read_excel, work with the result, move on. Sometimes it is the opposite — a downstream tool (finance software, a partner's reporting system, a legacy planning tool) expects a specific existing file, with its own formulas, formatting, and layout, and your job is to fill in a few computed values without disturbing anything else.

That second case is a job pandas.read_excel and to_excel cannot do — they read or write plain data, not edit a file in place. It is what openpyxl is for.

Two different jobs, two different tools#

import pandas as pdfrom openpyxl import load_workbook
results = pd.read_excel("computed_totals.xlsx")wb = load_workbook("report_template.xlsx")

pandas.read_excel treats a spreadsheet purely as data — rows and columns, nothing else. load_workbook opens the actual file: its formulas, styles, and structure come along for the ride and are preserved unless you explicitly change them. Reading the computed results with Pandas and opening the target template with openpyxl is the normal combination — each library does the half of the job the other cannot.

Writing into specific cells#

ws = wb["Summary"]
for _, row in results.iterrows():    key = int(row["region_id"])    for excel_row in range(3, ws.max_row + 1):        if ws.cell(excel_row, 1).value == key:            ws[f"D{excel_row}"] = row["total"]            break

There is usually no ready-made key to merge on — the target row lives at whatever position the template's author put it, and has to be located by scanning a known identifier column. This loop does exactly that: for each computed result, scan column A for a matching id, then write the value into the fixed column the template expects, and stop once found.

Finally, save under a new name so the original template stays untouched:

wb.save("report_filled.xlsx")

Two things worth doing better in a real version#

Resolve the column by name, not by a hard-coded letter. f"D{excel_row}" is only meaningful because someone opened this specific template and found which column the target tool reads totals from. If the template changes — a column inserted upstream — the letter silently points at the wrong cell with no error at all. Reading the header row once and looking up the target column by its label is more robust than encoding the letter as a literal.

Build a lookup before the loop, not during it. Scanning every worksheet row for every result is quadratic in the row count — fine at a few hundred rows, not fine at tens of thousands. A {key: excel_row} dictionary built once from a single pass over the sheet turns each lookup into a constant-time operation instead.

Common mistakes#

Writing over the original template#

wb.save() overwrites whatever path you pass it — including the source file, if you pass the same name back. Save to a new filename (or a copy made beforehand) so a bad run does not destroy the template.

Assuming read_excel and load_workbook are interchangeable#

read_excel cannot preserve an existing file's formulas and formatting, and load_workbook is unwieldy for exploring or transforming tabular data. Reach for the one that matches what the step actually needs: reading data, or editing a file.

Encoding a fragile column letter without a comment or a lookup#

A cell reference like D{row} carries no explanation of why column D is the right one. At minimum, document where that mapping came from; better, resolve it from the header row so it survives a template change.

Further reading#

• openpyxl tutorial — a full walkthrough of cells, styles, and worksheets.
• Office Open XML — the file format an .xlsx workbook actually is, and what "preserving formatting" means under the hood.
• pandas.read_excel and DataFrame.to_excel — the data-only counterparts, for when there is no existing template to preserve.

See also#