Find and Replace Text in Excel with openpyxl

Ctrl+H is fine for one workbook. It is tedious for forty templates after a company rename, error-prone when only whole-cell matches should change, and silent about what it touched. A short openpyxl script does the same job across any number of files, keeps every cell's formatting, can leave formulas alone, and writes a log of each change for someone to review. This guide, part of Using openpyxl for Excel File Manipulation, builds that script step by step — including the one detail that quietly destroys formatting if you miss it.

Prerequisites

pip install openpyxl

This builds a small workbook with the kinds of cells a real replacement meets — plain text, a whole-cell match, a formula, a number, and a cell whose text is partly bold:

from openpyxl import Workbook
from openpyxl.cell.rich_text import CellRichText, TextBlock
from openpyxl.cell.text import InlineFont
from openpyxl.styles import Font, PatternFill

wb = Workbook()
ws = wb.active
ws.title = "Cover"
ws["A1"] = "Acme Ltd — Quarterly Report"
ws["A1"].font = Font(size=16, bold=True)
ws["A2"] = CellRichText(["Prepared by ", TextBlock(InlineFont(b=True), "Acme Ltd"), " finance"])
ws["A3"] = "Acme"
ws["A3"].fill = PatternFill("solid", start_color="FFEB9C")
ws["A4"] = '=CONCAT("Acme", " Ltd")'
ws["A5"] = 2025
data = wb.create_sheet("Data")
data.append(["Customer", "Supplier"])
data.append(["Northwind", "Acme Ltd"])
wb.save("report_template.xlsx")

Step 1 — Load with rich text preserved

This is the step that matters most. By default openpyxl flattens a cell with mixed formatting — a bold word inside normal text — into a plain string, and saving the workbook writes it back without the bold. Nothing warns you. Load with rich_text=True and such cells come back as CellRichText objects whose formatted runs survive the round trip.

from openpyxl import load_workbook

plain = load_workbook("report_template.xlsx")
print(type(plain["Cover"]["A2"].value).__name__, repr(plain["Cover"]["A2"].value))   # str — bold lost

wb = load_workbook("report_template.xlsx", rich_text=True)
print(type(wb["Cover"]["A2"].value).__name__)                                          # CellRichText

Whole-cell styles — the font of A1, the fill of A3 — are unaffected either way, because they belong to the cell rather than to its text. Assigning a new string to cell.value keeps them.

Step 2 — Replace substrings in text cells

Walk every worksheet with iter_rows(), touch only string values, and skip anything that starts with = — that is a formula, and a blind replacement inside it can break a reference such as a sheet name. Return a list of changes rather than printing them, so the caller decides what to do with the record.

def replace_text(cell, old: str, new: str) -> bool:
    value = cell.value
    if isinstance(value, CellRichText):
        changed = False
        for index, part in enumerate(value):
            if isinstance(part, str):
                if old in part:
                    value[index] = part.replace(old, new)
                    changed = True
            elif old in part.text:
                part.text = part.text.replace(old, new)
                changed = True
        if changed:
            cell.value = value
        return changed
    if isinstance(value, str) and not value.startswith("=") and old in value:
        cell.value = value.replace(old, new)
        return True
    return False

def find_and_replace(wb, old: str, new: str) -> list[dict]:
    changes = []
    for ws in wb.worksheets:
        for row in ws.iter_rows():
            for cell in row:
                before = str(cell.value)
                if replace_text(cell, old, new):
                    changes.append({"sheet": ws.title, "cell": cell.coordinate,
                                    "before": before, "after": str(cell.value)})
    return changes

wb = load_workbook("report_template.xlsx", rich_text=True)
for change in find_and_replace(wb, "Acme Ltd", "Contoso Limited"):
    print(change)
wb.save("report_contoso.xlsx")

Rich text is replaced run by run, which keeps each run's formatting. The limitation is that a search string split across two runs — "Acme" in normal text and "Ltd" in bold — will not match, because no single run contains it. That is rare for names and codes, and the change log makes any miss easy to spot.

Step 3 — Match whole cells only

Substring replacement is wrong for codes and short names. Replacing Acme with Contoso everywhere would also turn Acme Ltd into Contoso Ltd and a customer called Acmeworks into Contosoworks. When the value in a cell is the thing being renamed, compare the whole stripped value instead.

def replace_whole(wb, mapping: dict[str, str]) -> list[tuple[str, str, str]]:
    changes = []
    for ws in wb.worksheets:
        for row in ws.iter_rows():
            for cell in row:
                if isinstance(cell.value, str) and cell.value.strip() in mapping:
                    old = cell.value
                    cell.value = mapping[old.strip()]
                    changes.append((ws.title, cell.coordinate, old))
    return changes

wb = load_workbook("report_template.xlsx", rich_text=True)
print(replace_whole(wb, {"Acme": "Contoso", "Northwind": "Northwind Traders"}))

A dictionary makes a bulk rename — dozens of old codes to new codes — a single pass through the workbook. Load the mapping from a CSV maintained by whoever owns the rename, rather than hard-coding it.

Step 4 — Use regular expressions for patterns

