Python Excel AutomationPractical guides for developers
Start learning
Guide
Advanced Data Transformation And CleaningDeep dive

Find and Report Missing Values in an Excel File

Audit an Excel file for gaps before you use it — per-column null counts, disguised blanks like n/a and dashes, missing-value patterns, and a formatted report of what is absent.

Before you fill a gap you have to know it is there, how big it is, and whether it is a gap at all. An Excel file arriving from elsewhere hides missing data in several forms: genuinely empty cells, the string n/a that pandas reads as ordinary text, spacer rows that inflate every count, and sentinel numbers like -1 that look like data. This guide builds a completeness audit that finds all of them and produces a report somebody can act on. It is the first step in Handling Missing Data in Excel Reports.

The four disguises missing data wears Four categories. Truly empty cells are recognised by pandas as NaN and counted automatically. Text sentinels such as n slash a, a dash or the word unknown are read as ordinary strings and are invisible to isna. Whole blank spacer rows come from the source layout rather than from records and inflate every per-column count. Numeric sentinels like minus one or nine nines look like valid data and quietly distort averages and sums. 1 · truly empty cells pandas reads them as NaN isna() finds these — the easy case 2 · text sentinels "n/a" · "-" · "unknown" · " " read as ordinary text — invisible 3 · whole blank rows spacers from the sheet layout inflate every per-column count 4 · numeric sentinels -1 · 0 · 999999 · 1900-01-01 look valid — distort every average

Prerequisites

Bash
pip install pandas openpyxl xlsxwriter

A file containing all four disguises:

Python
import numpy as np
import pandas as pd

pd.DataFrame({
    "order_id": [1001, 1002, 1003, None, 1005, 1006],
    "region": ["North", "n/a", "West", None, "-", "South"],
    "units": [120, 88, None, None, 95, -1],
    "revenue": [5150.00, 4268.50, 3511.25, None, np.nan, 1820.00],
    "note": [None, None, None, None, "restated", None],
}).to_excel("orders.xlsx", index=False)

Step 1 — Count what pandas already sees

isna covers the genuinely empty cells:

Python
import pandas as pd

df = pd.read_excel("orders.xlsx")

missing = df.isna().sum()
completeness = (1 - df.isna().mean()) * 100

report = pd.DataFrame({
    "missing": missing,
    "present": len(df) - missing,
    "complete_pct": completeness.round(1),
}).sort_values("missing", ascending=False)

print(report.to_string())

That is the baseline, and it understates the problem on any real file — n/a, -1 and the spacer row are all counted as present.

Step 2 — Catch the disguised blanks

Sentinels are file-specific, so make the list explicit rather than hoping the defaults cover it:

Python
import pandas as pd

TEXT_SENTINELS = {
    "", " ", "-", "--", ".", "n/a", "N/A", "na", "NA", "null", "NULL",
    "none", "None", "unknown", "UNKNOWN", "tbc", "TBC", "#N/A", "?",
}

def effective_missing(series, numeric_sentinels=()):
    """A boolean mask of values that are missing in substance, not just in form."""
    blank = series.isna()

    if series.dtype == "object" or str(series.dtype).startswith("string"):
        text = series.astype("string").str.strip()
        blank = blank | text.isin(TEXT_SENTINELS)
    elif numeric_sentinels:
        blank = blank | series.isin(list(numeric_sentinels))

    return blank

SENTINELS = {"units": (-1, 0), "revenue": (-1,)}

for name in df.columns:
    mask = effective_missing(df[name], SENTINELS.get(name, ()))
    print(f"{name:<12} pandas sees {df[name].isna().sum()}, "
          f"actually missing {int(mask.sum())}")

The gap between the two numbers is the point of the exercise. A column pandas calls 100% complete can be a third empty in substance.

You can also stop pandas guessing on the way in, which matters when a legitimate value collides with a default sentinel — the country code NA for Namibia is the classic case:

Python
import pandas as pd

# Namibia's code survives; only genuinely blank cells become NaN.
df = pd.read_excel(
    "orders.xlsx",
    keep_default_na=False,
    na_values=["", " "],
)

Step 3 — Separate spacer rows from real gaps

A blank row in the middle of a sheet is a layout artefact, not a record with missing fields. Counting it as one distorts every column's percentage:

Python
import pandas as pd

def split_blank_rows(df):
    """Return (real rows, blank spacer rows)."""
    all_blank = df.isna().all(axis=1)
    return df.loc[~all_blank].copy(), df.loc[all_blank]

