Python Excel AutomationPractical guides for developers
How-to
Getting Started With Python Excel AutomationDeep dive

Delete Empty Rows and Columns with openpyxl

Remove blank rows and columns from a worksheet in place while keeping its formatting: detect empty rows without creating cells, delete them in runs from the bottom up, handle empty columns, repair row heights, merged cells and formulas that delete_rows leaves behind, and switch to a copy-based rebuild when the sheet is large.
openpyxlData cleaning

Exports from accounting and ERP systems love blank rows: a spacer after every group, an empty column where a field used to be, a block of formatted-but-empty rows at the bottom that makes max_row report 5,000 when the data stops at 800. With pandas, dropping blank rows is one dropna(how="all") — but pandas rewrites the file and throws away the fonts, fills, widths and number formats someone set up. When the formatting has to stay, the edit has to happen in openpyxl, on the worksheet itself. openpyxl has delete_rows and delete_cols, and they work, but they are slow when called row by row and they leave several things unadjusted. This page covers detection, deletion in the right order, the repairs, and a much faster rebuild for large sheets. It sits under Using openpyxl for Excel File Manipulation.

Prerequisites

Bash
pip install openpyxl

export.xlsx has a styled header, six data rows separated by blank spacer rows, an empty column C (only its header cell exists, and it is blank), a Total row with a SUM formula, a tall row, a Total label merged across A:B, and 39 formatted-but-empty rows below the data — so max_row overstates the size of the sheet.

Step 1 — Find the empty rows without creating cells

Python
from openpyxl import load_workbook

wb = load_workbook("export.xlsx")
ws = wb["Export"]
print("max_row before:", ws.max_row)

def is_blank(v):
    return v is None or (isinstance(v, str) and v.strip() == "")

empty_rows = [i for i, row in enumerate(ws.iter_rows(values_only=True), start=1)
              if all(is_blank(v) for v in row)]
print("empty rows:", empty_rows[:6], "...", len(empty_rows), "in total")

Use iter_rows(values_only=True) for the scan. The tempting alternative, ws[r] or ws.cell(r, c) inside a loop, creates any cell it touches, so checking whether a row is empty quietly fills it with empty cell objects and makes max_row worse. is_blank treats whitespace-only strings as empty, which is usually what you want for exported data; drop the second condition if a space is meaningful. Rows that carry formatting but no value count as empty here — that is what lets the formatted tail at the bottom go too.

Step 2 — Delete in runs, from the bottom up

delete_rows(idx, amount) shifts every row below idx up by amount. Deleting from the top would make every remaining index in the list wrong after the first deletion; deleting from the bottom keeps the rows above untouched. Grouping consecutive rows into runs means one call per run instead of one per row:

Python
def runs(indices):
    out = []
    for i in indices:
        if out and out[-1][0] + out[-1][1] == i:
            out[-1][1] += 1
        else:
            out.append([i, 1])
    return out

blocks = runs(empty_rows)
print("delete calls needed:", len(blocks))
for start, amount in reversed(blocks):
    ws.delete_rows(start, amount)
print("max_row after:", ws.max_row)
Spacer rows removed, the styled header and data kept Before, the sheet has data rows for North, South and East separated by blank rows, and blank rows below the total. After deleting the empty rows in runs from the bottom, the data rows and the total sit directly under the header. A B C 1 Region Account Amount 2 North Sales 1,200 3 North Services 340 4 5 South Sales 980 6 7 East Sales 1,500 A B C 1 Region Account Amount 2 North Sales 1,200 3 North Services 340 4 South Sales 980 5 East Sales 1,500 before after delete_rows(start, n)

The scan found the spacer rows and the formatted tail; the tail is one long run, so it goes in a single call. The header keeps its bold white-on-indigo style, and the amount cells keep their #,##0 format, because deleting rows moves the remaining cell objects rather than rewriting them.

Step 3 — Delete empty columns the same way

Columns work identically with iter_cols and delete_cols. Decide whether a column with only a header counts as empty — here column C has a header cell that is blank, so it qualifies; if your headers are filled in, skip the first row in the test:

Python
empty_cols = [j for j, col in enumerate(ws.iter_cols(values_only=True), start=1)
              if all(is_blank(v) for v in col)]
print("empty columns:", empty_cols)
for start, amount in reversed(runs(empty_cols)):
    ws.delete_cols(start, amount)
print([c.value for c in ws[1]])

To ignore the header when testing, use ws.iter_cols(min_row=2, values_only=True). Column widths have the same problem as row heights, covered next: delete_cols does not move column_dimensions, so the width set for column D stays on the letter D even though the Amount data now lives in column C.

Step 4 — Repair what delete_rows does not move

delete_rows and delete_cols move cell values and cell styles. Several other things stay where they were. Check them on a tiny example:

Python
from openpyxl import Workbook

demo = Workbook().active
demo["A1"], demo["A2"], demo["A4"] = "Header", 1, 2
demo["A5"] = "=SUM(A2:A4)"
demo.row_dimensions[4].height = 30
demo.merge_cells("B4:C4")
demo["B4"] = "merged"
demo.delete_rows(3)
print("formula:", demo["A4"].value)
print("height on row 3:", demo.row_dimensions[3].height, "| row 4:", demo.row_dimensions[4].height)
print("merged ranges:", demo.merged_cells.ranges, "| value now in B3:", demo["B3"].value)
What delete_rows updates and what it leaves behind A grid of worksheet features. Cell values and cell styles move with the rows. Formulas, row heights, merged cell ranges, conditional formatting ranges, data validation, defined names and charts keep their old positions. Moves with the rows Cell values Cell styles (font, fill, number format) Formula references Row heights and column widths Merged cell ranges Conditional formats, validation, names, charts

The formula still reads =SUM(A2:A4) although its range should now end at A3; the 30-point height stayed on row 4 instead of moving to row 3; and the merged range is still B4:C4 while the merged cell's value moved to B3 — a recipe for a broken-looking sheet or a repair prompt in Excel. The fix is to remember these features before deleting and re-apply them afterwards with the row numbers mapped to their new positions:

Python
def remap_rows(ws, deleted):
    deleted = set(deleted)
    new_index, n = {}, 0
    for r in range(1, ws.max_row + len(deleted) + 1):
        if r not in deleted:
            n += 1
            new_index[r] = n
    return new_index

wb2 = load_workbook("export.xlsx")
ws2 = wb2["Export"]
blank = [i for i, row in enumerate(ws2.iter_rows(values_only=True), 1) if all(is_blank(v) for v in row)]
mapping = remap_rows(ws2, blank)
heights = {r: d.height for r, d in ws2.row_dimensions.items() if d.height}
merges = [(m.min_row, m.min_col, m.max_row, m.max_col) for m in ws2.merged_cells.ranges]
for m in list(ws2.merged_cells.ranges):
    ws2.unmerge_cells(str(m))
for start, amount in reversed(runs(blank)):
    ws2.delete_rows(start, amount)
for r in list(ws2.row_dimensions):
    ws2.row_dimensions[r].height = None
for r, h in heights.items():
    if r in mapping:
        ws2.row_dimensions[mapping[r]].height = h
for r1, c1, r2, c2 in merges:
    if r1 in mapping and r2 in mapping:
        ws2.merge_cells(start_row=mapping[r1], start_column=c1, end_row=mapping[r2], end_column=c2)
print({r: d.height for r, d in ws2.row_dimensions.items() if d.height}, ws2.merged_cells.ranges)

Merges are removed before the deletion and recreated afterwards at their mapped rows, so the Total label spans A:B again on its new row 8, and the 28-point height that belonged to old row 9 (East · Sales) lands on that row's new position, row 6. Formulas are the hard part: openpyxl does not parse references, so a SUM that spans deleted rows must be rewritten. For a known layout, regenerate the total after the deletion (ws2.cell(total_row, 4, f"=SUM(D2:D{total_row - 1})")); for unknown workbooks, list every formula first with the approach in Audit Every Formula in a Workbook with Python, or let LibreOffice do the deletion through its own engine, which does update references — see Automate LibreOffice Calc from Python with UNO.

