Python Excel AutomationPractical guides for developers
Start learning
Guide
Getting Started With Python Excel AutomationDeep dive

Insert and Delete Rows and Columns with openpyxl

Add and remove rows and columns in an existing Excel file with openpyxl — insert_rows, delete_cols, why formulas and merges do not move, and safe deletion in reverse order.

Sometimes a sheet needs a new column between two existing ones, or a block of rows removed. openpyxl has four methods for it, and they work — but they do considerably less than Excel's equivalent commands, and the gap is where bugs come from. Formulas do not follow the cells they reference. Merged ranges and conditional formatting do not always move. And deleting rows in the obvious order removes the wrong ones. This guide covers the mechanics and the guard rails. It is part of Using openpyxl for Excel File Manipulation.

What insert_rows moves, and what it leaves behind Before the insert, rows two to four hold data and row five holds a SUM over B2 to B4. After inserting a row at position three, the data rows shift down and the total lands in row six, but its formula text still reads SUM of B2 to B4. Excel would have rewritten it to B2 to B5; openpyxl does not, so the new row is silently excluded from the total. before after insert_rows(3) 2 North · 120 3 South · 95 4 West · 140 5 =SUM(B2:B4) 2 North · 120 3 East · 88 (new) 4 South · 95 5 West · 140 6 =SUM(B2:B4) — unchanged the new row is silently excluded from the total

Prerequisites

Bash
pip install openpyxl

A sheet with a formula, so the traps are visible:

Python
from openpyxl import Workbook

wb = Workbook()
ws = wb.active
ws.append(["region", "units"])
ws.append(["North", 120])
ws.append(["South", 95])
ws.append(["West", 140])
ws["A5"] = "Total"
ws["B5"] = "=SUM(B2:B4)"
wb.save("sales.xlsx")

Step 1 — Insert rows and columns

The four methods take a position and a count:

Python
from openpyxl import load_workbook

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

ws.insert_rows(3)                 # one row before row 3
ws.insert_rows(3, amount=2)       # two rows before row 3
ws.insert_cols(2)                 # one column before column B
ws.insert_cols(2, amount=3)       # three columns before column B

wb.save("sales_expanded.xlsx")

The position is where the new row will be, not where it goes after. insert_rows(3) makes the new row row 3 and pushes the old row 3 down to row 4.

Inserted rows are empty and unstyled — they do not inherit the formatting of their neighbours the way Excel's insert does. Fill them yourself:

Python
from copy import copy
from openpyxl import load_workbook

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

ws.insert_rows(3)
ws["A3"] = "East"
ws["B3"] = 88

# Copy the style from the row below, which is the old row 3.
for col in range(1, ws.max_column + 1):
    source = ws.cell(row=4, column=col)
    target = ws.cell(row=3, column=col)
    target._style = copy(source._style)

wb.save("sales_expanded.xlsx")

Copying _style wholesale carries font, fill, border, alignment and number format in one assignment. It is a private attribute, but it is the practical way to clone a cell's complete appearance — assigning the individual style objects one by one is both longer and easy to leave incomplete.

Step 2 — Fix the formulas yourself

This is the part that catches people. openpyxl moves cells; it does not rewrite formula text. After the insert above, the total still reads =SUM(B2:B4) and excludes the new row.

You have two options. The robust one is to rewrite the formulas after any structural change, which is easy when your script owns the layout:

Python
from openpyxl import load_workbook

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

ws.insert_rows(3)
ws["A3"], ws["B3"] = "East", 88

# Rewrite the total to span whatever the data range now is.
last_data_row = ws.max_row - 1          # the total sits on the last row
ws.cell(row=ws.max_row, column=2).value = f"=SUM(B2:B{last_data_row})"

wb.save("sales_expanded.xlsx")

The more durable one is to use a named range for the data block and have the formula reference the name. Names are stored once and are far easier to update than scanning formula text — see creating a named range in Excel with openpyxl.

If you must patch existing formulas, do it deliberately rather than with a blanket regular expression — a naive substitution will happily corrupt a text cell that happens to contain something like B2:B4:

Python
import re
from openpyxl import load_workbook