data, spacers = split_blank_rows(df)
print(f"{len(spacers)} spacer row(s) excluded; {len(data)} real rows")

Partly blank rows are the interesting middle case — a record where the key is present but half the fields are absent tells you something different from one where the key itself is gone:

Python
import pandas as pd

KEYS = ["order_id"]

def row_completeness(df, keys):
    """Classify rows by how much of them is present."""
    missing_per_row = df.isna().sum(axis=1)
    key_missing = df[keys].isna().any(axis=1)

    return pd.DataFrame({
        "missing_fields": missing_per_row,
        "key_missing": key_missing,
        "class": pd.cut(
            missing_per_row / df.shape[1],
            bins=[-0.01, 0.0, 0.34, 0.67, 1.0],
            labels=["complete", "mostly complete", "sparse", "almost empty"],
        ),
    })

print(row_completeness(data, KEYS)["class"].value_counts())

The rows with a missing key are the ones to quarantine rather than fill — a record you cannot identify cannot be reconciled with anything, and the discussion of what to do next belongs in filling missing values with pandas fillna.

Step 4 — Look at the pattern, not just the count

Where the gaps sit matters more than how many there are. Three patterns tell three different stories:

Three shapes of missingness, three different causes Three grids of rows and columns with missing cells marked. Scattered individual gaps across the grid suggest ordinary data-entry lapses, and filling or excluding them is reasonable. A solid contiguous block in one column over a range of rows suggests one batch or one source failed, which is a pipeline problem rather than a data-quality one. A whole tail of rows missing across every column suggests the extract was truncated, and the missing rows are not missing values at all — they were never delivered. scattered a block a tail data-entry lapses fill or exclude row by row one source or batch failed a pipeline problem, not a data one the extract was truncated those rows were never delivered
Python
import pandas as pd

