Copy a Formula Down a Column in openpyxl

Excel users drag the fill handle; from Python there is no handle to drag. Copying a formula down a column means writing one formula per row, and there are two good ways to do it: generate each formula from the row number, or take one source formula and let openpyxl translate it. This guide, part of Working with Excel Formulas in Python, covers both, together with the styling and blank-row details that decide whether the result looks hand-made or generated.

Prerequisites

pip install openpyxl

Approach 1: generate a formula per row

The most transparent method. Loop over the data rows and build the string for each one:

from openpyxl import Workbook

rows = [
    ("A-100", 4, 19.99),
    ("B-200", 2, 49.50),
    ("C-300", 7, 12.25),
    ("D-400", 5, 8.75),
]

wb = Workbook()
ws = wb.active
ws.title = "Orders"
ws.append(["SKU", "Quantity", "Unit_Price", "Line_Total", "Share"])

for sku, qty, price in rows:
    ws.append([sku, qty, price])

first, last = 2, ws.max_row
total_row = last + 2
ws[f"C{total_row}"] = "Total"
ws[f"D{total_row}"] = f"=SUM(D{first}:D{last})"

for r in range(first, last + 1):
    ws[f"D{r}"] = f"=B{r}*C{r}"
    ws[f"E{r}"] = f"=IF($D${total_row}=0,0,D{r}/$D${total_row})"
    ws[f"D{r}"].number_format = "#,##0.00"
    ws[f"E{r}"].number_format = "0.0%"

wb.save("filled_down.xlsx")
print(ws["E3"].value)      # '=IF($D$8=0,0,D3/$D$8)'

Everything the loop needs comes from first, last and total_row, so a longer dataset changes nothing but the numbers. The IF guard is worth the extra characters: without it, an empty dataset gives every row #DIV/0! and the report looks broken rather than empty.

Approach 2: translate an existing formula

When the source formula already exists — because you wrote it once, or because it came from a template — Translator shifts it for you and gets the anchoring right without string surgery:

from openpyxl import load_workbook
from openpyxl.formula.translate import Translator

wb = load_workbook("filled_down.xlsx")
ws = wb["Orders"]

source_coord = "D2"
source_formula = ws[source_coord].value        # '=B2*C2'

for r in range(3, 6):
    ws[f"D{r}"] = Translator(source_formula, origin=source_coord).translate_formula(f"D{r}")

print(ws["D5"].value)      # '=B5*C5'
wb.save("translated.xlsx")

Translator parses the formula, applies the row and column offset between origin and the destination to every relative reference, and leaves absolute parts untouched — exactly what Excel does on a copy-paste. It also handles the awkward cases string replacement gets wrong, such as a formula mentioning both B2 and AB2, or a sheet name that happens to contain something that looks like a reference.

The origin argument must be the coordinate the formula currently lives at. Passing the wrong origin produces a formula that is offset twice, which is a bug that survives review because the result still looks like a valid formula.

Copy the source cell's formatting too

Assigning a value never carries style with it. If the source cell is currency-formatted and bold, the copies are not — which is usually the first thing a reader notices:

from copy import copy
from openpyxl import load_workbook

wb = load_workbook("filled_down.xlsx")
ws = wb["Orders"]

source = ws["D2"]
for r in range(3, 6):
    target = ws[f"D{r}"]
    target._style = copy(source._style)      # font, fill, border, alignment, number format

wb.save("styled_fill.xlsx")

_style is a single object holding the whole style record, so copying it is both faster and more complete than setting font, fill, border and number_format one by one. Copy it — do not assign the same object to many cells — because openpyxl styles are shared immutable records and assigning without copy() can tie cells together in ways that surprise you later. When only the number format matters, setting target.number_format directly is simpler and clearer.

Only fill where there is data

Filling a formula down a fixed range leaves #VALUE! or zeros on empty rows below the data. Two defences, used together:

from openpyxl import load_workbook

wb = load_workbook("filled_down.xlsx")
ws = wb["Orders"]

# 1. Derive the last data row from a column that is always populated
last_data = max(
    (cell.row for cell in ws["A"] if cell.value not in (None, "")),
    default=1,
)

# 2. Make each formula self-blanking, in case a row is cleared later
for r in range(2, last_data + 1):
    ws[f"D{r}"] = f'=IF(A{r}="","",B{r}*C{r})'

