Python Excel AutomationPractical guides for developers
How-to
Advanced Data Transformation And CleaningDeep dive

Highlight Cells Containing Specific Text with openpyxl

Colour every status that mentions "late", every reference that begins with INV, or every note containing any word from a list. Write containsText, beginsWith and formula rules with openpyxl, choose between case-insensitive SEARCH and case-sensitive FIND, match whole words so "late" does not match "calculate", and drive the keyword from a cell.
Conditional formattingopenpyxlValidation & data quality

Status columns, comment fields and reference codes carry meaning in their text: "late", "on hold", "cancelled", a reference that starts with CRN for a credit note. A conditional format that colours those cells turns a column of words into something a reader can scan in a second. openpyxl can write Excel's text rules, but they have one requirement that is easy to miss — a formula the spreadsheet actually evaluates — and one behaviour that surprises almost everyone: "contains late" also matches "calculate". This page, part of Applying Conditional Formatting With openpyxl, writes each kind of text rule, and every result below was checked by rendering the workbook with LibreOffice.

Status cells that mention late or delayed, highlighted A shipments sheet with reference and status columns. A whole-word rule for the words late and delayed highlights Late, late - chase and Delayed at customs. It does not highlight calculate, which contains the letters l-a-t-e inside another word, or on time and cancelled. Whole-word match for 'late' or 'delayed' A B 1 ref status 2 INV-1041 on time 3 INV-1042 Late 4 CRN-0007 late - chase 5 INV-1050 Delayed at customs 6 PO-3 cancelled 7 INV-1060 calculate

Prerequisites

Bash
pip install openpyxl
Python
from openpyxl import load_workbook
from openpyxl.formatting.rule import Rule, FormulaRule
from openpyxl.styles import PatternFill, Font
from openpyxl.styles.differential import DifferentialStyle

AMBER = PatternFill("solid", bgColor="FFEB9C")
FLAG = DifferentialStyle(fill=AMBER, font=Font(color="9C5700", bold=True))

shipments.xlsx has six shipments with references in column A and free-text statuses in column B, including one — "calculate" — planted to show the substring trap.

Step 1 — Write a containsText rule with its formula

Excel's "Text that contains" rule is stored as a containsText rule. openpyxl needs three things: the type and operator, the text (which Excel shows in its rule dialog), and a formula that does the actual test, written for the top-left cell of the range:

Python
wb = load_workbook("shipments.xlsx")
ws = wb["Shipments"]
ws.conditional_formatting.add("B2:B7", Rule(
    type="containsText", operator="containsText", text="late",
    formula=['NOT(ISERROR(SEARCH("late",B2)))'], dxf=FLAG))
wb.save("contains.xlsx")

The formula is the part that counts. The spreadsheet evaluates it for each cell, adjusting B2 down the range; leave it out and the rule exists but never fires. SEARCH returns the position of the text or an error when it is absent, so NOT(ISERROR(...)) is true whenever "late" appears anywhere. Rendered, the rule marks "Late", "late - chase" — and "calculate".

Step 2 — Choose case-insensitive or case-sensitive

SEARCH ignores case, which is why "Late" matched. That is usually right for statuses people type. When case carries meaning — a code where ABC and abc differ — use FIND, which is case-sensitive, in a plain formula rule:

Python
wb = load_workbook("shipments.xlsx")
ws = wb["Shipments"]
ws.conditional_formatting.add("B2:B7", FormulaRule(formula=['ISNUMBER(FIND("Late",B2))'], fill=AMBER))
wb.save("case_sensitive.xlsx")

Only the capitalised "Late" is marked now. ISNUMBER(FIND(...)) and NOT(ISERROR(SEARCH(...))) are interchangeable patterns; ISNUMBER reads a little more directly.

Step 3 — Match whole words

"calculate" contains the letters l-a-t-e, so a plain contains rule marks it. For words, pad both the cell and the keyword with spaces: a whole word is surrounded by spaces in the padded text, and a fragment inside another word is not:

