Clear Cell Contents and Formatting with openpyxl

Excel's Home → Clear menu offers five choices — Clear All, Clear Formats, Clear Contents, Clear Comments and Notes, Clear Hyperlinks — because "clear this range" means different things in different situations. A script that refills a weekly template wants to wipe last week's numbers but keep the yellow input cells and the formulas; a script tidying an export wants the opposite. openpyxl has no single clear() method, but each of Excel's options maps onto one or two attribute assignments. This page, part of Using openpyxl for Excel File Manipulation, shows each one, then builds a helper for the template case and explains the merged-cell trap.

Prerequisites

pip install openpyxl

The examples use template.xlsx: a merged title in A1:D1, a header row, three item rows with yellow input cells in B and C, =B*C formulas in column D, a comment on B3 and a hyperlink on A4.

from openpyxl import load_workbook
from openpyxl.cell.cell import MergedCell

def state(cell):
    return {"value": cell.value, "fill": cell.fill.fgColor.rgb if cell.fill.fill_type else None,
            "comment": cell.comment is not None, "link": cell.hyperlink is not None}

Step 1 — Clear contents, keep formatting

Setting the value to None is Excel's Clear Contents. Everything else about the cell stays:

wb = load_workbook("template.xlsx")
ws = wb["Input"]
ws["B3"].value = None
print(state(ws["B3"]))
# {'value': None, 'fill': '00FFF2CC', 'comment': True, 'link': False}

The yellow fill and the comment survive. That is exactly the behaviour a template needs, and it is why value = None is the right default for "clear this range".

Step 2 — Clear formatting, keep values

Assigning the built-in "Normal" named style resets every formatting attribute at once — font, fill, border, alignment, protection and number format — to the workbook's default:

ws["C3"].style = "Normal"
print(state(ws["C3"]), ws["C3"].has_style)
# {'value': 3.5, 'fill': None, 'comment': False, 'link': False} False

has_style becoming False means the cell now carries no formatting of its own. Resetting attributes one by one (cell.font = Font(), cell.fill = PatternFill(), and so on) also works but is easy to leave incomplete — a forgotten border or number format survives. The named style is one assignment and cannot miss anything.

Step 3 — Remove comments and hyperlinks

Comments and hyperlinks are not formatting, so neither of the steps above touches them. Clear them explicitly:

ws["B3"].comment = None
ws["A4"].hyperlink = None
print(state(ws["B3"]), state(ws["A4"]))

When a hyperlink is removed this way, the cell keeps the hyperlink style (blue, underlined) if one was applied. Follow it with style = "Normal" when the cell should look like plain text again.

Step 4 — Clear a range, skipping merged cells

Clearing a whole range means looping over it. The obvious loop fails as soon as the range includes a merged block, because covered cells are MergedCell objects that refuse any assignment, even None:

wb = load_workbook("template.xlsx")
ws = wb["Input"]
try:
    for row in ws["A1:D1"]:
        for cell in row:
            cell.value = None
except AttributeError as exc:
    print(exc)
# 'MergedCell' object attribute 'value' is read-only

Skip placeholders; the merge's top-left cell holds the only value there is:

def clear_range(ws, cell_range, contents=True, formats=False, comments=False, links=False):
    for row in ws[cell_range]:
        for cell in row:
            if isinstance(cell, MergedCell):
                continue
            if contents:
                cell.value = None
            if comments:
                cell.comment = None
            if links:
                cell.hyperlink = None
            if formats:
                cell.style = "Normal"

clear_range(ws, "A1:D1")
print(ws["A1"].value, ws.merged_cells.ranges)
# None {<MergedCellRange A1:D1>}

The merge itself stays, which is usually right for a template. If the merged block should go too, call ws.unmerge_cells("A1:D1") before clearing. Fix "MergedCell object attribute value is read-only" in openpyxl explains the placeholders in detail.

Step 5 — Clear a template's inputs but keep its formulas

The most common real task is resetting a template for the next run: every typed number goes, every formula stays. Excel users do this with Go To Special → Constants; in openpyxl, check whether each value is a formula before clearing it:

def clear_inputs(ws, cell_range):
    cleared = 0
    for row in ws[cell_range]:
        for cell in row:
            if isinstance(cell, MergedCell) or cell.value is None:
                continue
            if isinstance(cell.value, str) and cell.value.startswith("="):
                continue                     # keep formulas
            cell.value = None
            cleared += 1
    return cleared

wb = load_workbook("template.xlsx")
ws = wb["Input"]
print(clear_inputs(ws, "A3:D5"), "cells cleared")
print([[c.value for c in row] for row in ws["A3:D5"]])
wb.save("template_reset.xlsx")