def missing_pattern(df, column, bins=20):
    """Where in the file do this column's gaps sit?"""
    mask = df[column].isna()
    if not mask.any():
        return "complete"

    position = pd.cut(pd.Series(range(len(df))), bins=bins, labels=False)
    by_bin = mask.groupby(position).mean()

    if by_bin.tail(max(1, bins // 5)).mean() > 0.9:
        return "tail — the extract may be truncated"
    if (by_bin > 0.9).sum() >= 2 and (by_bin < 0.1).sum() >= 2:
        return "block — one batch or source appears to have failed"
    return "scattered — ordinary data-entry gaps"

for name in df.columns:
    print(f"{name:<12} {missing_pattern(df, name)}")

Distinguishing these is what turns "the revenue column is 30% empty" into an actionable statement. A tail means asking the sender to re-run the extract; scattered gaps mean deciding a fill strategy.

Step 5 — Write the report

One threshold for the file flags the wrong columns Four columns with their actual completeness shown as bars against a per-column expectation marker. The order id column is ninety-eight per cent complete against a hundred per cent expectation and is therefore a genuine failure. Revenue at ninety-four per cent against ninety passes. Region at ninety-six against ninety-five passes. The notes column at four per cent has no expectation and passes trivially. A single file-wide threshold of ninety would have flagged notes and missed order id — exactly backwards. completeness against a per-column expectation order_id FAIL revenue OK region OK note OK a single 90% threshold would flag "note" and miss "order_id" — exactly backwards

Produce something a non-programmer can open, with the gaps highlighted:

Python
import pandas as pd

def completeness_report(path, dest, sentinels=None, expectations=None):
    """Audit an Excel file and write a formatted completeness report."""
    df = pd.read_excel(path)
    data, spacers = split_blank_rows(df)
    sentinels = sentinels or {}
    expectations = expectations or {}

    rows = []
    for name in data.columns:
        mask = effective_missing(data[name], sentinels.get(name, ()))
        missing = int(mask.sum())
        complete = round((1 - missing / len(data)) * 100, 1) if len(data) else 0.0
        required = expectations.get(name, 0.0)
        rows.append({
            "column": name,
            "rows": len(data),
            "missing": missing,
            "complete_pct": complete,
            "required_pct": required,
            "status": "OK" if complete >= required else "BELOW EXPECTATION",
            "pattern": missing_pattern(data, name),
        })

    report = pd.DataFrame(rows).sort_values("complete_pct")

    with pd.ExcelWriter(dest, engine="xlsxwriter") as writer:
        report.to_excel(writer, sheet_name="Completeness", index=False)
        book, sheet = writer.book, writer.sheets["Completeness"]

        header = book.add_format({"bold": True, "bg_color": "#EEF2FF",
                                  "border": 1})
        for position, name in enumerate(report.columns):
            sheet.write(0, position, name, header)

        bad = book.add_format({"bg_color": "#FEE8F2", "font_color": "#BE185D"})
        sheet.conditional_format(
            1, 5, len(report), 5,
            {"type": "text", "criteria": "containing",
             "value": "BELOW", "format": bad},
        )
        sheet.set_column("A:A", 20)
        sheet.set_column("B:F", 14)
        sheet.set_column("G:G", 42)
        sheet.freeze_panes(1, 0)

    return report

completeness_report(
    "orders.xlsx", "completeness.xlsx",
    sentinels={"units": (-1, 0)},
    expectations={"order_id": 100.0, "region": 95.0, "revenue": 90.0},
)

Per-column expectations are what makes the report useful. A blanket threshold flags the commentary column that is meant to be empty and misses the key column that is 2% short — which is the one that matters. The highlighting technique generalises, as shown in highlighting invalid cells in Excel with Python.

Common pitfalls and fixes

SymptomCauseFix
Column reports 100% complete but is notText sentinels read as dataCheck against an explicit sentinel list.
Country code NA became blankpandas default na_valueskeep_default_na=False plus your own list.
Every column looks 10% emptySpacer rows counted as recordsExclude all-blank rows first.
Averages look wrongNumeric sentinel such as -1 includedTreat sentinels as missing before aggregating.
Report flags a notes columnBlanket thresholdSet expectations per column.
Missing values reappear next runFixed the symptom, not the sourceReport the pattern; escalate a block or tail.
A whole tail of rows is emptyExtract truncated upstreamAsk for a re-run; do not fill.

Performance and scale notes

isna() builds a boolean frame the same shape as the data, so auditing a large workbook briefly doubles its memory footprint. Two adjustments keep that manageable.

Aggregate per column rather than materialising the whole mask. df.isna().sum() builds the full frame; a loop over columns builds one column at a time:

Python
missing = {name: int(df[name].isna().sum()) for name in df.columns}

Audit a sample for a first pass. Completeness percentages stabilise quickly, so a sample of a hundred thousand rows gives a reliable picture of a ten-million-row file at a fraction of the cost. Follow up with a full pass only on the columns the sample flagged.

For files too large to hold at all, accumulate the counts chunk by chunk — the counts are additive, so partial results combine cleanly:

Python
import pandas as pd

totals, rows = None, 0
for chunk in pd.read_csv("huge_export.csv", chunksize=200_000):
    part = chunk.isna().sum()
    totals = part if totals is None else totals.add(part, fill_value=0)
    rows += len(chunk)

print((1 - totals / rows).mul(100).round(1).sort_values().to_string())

The same chunked shape works for Excel via the approach in reading large Excel files in chunks, and running the audit at ingest — before anything downstream depends on the data — is where it costs least and catches most.

Conclusion

A completeness audit is isna plus everything isna cannot see. List the text sentinels the file actually uses, name the numeric ones per column, and exclude whole-blank spacer rows so the percentages mean something. Then look at where the gaps sit: scattered gaps are a data-quality question, a solid block is a failed source, and a missing tail means the extract was truncated and no amount of filling will help. Set expectations per column rather than one threshold for the file, and write the result somewhere a human will read it before anybody fills anything.

Frequently asked questions

Which values does pandas treat as missing by default? Empty cells, NaN, None, NaT and a short list of strings including NA, N/A, null and nan. Anything else — a dash, the word unknown, a single space, the number -1 used as a sentinel — is read as ordinary data.

How do I stop pandas treating a real value as missing? Pass keep_default_na=False and supply your own na_values list. That matters for a genuine product code like NA or a country code like NA for Namibia, which the defaults would otherwise blank out.

What is a good completeness threshold? It depends on the column, not the file. A key column should be one hundred per cent complete, a commentary column can be almost entirely empty. Set the expectation per column and check against it.

Should I report missing values or just fill them? Report first, always. Filling before understanding turns a broken upstream extract into a plausible-looking report, and the fill choice depends on why the values are missing in the first place.

How do I find rows that are entirely blank? Use df.isna().all(axis=1) to select them. They usually come from spacer rows in the source sheet rather than from real records, so counting them separately keeps the per-column figures honest.