RANGE = re.compile(r"\b([A-Z]{1,3})(\d+):([A-Z]{1,3})(\d+)\b")

def shift_ranges(formula, at_row, amount):
    """Extend ranges that span the insertion point. Formulas only."""
    def repl(m):
        c1, r1, c2, r2 = m.group(1), int(m.group(2)), m.group(3), int(m.group(4))
        if r1 < at_row <= r2:
            r2 += amount
        return f"{c1}{r1}:{c2}{r2}"
    return RANGE.sub(repl, formula)

wb = load_workbook("sales.xlsx")
ws = wb.active
for row in ws.iter_rows():
    for cell in row:
        if isinstance(cell.value, str) and cell.value.startswith("="):
            cell.value = shift_ranges(cell.value, at_row=3, amount=1)

The startswith("=") guard is what keeps this safe — only formula cells are touched.

Step 3 — Delete in reverse order

Deleting looks symmetrical and is not. Every deletion shifts the rows below it up by one, so a list of row numbers collected beforehand goes stale the moment you delete the first.

Why deleting rows in ascending order removes the wrong ones Starting from six rows, the goal is to delete rows three and five. Deleting ascending removes row three first, which shifts everything below up by one, so the second deletion of row five actually removes what was originally row six — the wrong row, with no error. Deleting descending removes row five first, which does not affect the position of row three, so the second deletion removes exactly the intended row. goal: delete rows 3 and 5 ascending: 3 then 5 delete 3 → rows 4,5,6 shift up to become 3,4,5 delete 5 → removes what was originally row 6 wrong row gone, no error raised descending: 5 then 3 delete 5 → only rows below row 5 move delete 3 → row 3 is still exactly where it was both intended rows removed

Collect first, delete second, and always descending:

Python
from openpyxl import load_workbook

def delete_rows_where(ws, predicate, min_row=2):
    """Delete every row for which predicate(row_values) is true."""
    doomed = [
        cell.row
        for cell in (r[0] for r in ws.iter_rows(min_row=min_row))
        if predicate([c.value for c in ws[cell.row]])
    ]

    # Descending: deleting a later row never moves an earlier one.
    for row in sorted(doomed, reverse=True):
        ws.delete_rows(row)

    return len(doomed)

wb = load_workbook("sales.xlsx")
ws = wb.active
removed = delete_rows_where(ws, lambda values: values[1] in (None, 0))
print(f"removed {removed} rows")
wb.save("sales_clean.xlsx")

Never delete while iterating. The iterator holds positions that the deletion invalidates, and you get skipped rows or an exception with no clear cause.

Deleting contiguous blocks is both faster and simpler — one call instead of many:

Python
ws.delete_rows(10, amount=25)        # rows 10 through 34 in one operation
ws.delete_cols(4, amount=3)          # columns D, E and F

Step 4 — Repair what did not move

What shifts with an insert, and what stays behind Two columns. Cell values, cell styles and row heights move with the rows, which is the behaviour people expect. Formula text, merged ranges, conditional formatting ranges, data validation ranges and chart series references are stored as coordinates and are not all recalculated, so they end up pointing at the wrong cells. The second group must be checked and re-applied after any structural change. moves with the rows cell values cell styles and number formats row heights the behaviour you expect stays at fixed coordinates formula text inside cells merged ranges conditional formatting ranges validation and chart references check and re-apply after any insert or delete

Merged ranges, conditional formatting, data validation and chart references are stored as coordinate strings, and openpyxl does not consistently rewrite all of them when the sheet shifts. Check afterwards:

Python
from openpyxl import load_workbook

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

print("merged:", [str(r) for r in ws.merged_cells.ranges])
print("validations:", [str(dv.sqref) for dv in ws.data_validations.dataValidation])
print("conditional:", list(ws.conditional_formatting))

The pragmatic pattern for a sheet with several of these is to strip and re-apply rather than trying to patch coordinates. Re-adding a conditional format over the new range is a few lines and is guaranteed correct, whereas patching a range string is guesswork:

Python
from openpyxl.formatting.rule import CellIsRule
from openpyxl.styles import PatternFill