wb.save("safe_fill.xlsx")
print("filled rows 2 to", last_data)

Deriving last_data from the key column rather than ws.max_row matters on a sheet that has a total block or stray formatting at the bottom: max_row counts any row Excel considers used, including one that only holds a border, so it routinely overshoots the real data.

Filling across columns as well

The same two approaches extend to a rectangle. Translator shifts columns as readily as rows, which makes a monthly grid a two-line job:

from openpyxl import Workbook
from openpyxl.formula.translate import Translator
from openpyxl.utils import get_column_letter

wb = Workbook()
ws = wb.active
ws.append(["Region", "Jan", "Feb", "Mar", "Q1 %"])
for region, jan, feb, mar in (("North", 100, 120, 140), ("South", 90, 95, 99)):
    ws.append([region, jan, feb, mar])

ws["E2"] = "=SUM(B2:D2)/SUM($B$2:$D$3)"
origin = "E2"
for r in range(3, ws.max_row + 1):
    ws[f"E{r}"] = Translator(ws[origin].value, origin=origin).translate_formula(f"E{r}")
    ws[f"E{r}"].number_format = "0.0%"

print(get_column_letter(5), ws["E3"].value)
wb.save("grid_fill.xlsx")

Note how the denominator stays $B$2:$D$3 on every row: it is anchored, so both the row fill and any later column fill leave it alone. Choosing the anchors carefully in the one source formula is what makes the rest of the grid correct for free.

Filling into an Excel table

If the destination range is a real Excel table, a formula written into one cell of a calculated column is expected to appear in every row of that column when a person edits it — but openpyxl writes only the cell you assign. Fill the whole column yourself, then extend the table reference so the new rows belong to it:

from openpyxl import load_workbook

wb = load_workbook("orders_table.xlsx")
ws = wb.active
table = ws.tables["Orders"]

start, end = table.ref.split(":")
last_row = ws.max_row
table.ref = f"{start}:{end[0]}{last_row}"     # stretch to the new last row

for r in range(2, last_row + 1):
    ws[f"D{r}"] = f"=B{r}*C{r}"

wb.save("orders_table_filled.xlsx")

Stretching table.ref matters because Excel treats the table range as authoritative: rows outside it are ordinary cells, so they lose the banded formatting and are ignored by any structured reference such as Orders[Line_Total]. Writing the formulas and forgetting the range is the usual reason a filled column looks right but a summary elsewhere under-reports.

Common pitfalls and gotchas

Performance and scale notes

Both approaches write one formula string per cell, so the cost is linear in rows. Translator parses each formula it shifts, which makes it measurably slower than an f-string in a tight loop — on 100,000 rows that difference is worth caring about, and generating the string yourself wins. The bigger scale question is whether the reader needs formulas at all: 100,000 live formulas make a workbook slow to open and recalculate, while computing the column in pandas and writing numbers keeps the file fast. Reserve the filled column for sheets where someone is expected to change an input.

Conclusion

There is no fill handle in the file format, so filling down is always "write one formula per row". Generate the strings when your code owns the layout, translate when the source formula belongs to a template, copy _style if the cells should look alike, and derive the last row from a column that always has data. Get the anchors right once and every copied row inherits correct references.

Frequently asked questions

Does openpyxl have a fill handle? No. Copying a formula down means writing one formula per row, either generated in a loop or produced from a source formula with the Translator class.

What does Translator actually change? It shifts relative references by the row and column offset between the origin cell and the destination, and leaves anchored parts such as $B$1 exactly as they are.

Does copying the formula copy the formatting too? No. Assigning a value never copies style. Use copy(source._style) or set number_format, font and fill explicitly on each destination cell.

How do I stop the formula appearing on blank rows? Only write it where the source data exists, or wrap the expression in IF(A2="","",…) so the cell shows nothing when the row is empty.

Related

Up to the parent guide:

• Working with Excel Formulas in Python — anchoring, names and the formula round trip.

Related guides:

• Write a Formula to an Excel Cell with openpyxl — the single-cell case in detail.
• Add SUM and AVERAGE Formulas with Python — the total row under a filled column.
• Set Column Width and Row Height in openpyxl — making the filled column readable.
• Styling Excel Cells with openpyxl — fonts, fills and formats you may want to copy.