Nine constants go; the three =B*C formulas remain, and the yellow fills stay on the input cells so the next person knows where to type. Load the workbook without data_only=True for this — with it, formulas are replaced by their cached results, the check cannot see them, and saving would destroy them. Fill an Excel Template with Python and openpyxl covers the filling half of the cycle.

Before overwriting last week's file, it is worth keeping a record of what was cleared. A template reset that runs on the wrong workbook — last month's final numbers instead of this week's scratch copy — is otherwise unrecoverable. Collect the old values as you clear them and write them somewhere safe:

import csv

def clear_inputs_logged(ws, cell_range, log_path):
    rows = []
    for row in ws[cell_range]:
        for cell in row:
            if isinstance(cell, MergedCell) or cell.value is None:
                continue
            if isinstance(cell.value, str) and cell.value.startswith("="):
                continue
            rows.append({"sheet": ws.title, "cell": cell.coordinate, "old_value": cell.value})
            cell.value = None
    with open(log_path, "w", newline="", encoding="utf-8") as fh:
        writer = csv.DictWriter(fh, fieldnames=["sheet", "cell", "old_value"])
        writer.writeheader()
        writer.writerows(rows)
    return len(rows)

wb = load_workbook("template.xlsx")
print(clear_inputs_logged(wb["Input"], "A3:D5", "cleared_values.csv"))
print(open("cleared_values.csv").read().splitlines()[:3])

The CSV is enough to put any cell back by hand, and the count is a useful sanity check in the job log: a reset that suddenly clears 900 cells instead of 9 is pointing at the wrong range.

Step 6 — Shrink the used range after clearing everything

A cell with no value but a style is still a cell as far as the file is concerned, so clearing contents alone leaves max_row where it was. Clearing formats as well lets openpyxl drop the cells when it saves:

wb = load_workbook("template.xlsx")
ws = wb["Input"]
print(ws.max_row)
clear_range(ws, "A3:D5", contents=True, formats=True, comments=True, links=True)
wb.save("cleared.xlsx")
print(load_workbook("cleared.xlsx")["Input"].max_row)
# 2 — only the title and header rows remain

When whole rows are being emptied, ws.delete_rows(3, 3) does the same job in one call and also removes row heights, but it shifts everything below — fine at the bottom of a sheet, disruptive in the middle of a report. Fix openpyxl max_row Counting Empty Rows covers the used range in detail.

What clearing does not touch

Some features are stored for ranges, not cells, and survive every step on this page: conditional formatting rules, data validation (dropdowns), Excel tables, defined names and print areas. Clearing A3:D5 leaves a dropdown rule on B3:B5 in place, which is usually what a template wants. When they must go, remove them through their own collections — List and Remove Conditional Formatting with openpyxl shows how for formatting rules.

Common pitfalls

• Expecting value = None to remove formatting. It clears contents only.
• Clearing with data_only=True. The workbook's formulas become cached values; saving loses them.
• Forgetting merged cells. Skip MergedCell objects or unmerge first.
• Resetting attributes one at a time. style = "Normal" is complete; hand-built resets miss borders or number formats.
• Clearing comments by clearing contents. Comments and links need their own assignments.

Performance notes

Clearing is a per-cell loop and handles tens of thousands of cells per second. For very large areas, reconsider the approach: if the whole data region is being replaced, deleting the rows or writing into a fresh copy of the template is faster than visiting every cell.

Conclusion

cell.value = None is Clear Contents, cell.style = "Normal" is Clear Formats, and cell.comment = None and cell.hyperlink = None clear notes and links. Loop over a range with a helper that skips MergedCell placeholders, clear only constants when resetting a template so its formulas survive, and reset formats too when the used range should shrink. Range-level features — conditional formats, validation, tables — need their own removal.

Frequently asked questions

Does setting cell.value = None remove formatting? No. It clears the value only. Font, fill, borders, number format, comments and hyperlinks stay, which is what you want when refilling a template.

How do I remove all formatting from a cell? Assign cell.style = "Normal". That resets font, fill, border, alignment, protection and number format to the workbook's default style. Comments and hyperlinks are separate and must be cleared on their own.

Why does my clearing loop raise a MergedCell error? The range includes cells covered by a merge, and those cannot be assigned a value, not even None. Skip MergedCell objects in the loop, or unmerge the range first.

Why does max_row stay large after clearing? Cells that still carry a style count as used. Resetting the style to Normal, or deleting the rows, lets openpyxl drop them, and max_row shrinks after the file is saved and reloaded.

Related

• Up one level: Using openpyxl for Excel File Manipulation — editing cells and ranges.
• Copy Cell Styles from One Cell to Another with openpyxl — the opposite of clearing formats.
• Populate an Excel Template Without Losing Formatting — refilling after a reset.
• Move a Range of Cells with openpyxl move_range — relocating blocks instead of clearing them.