ws.conditional_formatting = type(ws.conditional_formatting)()   # clear
last = ws.max_row - 1
ws.conditional_formatting.add(
    f"B2:B{last}",
    CellIsRule(operator="lessThan", formula=["100"],
               fill=PatternFill("solid", bgColor="FEE8F2")),
)

The same reasoning applies to the rules described in applying conditional formatting to a range with openpyxl.

Common pitfalls and fixes

SymptomCauseFix
Total excludes the new rowFormula text not rewrittenRewrite the formula, or use a named range.
Wrong rows deletedDeleted in ascending orderSort the row numbers descending.
Iterator skips rows or raisesDeleting during iterationCollect first, delete in a second pass.
Inserted row has no formattingopenpyxl does not inherit stylesCopy _style from a neighbouring row.
Merged range now covers the wrong cellsMerges stored as coordinatesRe-apply merges after the change.
Conditional formatting stops at the old last rowRange string unchangedClear and re-add the rule.
Chart plots the wrong seriesChart references are absoluteRebuild the chart after restructuring.
Very slow with many insertsEvery insert shifts all cells belowRebuild the sheet instead.

Performance and scale notes

Each insert or delete shifts every cell below the point of change. That makes a single call cheap and a loop of calls quadratic — a thousand individual inserts on a fifty-thousand-row sheet moves cells fifty million times.

Python
import time
from openpyxl import Workbook

wb = Workbook(); ws = wb.active
for i in range(20_000):
    ws.append([f"row {i}", i])

start = time.perf_counter()
for _ in range(200):
    ws.insert_rows(2)
print(f"200 inserts: {time.perf_counter() - start:.2f}s")

Three rules follow.

Batch with amount=. One insert_rows(2, amount=200) does the shifting once; two hundred separate calls do it two hundred times.

Rebuild rather than restructure when the change is substantial. Writing the rows you want into a fresh sheet is linear, and it sidesteps every formula, merge and conditional-formatting problem in this guide:

Python
import pandas as pd

# Instead of deleting a thousand rows one by one:
df = pd.read_excel("sales.xlsx")
df = df[df["units"] > 0]
df.to_excel("sales_clean.xlsx", index=False, engine="xlsxwriter")

For most cleaning tasks that is not just faster but simpler — the row filtering belongs in pandas, and the workbook is written once at the end. Reach for insert_rows and delete_rows when you must preserve an existing sheet's formatting and structure, as when filling a template; otherwise let writing a DataFrame to Excel produce the shape you want directly.

Do the structural work before the styling. Inserting rows into a heavily styled sheet moves style references as well as values, so a script that styles first and restructures afterwards does the expensive work twice. Get the shape right, then format.

Conclusion

insert_rows, insert_cols, delete_rows and delete_cols move cells and nothing else. Formulas keep their original text, so rewrite them — or reference a named range that you update once. Delete in descending order, always, and collect the row numbers in a separate pass from the deletion. Check merged ranges, conditional formatting and validations afterwards, and re-apply rather than patch. And when the change is more than a few operations, rebuilding the sheet from the data you want is both faster and free of every trap above.

Frequently asked questions

Do formulas update when I insert a row with openpyxl? No. openpyxl moves cell values and styles but does not rewrite formula text, so a SUM over B2:B10 still says B2:B10 after a row is inserted in the middle. Excel would adjust it; openpyxl does not.

Why did my deletion remove the wrong rows? You deleted in ascending order. Every deletion shifts the rows below up by one, so the indexes you collected before starting no longer point at the same rows. Delete from the bottom upwards instead.

Do merged cells, charts and conditional formatting move with the rows? Not reliably. Merged ranges, chart references, data validation ranges and conditional formatting ranges are stored as coordinates and are not all recalculated on insert or delete. Check and re-apply them afterwards.

Is it faster to rebuild the sheet than to insert rows? Almost always, for anything beyond a handful of operations. Each insert or delete shifts every cell below the point of change, so a loop of a thousand inserts is far slower than writing a new sheet from the desired data.

How do I delete every row matching a condition? Collect the matching row numbers in one pass, then delete them in reverse order in a second pass. Never delete while iterating — the iterator and the sheet fall out of step.