Tell Blank Cells, Empty Strings and Zeros Apart in Excel Data

In a stock-count sheet, a blank in the "Counted" column means nobody counted that shelf, and a zero means somebody counted it and found nothing. Those are opposite facts, and a report that confuses them either orders stock that is already there or misses a stock-out. The trouble is that "looks blank" covers several different cell contents in Excel — a truly empty cell, a formula that returns "", a cell holding a single space, a zero hidden by a custom number format — and pd.read_excel folds most of them into the same NaN. This page shows what each case really contains, what pandas and openpyxl return for it, and how to classify cells so the distinction survives into your report. It belongs to Handling Missing Data in Excel Reports.

Prerequisites

pip install pandas openpyxl

stock_count.xlsx has one row per item with a Counted column. In Excel, four of the nine counts look empty and two more show a placeholder. Behind them: Bolts M8 is truly empty; Washers holds a formula that returns an empty string (the kind a failed lookup wrapped in IFERROR(…, "") produces); Nuts M6 holds a single space; Brackets holds a real zero formatted with 0;-0;;@, whose empty third section hides zeros; and Hinges and Clamps hold the placeholders - and N/A. Nuts M8 is a genuine visible zero and Rivets a zero typed as text.

Step 1 — See what the default read returns

import pandas as pd

df = pd.read_excel("stock_count.xlsx")
df["repr"] = df["Counted"].map(repr)
print(df.to_string(index=False))

    Item Counted repr
Bolts M6      40   40
Bolts M8     NaN  nan
 Washers     NaN  nan
 Nuts M6          ' '
 Nuts M8       0    0
  Rivets       0  '0'
Brackets       0    0
  Hinges       -  '-'
  Clamps     NaN  nan

Three different situations — never counted, a lookup that found nothing, and an explicit "not applicable" — all became NaN. N/A is on pandas' default list of missing-value markers, so it disappears without trace. Meanwhile the space survived as a one-character string, Rivets is the string '0' sitting in the same column as the numbers, and the hidden zero on Brackets is an ordinary 0, because number formats only change what Excel displays.

Step 2 — Turn off the automatic NA conversion

keep_default_na=False stops pandas from replacing marker strings and blanks with NaN, so every cell comes through as what it is:

raw = pd.read_excel("stock_count.xlsx", keep_default_na=False)
raw["repr"] = raw["Counted"].map(repr)
print(raw.to_string(index=False))

Now N/A and - are visible as text, and the two empty-looking cells come back as ''. That is progress, but notice that the truly empty Bolts M8 and the formula-returning-empty Washers are both '' — pandas cannot tell them apart, because neither has a value to read. Do not combine this with dtype=str while investigating: that turns the numeric zeros into '0' too, and the difference between Nuts M8 (a number) and Rivets (text) is lost.

Step 3 — Ask openpyxl what each cell holds

openpyxl exposes the information pandas discards: whether a cell has a formula, its stored data type, and its number format. Load the workbook twice — once for formulas, once for the cached results — and classify each cell:

from openpyxl import load_workbook

formulas = load_workbook("stock_count.xlsx")["Count"]
values = load_workbook("stock_count.xlsx", data_only=True)["Count"]
PLACEHOLDERS = {"-", "n/a", "na", "none", "null", "tbc"}

def classify(f_cell, v_cell):
    v = v_cell.value
    if isinstance(f_cell.value, str) and f_cell.value.startswith("="):
        return "formula, empty result" if v in (None, "") else "formula"
    if v is None:
        return "empty"
    if isinstance(v, str):
        if v.strip() == "":
            return "whitespace only"
        if v.strip().lower() in PLACEHOLDERS:
            return "placeholder text"
        if v.strip().lstrip("-").replace(".", "", 1).isdigit():
            return "number stored as text"
        return "text"
    if v == 0 and v_cell.number_format.split(";")[2:3] == [""]:
        return "zero, hidden by format"
    return "zero" if v == 0 else "number"

report = [(f[0].value, classify(f[1], v[1]))
          for f, v in zip(formulas.iter_rows(min_row=2), values.iter_rows(min_row=2))]
for item, kind in report:
    print(f"{item:10} {kind}")

Bolts M6   number
Bolts M8   empty
Washers    formula, empty result
Nuts M6    whitespace only
Nuts M8    zero
Rivets     number stored as text
Brackets   zero, hidden by format
Hinges     placeholder text
Clamps     placeholder text

The formula check needs the first workbook because the data_only=True load only sees the cached result, and for Washers that result is None, indistinguishable from an empty cell. The hidden-zero check looks at the third section of the number format, the one Excel uses for zero values; an empty section means zeros display as nothing. If the workbook was saved by a tool that does not store formula results, the second load sees None for every formula — see Read Formula Results with openpyxl data_only.

Step 4 — Decide what each case means and apply it