Some replacements are patterns rather than literals: every FY2025 becoming FY2026, or product codes of the form AB-1234 gaining a new prefix. re.subn returns the new text and the number of substitutions, which doubles as the "did anything change" test.

import re

def regex_replace(wb, pattern: str, replacement: str) -> int:
    compiled = re.compile(pattern)
    total = 0
    for ws in wb.worksheets:
        for row in ws.iter_rows():
            for cell in row:
                if isinstance(cell.value, str) and not cell.value.startswith("="):
                    new_value, count = compiled.subn(replacement, cell.value)
                    if count:
                        cell.value = new_value
                        total += count
    return total

wb = load_workbook("report_template.xlsx", rich_text=True)
print(regex_replace(wb, r"\bAcme\b(?! Ltd)", "Contoso"))     # only a bare "Acme"

The negative lookahead in that pattern replaces Acme only when it is not followed by Ltd — the kind of rule that is impossible in Excel's dialog. Test a pattern on a copy and read the change log before running it across a folder.

Step 5 — Replace inside formulas deliberately

Formulas were skipped so far, and usually that is right. When the rename should reach formulas — a sheet called Acme Data referenced as 'Acme Data'!B2 — treat it as a separate, explicit step that only touches the text you intend.

def replace_in_formulas(wb, old: str, new: str) -> list[str]:
    touched = []
    for ws in wb.worksheets:
        for row in ws.iter_rows():
            for cell in row:
                if isinstance(cell.value, str) and cell.value.startswith("=") and old in cell.value:
                    cell.value = cell.value.replace(old, new)
                    touched.append(f"{ws.title}!{cell.coordinate}")
    return touched

wb = load_workbook("report_template.xlsx", rich_text=True)
print(replace_in_formulas(wb, '"Acme"', '"Contoso"'))

Renaming a sheet itself is ws.title = "New name", but openpyxl does not update formulas that refer to the old name — that is exactly what this step is for. Formula cells saved by openpyxl have no cached result until the workbook is recalculated, as explained in Read Formula Results with openpyxl data_only.

Step 6 — Include headers, footers and sheet names

Text also hides outside cells. Page headers and footers print on every page of a PDF export, and sheet names appear on every tab. A thorough rename checks them too.

def replace_outside_cells(wb, old: str, new: str) -> list[str]:
    touched = []
    for ws in wb.worksheets:
        if old in ws.title:
            ws.title = ws.title.replace(old, new)
            touched.append(f"sheet name -> {ws.title}")
        for part in (ws.oddHeader, ws.oddFooter):
            for section in (part.left, part.center, part.right):
                if section.text and old in section.text:
                    section.text = section.text.replace(old, new)
                    touched.append(f"{ws.title} header/footer")
    return touched

wb = load_workbook("report_template.xlsx", rich_text=True)
wb["Cover"].oddFooter.center.text = "Acme Ltd — confidential"
print(replace_outside_cells(wb, "Acme Ltd", "Contoso Limited"))

Headers and footers are set up in Set the Print Area and Page Setup with openpyxl. Chart titles, text boxes and comments are further places text can live; openpyxl exposes comments on each cell as cell.comment, while shapes and text boxes are not loaded at all.

Common pitfalls

Performance and scale notes

A full load_workbook holds every cell in memory, and a replacement pass is a Python loop over all of them — about a second for tens of thousands of cells, and proportionally longer for large data sheets. Restrict the walk to the sheets that can contain the text, or to a column range with min_col and max_col. For a folder of templates, process files one at a time and write each result to a new path, so a failure halfway through never leaves a half-renamed original.

Conclusion

Load with rich_text=True, walk the cells, and change only string values that are not formulas — assigning cell.value keeps each cell's style, and replacing rich text run by run keeps partial formatting. Choose substring, whole-cell or regex matching deliberately, treat formulas, sheet names, headers and footers as separate explicit steps, and log every change. Save to a new file and review the log before replacing the originals.

Frequently asked questions

Does openpyxl keep cell formatting when I change a value? Yes. Fonts, fills, borders and number formats belong to the cell, and assigning a new value leaves them in place. Only formatting inside the text of a rich text cell needs rich_text=True.

Can I do a case-insensitive replace? Yes. Compile the pattern with re.IGNORECASE and use subn, as in the regex step. Keep the replacement text in the case you want to appear.

Will this work on .xlsm files with macros? Load with keep_vba=True and save with the .xlsm extension, and the VBA project is carried through. Test on a copy, because some other parts such as form controls are not preserved.

How do I replace text in many files at once? Loop over the files with pathlib, run the same function on each workbook, save to an output folder, and collect the change logs into one CSV for review.

Related

• Up one level: Using openpyxl for Excel File Manipulation — the core workbook editing techniques.
• Iterate over Rows and Columns with openpyxl — the traversal patterns used here.
• Find the Last Row with Data in openpyxl — limiting the walk to real data.
• Fill an Excel Template with Python and openpyxl — writing values into templates rather than replacing them.
• Replace Placeholders in an Excel Template with Python — the placeholder-driven version of this technique.