Highlight Blank Cells in Excel with openpyxl

A contact list, an order import or a timesheet is only as good as its required fields, and a missing email in row 412 is easy to miss in a sheet of a thousand rows. Highlighting blanks makes the gaps visible to whoever fills the sheet in, and it keeps working as they fix them. The rule looks trivial, but "blank" has three meanings in a spreadsheet — no content, only spaces, and a formula that returns nothing — and the obvious rule also lights up every empty row below the data. This page, part of Applying Conditional Formatting With openpyxl, writes rules that catch the right cells; each result was checked by rendering the workbook with LibreOffice.

Prerequisites

pip install openpyxl

contacts.xlsx has four contacts: one complete, one with no email, one whose email is three spaces and whose phone is missing, and one with an empty-string phone. Column D is a formula that returns "" when there is no phone. A note in F7 marks the end of the used area so there are empty rows below the data.

from openpyxl import load_workbook
from openpyxl.formatting.rule import Rule, FormulaRule
from openpyxl.styles import PatternFill
from openpyxl.styles.differential import DifferentialStyle

AMBER = PatternFill("solid", bgColor="FFEB9C")

Step 1 — Know which cells count as blank

The two common tests give different answers on the same cells. Rendering the sheet with each rule applied to B2:D7 showed:

For required-field checks, LEN(TRIM(B2))=0 is almost always the right test: a person who typed a space into the email cell has not provided an email, and a lookup formula that returns "" has not found anything. Use ISBLANK only when you specifically want cells that have never been touched.

Step 2 — Write a containsBlanks rule

Excel's "Format only cells that contain Blanks" is stored as a containsBlanks rule. Like the other text-style rules, it needs a formula to evaluate:

wb = load_workbook("contacts.xlsx")
ws = wb["Contacts"]
ws.conditional_formatting.add("B2:D7", Rule(
    type="containsBlanks", formula=["LEN(TRIM(B2))=0"], dxf=DifferentialStyle(fill=AMBER)))
wb.save("blanks_basic.xlsx")

The rule marks the missing email, the spaces-only email, the missing and empty-string phones, and the empty region codes. It also marks rows 6 and 7 from B to D, which are empty because there is no data there at all. On a sheet formatted down to row 1,000 for future entries, that is a solid block of amber below the table — technically correct, and useless.

Step 3 — Only check rows that contain data

Tie the test to a column that is always filled in a real record — the ID, a date, a name — so rows without one are skipped:

wb = load_workbook("contacts.xlsx")
ws = wb["Contacts"]
ws.conditional_formatting.add("B2:D1000", FormulaRule(
    formula=['AND(LEN(TRIM(B2))=0,$A2<>"")'], fill=AMBER))
wb.save("blanks_in_data.xlsx")

The $ before A makes every cell in a row look at that row's ID; the rule can then cover a generous range, down to row 1,000, and new records typed at the bottom are checked automatically without colouring the empty rows in between. Rendered, only the six cells inside the four records are highlighted.

Step 4 — Highlight the whole row when a required field is missing

For a data-entry sheet, colouring the whole record is often clearer than colouring one cell. Apply the rule to the full row range and test the required columns with COUNTBLANK:

wb = load_workbook("contacts.xlsx")
ws = wb["Contacts"]
ws.conditional_formatting.add("A2:D1000", FormulaRule(
    formula=['AND($A2<>"",COUNTBLANK($B2:$C2)>0)'], fill=PatternFill("solid", bgColor="FCE4D6")))
wb.save("rows_with_gaps.xlsx")

Records 2, 3 and 4 are coloured from A to D; record 1 is complete and stays plain. COUNTBLANK counts empty cells and cells holding an empty string. Which columns count as required is a business decision: here email and phone, but not the derived region code, which is blank whenever the phone is and would only double-count.

Step 5 — Show readers why a row is marked

Colour says "something is wrong here"; a note says what. Add a status column that spells out the missing fields with a formula, so the sheet explains itself when printed or read by someone who cannot distinguish the highlight colour:

wb = load_workbook("contacts.xlsx")
ws = wb["Contacts"]
ws["E1"] = "missing"
for row in range(2, 6):
    ws[f"E{row}"] = (f'=TRIM(IF(LEN(TRIM(B{row}))=0,"email ","")'
                     f'&IF(LEN(TRIM(C{row}))=0,"phone",""))')
ws.conditional_formatting.add("B2:D1000", FormulaRule(formula=['AND(LEN(TRIM(B2))=0,$A2<>"")'], fill=AMBER))
wb.save("blanks_explained.xlsx")
print(ws["E4"].value)