Classification is the evidence; the policy is a business decision. For a stock count, a sensible policy is: a real or text zero is a count of zero; empty, whitespace and empty formula results mean "not counted"; placeholders mean "not applicable"; a hidden zero is a zero that someone wanted to look tidy. Apply it as a mapping from class to status, then build a clean numeric column:

policy = {
    "number": "counted", "zero": "counted", "zero, hidden by format": "counted",
    "number stored as text": "counted",
    "empty": "not counted", "whitespace only": "not counted", "formula, empty result": "not counted",
    "placeholder text": "not applicable",
}
status = pd.Series([policy[k] for _, k in report], name="Status")
clean = raw[["Item"]].assign(
    Status=status,
    Counted=pd.to_numeric(raw["Counted"].astype(str).str.strip(), errors="coerce").astype("Int64"),
)
print(clean.to_string(index=False))
print(clean["Status"].value_counts().to_dict())

The Counted column is now nullable integer: numbers where a count exists — including 0 for Nuts M8, Rivets and Brackets — and <NA> everywhere else, while Status records why a value is missing. The report can now say "three items not counted, two not applicable" instead of "three blanks, a space and a dash". Nullable integers are covered in Use Nullable Data Types When Reading Excel with pandas.

Step 5 — Count blanks the way Excel does, or deliberately differently

If a report must match a figure someone produced in Excel, know that Excel's own functions disagree with each other. COUNTBLANK counts truly empty cells and cells whose formula returns ""; ISBLANK is TRUE only for truly empty cells; neither treats a single space as blank. Reproducing each in pandas is straightforward once the classification exists:

kinds = pd.Series([k for _, k in report])
countblank = kinds.isin(["empty", "formula, empty result"]).sum()
isblank = (kinds == "empty").sum()
looks_blank = kinds.isin(["empty", "formula, empty result", "whitespace only", "zero, hidden by format"]).sum()
print(f"COUNTBLANK={countblank}  ISBLANK count={isblank}  looks blank on screen={looks_blank}")

The three numbers come out as 2, 1 and 4 for the same column. Whichever you report, say which definition it uses. For a full missing-value audit across every column, combine this with Find and Report Missing Values in an Excel File.

Common pitfalls

• Relying on the default NA list. N/A, NA, null, NaN and several others become NaN silently; pass keep_default_na=False while you investigate, then map the markers yourself.
• Expecting pandas to see formulas. A formula returning "" and an empty cell are identical to read_excel; only openpyxl's formula load can separate them.
• Trusting what Excel displays. Custom formats such as 0;-0;;@ and ;;; hide real values; check number_format before calling a cell blank.
• Mixed numbers and text in one column. '0' and 0 print the same but compare differently: df["Counted"] == 0 misses the text zero.
• Writing blanks back. openpyxl does not store empty strings at all, so '' written to a cell becomes a truly empty cell on reload. If the distinction must survive, write the status column alongside the numbers.

Performance notes

Loading a workbook twice with openpyxl doubles the parse time, which is acceptable for sheets of a few thousand rows. For larger files, only classify the columns where blanks carry meaning: read them in read_only=True mode with iter_rows(min_col=…, max_col=…), and leave the rest to read_excel. The pandas reads in Steps 1 and 2 cost the same as any normal read.

Conclusion

"Blank" in Excel covers truly empty cells, formulas returning "", whitespace, zeros hidden by number formats and placeholder text, and the default read_excel merges most of them into NaN. Read with keep_default_na=False to see the raw values, use openpyxl's formula load, cached values and number formats to classify each cell, and turn the classes into an explicit status column plus a nullable numeric column. Then counts of "missing" mean something — and when they must match Excel, remember that COUNTBLANK and ISBLANK use different definitions.

Frequently asked questions

Why does pandas read "N/A" and empty cells as the same NaN? read_excel converts truly empty cells and a default list of marker strings, including N/A, NA, null and NaN, to NaN. Pass keep_default_na=False to keep the strings, and na_values=... to choose your own markers.

How can I tell a formula that returns "" from an empty cell in Python? Load the workbook with openpyxl without data_only: the formula cell's value is a string starting with "=". With data_only=True, or with pandas, both cells look the same because the cached result of the formula is empty.

How do I detect zeros hidden by a number format? Read the cell with openpyxl and inspect cell.number_format. A format whose third section, after the second semicolon, is empty — such as 0;-0;;@ — displays zero values as nothing even though the cell holds 0.

Does Excel COUNTBLANK count cells with a space? No. COUNTBLANK counts truly empty cells and cells whose formula returns an empty string, but a cell containing a space is text and is not counted. ISBLANK is stricter still and returns TRUE only for truly empty cells.

Related

• Up one level: Handling Missing Data in Excel Reports — finding, filling and reporting gaps.
• Replace Placeholder Text with NaN in pandas — mapping dashes and N/A once you have decided what they mean.
• Highlight Blank Cells in Excel with openpyxl — showing the gaps to the people who fill them in.
• Read Cell Values as Displayed in Excel — applying number formats in Python.