Python
wb = load_workbook("shipments.xlsx")
ws = wb["Shipments"]
ws.conditional_formatting.add("B2:B7", FormulaRule(formula=['ISNUMBER(SEARCH(" late "," "&B2&" "))'], fill=AMBER))
wb.save("whole_word.xlsx")

The padded text of "late - chase" is " late - chase ", which contains " late "; the padded "calculate" does not. The trick treats spaces as the only word boundary, so "late," with a comma still matches only if a space follows the comma. For punctuation-heavy text, substitute punctuation with spaces inside the formula, or flag the rows in pandas with a regular expression and format on the flag.

Which statuses each text rule highlights A grid of the six statuses against four rules. Contains late with SEARCH marks Late, late - chase and calculate. Case-sensitive FIND for Late marks only Late. The whole-word rule marks Late and late - chase but not calculate. The whole-word rule for late or delayed also marks Delayed at customs. None of them mark on time or cancelled. SEARCH "late" FIND "Late" whole word "late" late or delayed on time Late late - chase Delayed at customs cancelled calculate

Step 4 — Match any word from a list

A status column usually has several words that mean trouble. Build an OR of one test per word in Python, so the list lives in your code or configuration rather than in a hand-edited formula:

Python
TROUBLE = ["late", "delayed", "on hold"]
tests = ",".join(f'ISNUMBER(SEARCH(" {w} "," "&B2&" "))' for w in TROUBLE)
formula = f"OR({tests})"
print(formula)

wb = load_workbook("shipments.xlsx")
ws = wb["Shipments"]
ws.conditional_formatting.add("B2:B7", FormulaRule(formula=[formula], fill=AMBER))
wb.save("any_word.xlsx")

Spelling out one SEARCH per word is deliberate. The shorter form with an array constant, OR(ISNUMBER(SEARCH({"late","delayed"},B2))), did not evaluate as an array inside a conditional format in testing — it matched only the first keyword — while the explicit OR matched all three statuses. Multi-word phrases such as "on hold" work with the same padding, as long as the words in the cell are separated by single spaces. Escape any double quote in a keyword by doubling it before building the formula.

Step 5 — Match the start or end of the text

References and codes are better matched by position than by content. beginsWith and endsWith rules exist for this, and again carry their own formula:

Python
wb = load_workbook("shipments.xlsx")
ws = wb["Shipments"]
ws.conditional_formatting.add("A2:A7", Rule(
    type="beginsWith", operator="beginsWith", text="CRN",
    formula=['LEFT(A2,LEN("CRN"))="CRN"'], dxf=FLAG))
ws.conditional_formatting.add("B2:B7", Rule(
    type="notContainsText", operator="notContains", text="late",
    formula=['ISERROR(SEARCH("late",B2))'], dxf=DifferentialStyle(font=Font(color="808080"))))
wb.save("starts_with.xlsx")

The first rule marks credit notes (CRN-0007) in the reference column; the second greys out every status that does not mention "late", which draws the eye to the ones that do by making the rest quieter. For endsWith, the formula is RIGHT(A2,LEN("X"))="X". Highlight an Entire Row Based on a Cell Value with openpyxl shows how to colour the whole shipment row instead of one cell.

Step 6 — Let readers choose the keyword

A small search box — a keyword typed into D1 that highlights matching statuses — makes a report interactive without any code on the reader's side. Refer to the cell absolutely, and guard against an empty box, because an empty search string matches everything:

Python
wb = load_workbook("shipments.xlsx")
ws = wb["Shipments"]
ws["D1"] = "late"
ws["C1"] = "Highlight:"
ws.conditional_formatting.add("B2:B7", FormulaRule(
    formula=['AND($D$1<>"",ISNUMBER(SEARCH($D$1,B2)))'], fill=AMBER))
wb.save("search_box.xlsx")

With "late" in D1, the matching statuses light up; clear D1 and the highlighting disappears instead of covering the column. Pair it with a dropdown of common keywords — Add Dropdown Data Validation to Excel with openpyxl — and readers can switch between views with one click.