Once the workbook is calculated, the new column reads "email" for record 2, "email phone" for record 3 and "phone" for record 4. Combining a highlight with a text reason is also the more accessible design — colour alone should never be the only signal. Highlight Invalid Cells in Excel with Python builds on the same idea for other kinds of invalid data.

Add a live count of missing fields

A highlight shows where the gaps are; a count at the top of the sheet shows how many are left, and it falls to zero as people fill them in. SUMPRODUCT can apply the same LEN(TRIM()) test to a whole block and count only rows that have an ID:

import subprocess

wb = load_workbook("contacts.xlsx")
ws = wb["Contacts"]
ws["H1"] = "Missing fields"
ws["H2"] = '=SUMPRODUCT((LEN(TRIM(B2:C1000))=0)*(A2:A1000<>""))'
wb.save("with_counter.xlsx")

subprocess.run(["soffice", "--headless", "--convert-to", "xlsx", "--outdir", "calc", "with_counter.xlsx"],
               check=True, capture_output=True, timeout=120)
print(load_workbook("calc/with_counter.xlsx", data_only=True)["Contacts"]["H2"].value)
# 4

Recalculated, the counter reads 4: two emails and two phones. Put the counter next to a short instruction — "fill the amber cells until this reads 0" — and a data-entry sheet explains its own job. Multiplying the two conditions acts as an AND across the two ranges: a cell counts only when it is empty and its row has an ID. Keep the counter's ranges the same size as the rule's range, or the number and the highlighting will disagree once the data grows past one of them.

Blanks in pandas before they reach the sheet

When the workbook is produced from a DataFrame, you can also find the gaps before writing and report them, instead of only colouring them:

import pandas as pd

contacts = pd.read_excel("contacts.xlsx", usecols="A:C")
contacts = contacts.dropna(subset=["id"])
text = contacts[["email", "phone"]].astype("string").apply(lambda s: s.str.strip())
missing = text.isna() | (text == "")
print(missing.sum().to_dict())
print(contacts.loc[missing.any(axis=1), "id"].astype(int).tolist())

The counts — two missing emails and two missing phones — match the highlighted cells. Using both is common: pandas for a summary in the job log or a quality sheet, conditional formatting for the people fixing records in the workbook. Find and Report Missing Values in an Excel File turns the pandas side into a full report.

Common pitfalls

• Using ISBLANK for required fields. Spaces and "" formulas slip through.
• Rules over empty rows. Add a condition on an always-filled column.
• Unlocked column references in row rules. COUNTBLANK(B2:C2) without $ shifts across the row and tests the wrong cells.
• Treating derived columns as required. A formula column that is blank because its input is blank double-counts the same gap.

Performance notes

Blank-checking rules are cheap per cell, and covering a range of a thousand rows ahead of the data costs nothing noticeable. For sheets of hundreds of thousands of rows, prefer a single status column computed once and a simple rule on it, rather than several COUNTBLANK rules recalculating across the whole sheet.

Conclusion

Use LEN(TRIM(B2))=0 rather than ISBLANK so that spaces and formulas returning "" count as missing, write it as a containsBlanks rule or a formula rule, and add $A2<>"" so only rows with data are checked. Colour whole records with COUNTBLANK over the required columns, add a text column that names what is missing, and use pandas to count the same gaps for the job's own report.

Frequently asked questions

Why does ISBLANK not highlight a cell that looks empty? ISBLANK is true only for a cell with no content at all. A cell holding spaces, or a formula that returns an empty string, is not blank to ISBLANK. Use LEN(TRIM(B2))=0 to treat all of those as empty.

Why is every empty row under my table highlighted? The rule covers a range that extends past the data, and those cells are genuinely empty. Add a condition on a column that is always filled in real rows, such as AND(LEN(TRIM(B2))=0,$A2<>""), so only rows with an ID are checked.

How do I highlight the whole row when any required field is missing? Apply a formula rule to the full row range and test the required columns with COUNTBLANK, locking the column letters with dollar signs so every cell in the row checks the same columns, as shown in Step 4.

Should I use the containsBlanks rule type or a formula rule? Either works, and both need a formula. containsBlanks shows up as "Format only cells that contain Blanks" in Excel's rule manager, which some users find clearer; a plain formula rule is more flexible when you add conditions.

Related

• Up one level: Applying Conditional Formatting With openpyxl — every rule type.
• Drop Rows with Missing Required Fields in pandas — removing incomplete records instead of flagging them.
• Highlight Cells Containing Specific Text with openpyxl — the text-rule counterpart.
• Control How NaN, None and Infinity Appear in Excel Output — how gaps are written in the first place.