Step 5 — Rebuild instead of deleting when the sheet is large

Each delete_rows call moves every cell below the deletion point, so the total cost grows with the number of runs times the number of rows. On a 10,000-row test sheet with 1,622 separate blank runs, the grouped deletion took 14 seconds; on 3,000 rows it took 1.2 seconds. Copying the non-empty rows to a fresh sheet is linear and took 0.03 seconds for the same 10,000 rows:

Python
from copy import copy

def compact_copy(wb, ws):
    blank = {i for i, row in enumerate(ws.iter_rows(values_only=True), 1) if all(is_blank(v) for v in row)}
    new = wb.create_sheet(ws.title + "_clean")
    out = 0
    for row in ws.iter_rows():
        if row[0].row in blank:
            continue
        out += 1
        for cell in row:
            if cell.value is None and not cell.has_style:
                continue
            target = new.cell(out, cell.column, cell.value)
            if cell.has_style:
                target._style = copy(cell._style)
    for key, dim in ws.column_dimensions.items():
        new.column_dimensions[key].width = dim.width
    return new

wb3 = load_workbook("export.xlsx")
clean = compact_copy(wb3, wb3["Export"])
print(clean.title, clean.max_row, [c.value for c in clean[clean.max_row]])

Copying _style carries the font, fill, border, alignment and number format in one assignment, the same technique explained in Copy Cell Styles with openpyxl. The printout shows the catch that applies to both methods: the total row arrived on row 8 still reading =SUM(D2:D11), so regenerate it as in Step 4. Column widths are copied explicitly; heights and merges can be carried over with the mapping from Step 4. Once the copy looks right, remove the original with wb3.remove(wb3["Export"]) and rename the new sheet.

Seconds to remove blank rows from a sheet A column chart comparing grouped delete_rows calls with copying the non-empty rows to a new sheet. On 3,000 rows delete_rows took 1.2 seconds and copying 0.01; on 10,000 rows delete_rows took 14.3 seconds and copying 0.03. grouped delete_rows copy to a new sheet 0 s 5 s 10 s 15 s 3,000 rows 10,000 rows 1.21 14.26 0.01 0.03

Common pitfalls

  • Scanning with ws.cell() or ws[r]. Both create cells; scan with iter_rows(values_only=True).
  • Deleting top-down. After the first deletion every later index is off by one; always go bottom-up.
  • Forgetting the side effects. Heights, widths, merges, conditional-format ranges, validations and formulas stay put; re-apply them or check them after saving.
  • Treating formatted cells as data. A filled-but-empty cell still counts towards max_row; deciding emptiness on values alone is what removes the phantom tail.
  • Running on the only copy. Save to a new file name until the result has been opened and checked.

Conclusion

Find empty rows and columns with a values_only scan, group them into runs and delete from the bottom up with delete_rows and delete_cols; the values and styles move with the rows. Then repair what openpyxl leaves in place — heights, widths, merged ranges and especially formula references — or, for large sheets, copy the non-empty rows to a fresh sheet, which is hundreds of times faster and makes the remapping explicit.

Frequently asked questions

How do I delete blank rows in Excel with openpyxl? Collect the indexes of rows whose values are all None (or whitespace) with ws.iter_rows(values_only=True), group consecutive indexes into runs, and call ws.delete_rows(start, amount) for each run starting from the bottom of the sheet.

Why does openpyxl delete_rows break my formulas? openpyxl does not parse or adjust formula references when rows move. A formula such as =SUM(A2:A10) keeps its text after rows above row 10 are deleted. Rewrite affected formulas yourself, or perform the deletion in Excel or LibreOffice, which update references.

Why is deleting rows with openpyxl so slow? Every delete_rows call shifts all cells below the deletion point, so deleting many separate rows costs roughly rows times deletions. Group consecutive rows into one call, or copy the non-empty rows to a new sheet, which takes a single pass.

Does delete_rows keep cell formatting? Yes for the cells themselves: fonts, fills, borders and number formats move with the rows. Row heights, column widths, merged ranges, conditional formatting ranges and data validation do not move and need to be re-applied.

More on openpyxl

More on openpyxl →