How a text rule decides whether to colour a cell Four steps. The spreadsheet takes the rule formula written for the top-left cell. It adjusts relative references for each cell in the range, so B2 becomes B3, B4 and so on. It evaluates SEARCH or FIND against that cell's text. If the result is true, the rule's style is applied over the cell's own formatting. Rule formula written for B2 Adjust per cell B2 → B3 → B4 … Evaluate SEARCH / FIND on that text Apply style when the result is TRUE

Apply one rule to several columns

Real sheets often have the same keyword problem in several places — a status column, a notes column, a free-text reason on another sheet. Each range needs a formula anchored on its own top-left cell, which is easy to get wrong when copying rules by hand. A helper that derives the anchor from the range keeps it right:

Python
from openpyxl.utils.cell import range_boundaries
from openpyxl.utils import get_column_letter

def highlight_words(ws, cell_range, words, fill=AMBER):
    min_col, min_row, _, _ = range_boundaries(cell_range)
    anchor = f"{get_column_letter(min_col)}{min_row}"
    tests = ",".join(f'ISNUMBER(SEARCH(" {w.replace(chr(34), chr(34) * 2)} "," "&{anchor}&" "))' for w in words)
    ws.conditional_formatting.add(cell_range, FormulaRule(formula=[f"OR({tests})"], fill=fill))
    return anchor

wb = load_workbook("shipments.xlsx")
ws = wb["Shipments"]
ws["C1"] = "note"
ws["C3"] = "customer says delayed"
print(highlight_words(ws, "B2:B7", TROUBLE), highlight_words(ws, "C2:C7", TROUBLE))
wb.save("several_columns.xlsx")
print([(str(cf.sqref), cf.rules[0].formula[0][:32]) for cf in ws.conditional_formatting])

Each range gets its own anchor — B2 for the status column, C2 for the notes — so every cell tests itself rather than the column next to it. Doubling any " in a keyword keeps the formula valid if the list ever contains a quoted phrase.

Common pitfalls

  • No formula on a text rule. The rule is stored but never fires.
  • Substring matches. "late" matches "calculate"; pad with spaces for whole words.
  • Absolute references in the tested cell. $B$2 makes every cell test B2; use B2 (or $B2 for row rules).
  • Empty keyword cells. Guard with $D$1<>"", or an empty box highlights everything.
  • Array constants in rule formulas. Build explicit OR(...) terms in Python instead.

Performance notes

Text rules are evaluated per cell by the spreadsheet on every recalculation. A handful of SEARCH calls per cell is negligible for thousands of rows; for very long lists of keywords on large sheets, classify the rows in pandas with str.contains and a regular expression, write a category column, and use one simple rule on it.

Conclusion

Write text rules with openpyxl as containsText, notContainsText, beginsWith or endsWith rules — always with a formula that does the test — or as plain formula rules. Use SEARCH for case-insensitive and FIND for case-sensitive matching, pad with spaces to match whole words, build OR expressions in Python for keyword lists, and point the formula at a cell to let readers choose the keyword. Render the workbook once to confirm the rule marks exactly the cells you meant.

Frequently asked questions

Why does my containsText rule highlight nothing? openpyxl writes the rule's type and text, but the spreadsheet evaluates its formula. Without a formula such as NOT(ISERROR(SEARCH("late",B2))) the rule has nothing to test. Always pass the formula alongside the text.

Is text matching case-sensitive? SEARCH, which Excel's own "Text that contains" rule uses, ignores case. For a case-sensitive match, write a formula rule with FIND instead, for example ISNUMBER(FIND("Late",B2)).

How do I stop "late" from matching "calculate"? Match whole words by padding both sides with spaces: ISNUMBER(SEARCH(" late "," "&B2&" ")). A word surrounded by spaces in the padded text is a whole word; a word inside another word is not.

Can the keyword come from a cell? Yes. Refer to it with an absolute reference such as $D$1 in the formula, and guard against an empty keyword with AND($D$1<>"", ...), otherwise every cell matches.

More on conditional formatting

More on conditional formatting →