Handling Missing Data in Excel Reports with Pandas

Excel exports rarely use a single, clean marker for "no value." One column has empty cells, another has the string "N/A", a third has a stray space. Left alone, these break aggregations or silently skew totals. This guide builds a deterministic workflow: normalize blanks to NaN, profile where the gaps are, fill each column type appropriately, then validate before export. Every block is runnable and shares one namespace, so paste them in order.

This is one stage of Advanced Data Transformation and Cleaning. For parsing raw workbooks, see Cleaning Excel Data with Pandas.

Create a sample workbook

The sample mixes the placeholder styles you meet in the wild — empty cells, "N/A", a dash, and a blank space:

import pandas as pd

raw = pd.DataFrame({
    "region": ["North", "N/A", "South", " ", "West"],
    "sales_rep": ["Ana", "Ben", None, "Dan", "Eve"],
    "revenue": [12000, None, 9800, 7200, "-"],
    "units_sold": [120, 40, None, 36, 60],
    "transaction_date": ["2024-01-05", None, "2024-01-07", "2024-01-08", None],
})
raw.to_excel("monthly_report.xlsx", index=False)

Step 1: Load and normalize blanks to NaN

read_excel only recognizes a default set of NA tokens. Pass na_values so placeholders like "N/A" and "-" become real NaN, and convert whitespace-only strings explicitly:

na_indicators = ["N/A", "NA", "-", "null", "NULL", "#N/A"]
df = pd.read_excel("monthly_report.xlsx", na_values=na_indicators).copy()

# Whitespace-only cells aren't caught by na_values — replace them
df = df.replace(r"^\s*$", pd.NA, regex=True)
print(df)

Step 2: Profile the missingness

Before filling anything, quantify the gaps so you know which columns need attention and how severe each is:

missing_count = df.isna().sum()
missing_pct = (df.isna().mean() * 100).round(1)
profile = pd.DataFrame({"missing_count": missing_count, "missing_pct": missing_pct})
print(profile[profile["missing_count"] > 0])

Step 3: Coerce types before aggregating

Step 1 already mapped "-" to NaN, but coerce revenue and units_sold to numeric anyway as a guard — any stray non-numeric value becomes NaN and joins the gaps to fill:

for col in ["revenue", "units_sold"]:
    df[col] = pd.to_numeric(df[col], errors="coerce")
print(df.dtypes)

Step 4: Fill each column by type

Different columns deserve different fills. Numeric gaps take a representative statistic (median resists outliers); categorical gaps take an explicit label; dates get parsed and then filled in chronological order:

# Numeric: median per column
df["revenue"] = df["revenue"].fillna(df["revenue"].median())
df["units_sold"] = df["units_sold"].fillna(df["units_sold"].median())

# Categorical: explicit labels, not silent guesses
df["region"] = df["region"].fillna("Unassigned")
df["sales_rep"] = df["sales_rep"].fillna("Pending Assignment")

# Temporal: parse, sort, then forward/back fill
df["transaction_date"] = pd.to_datetime(df["transaction_date"], errors="coerce")
df = df.sort_values("transaction_date")
df["transaction_date"] = df["transaction_date"].ffill().bfill()

print(df)

The method= argument to fillna() was removed in pandas 3.0 — use the dedicated ffill() and bfill() methods for forward and backward fills. For the full range of fillna patterns, see Fill Missing Values in Excel with Pandas Fillna.

Step 5: Validate and export

Confirm no NaN survives in the columns your report depends on, then write the cleaned workbook:

critical_cols = ["revenue", "transaction_date", "region"]
remaining = df[critical_cols].isna().sum().sum()
if remaining:
    raise ValueError(f"{remaining} NaN values remain in critical columns")

df.to_excel("cleaned_monthly_report.xlsx", index=False, engine="openpyxl")
print("Exported cleaned_monthly_report.xlsx")

A reusable cleaning function

The same steps, packaged for a pipeline. It only fills columns that exist, so it tolerates schema drift between months:

import pandas as pd

def clean_reporting_excel(input_path, output_path):
    na_map = ["N/A", "NA", "-", "null", "NULL", "#N/A"]
    df = pd.read_excel(input_path, na_values=na_map).copy()
    df = df.replace(r"^\s*$", pd.NA, regex=True)

    for col in ["revenue", "units_sold"]:
        if col in df.columns:
            df[col] = pd.to_numeric(df[col], errors="coerce")

    fill_strategy = {
        "revenue": df["revenue"].median() if "revenue" in df.columns else 0,
        "units_sold": df["units_sold"].median() if "units_sold" in df.columns else 0,
        "region": "Unassigned",
        "sales_rep": "Pending Assignment",
    }
    active = {k: v for k, v in fill_strategy.items() if k in df.columns}
    df = df.fillna(active)

    if "transaction_date" in df.columns:
        df["transaction_date"] = pd.to_datetime(df["transaction_date"], errors="coerce")
        df = df.sort_values("transaction_date")
        df["transaction_date"] = df["transaction_date"].ffill().bfill()

    df.to_excel(output_path, index=False, engine="openpyxl")
    return df

result = clean_reporting_excel("monthly_report.xlsx", "report_clean.xlsx")
print(f"Cleaned {len(result)} rows")

Common errors and fixes

fillna() ignores blank cells. Excel often exports blanks as empty strings, which pandas keeps as valid text. Run df.replace(r"^\s*$", pd.NA, regex=True) after loading so the blanks become NaN.

Filling a mixed column turns numbers into strings. A column holding numbers and "-" reads as object; a numeric fill then coerces everything to text. Run pd.to_numeric(df[col], errors="coerce") first.

sum() or mean() still returns NaN. Check df.dtypes — an unconverted object column or a remaining NaN propagates. Coerce types and confirm the fill covered every gap.

Dates appear as serial numbers like 45215. Excel stores dates as serials. Let read_excel parse them, or convert numeric date columns with pd.to_datetime(col, origin="1899-12-30", unit="D").

What a blank cell actually means

Before choosing a fill strategy, decide what the gap represents. The same empty cell can mean three different things, and they call for three different treatments:

The distinction is not academic. Filling "not yet recorded" with zero makes a total look complete when it is not; treating "genuinely nothing" as missing drops a row from an average that should have included it. Because a spreadsheet cannot record the difference, it has to come from the business — and once you know it, encode it per column rather than applying one rule across the frame.

import pandas as pd

FILL_ZERO = ["Units_Sold", "Discount"]          # genuinely nothing
LEAVE_MISSING = ["Unit_Price", "Customer_ID"]   # unknown — must be reported

def apply_missing_policy(df):
    out = df.copy()
    for column in FILL_ZERO:
        if column in out:
            out[column] = out[column].fillna(0)
    gaps = {c: int(out[c].isna().sum()) for c in LEAVE_MISSING if c in out}
    return out, {c: n for c, n in gaps.items() if n}

frame, remaining = apply_missing_policy(pd.read_excel("orders.xlsx"))
print("still missing:", remaining)

Filling in an order that makes sense

Forward fill, group means and interpolation each assume something about the data, and applying them in the wrong place produces numbers that look reasonable and are not:

import pandas as pd

df = pd.read_excel("orders.xlsx", sheet_name="Orders")

# 1. A merged grouping label: the blank really does mean "same as above"
df["Region"] = df["Region"].ffill()

# 2. A price that is stable within a product: the group's own value is the best guess
df["Unit_Price"] = df.groupby("SKU")["Unit_Price"].transform(lambda s: s.fillna(s.median()))

# 3. A time series with occasional gaps: interpolate only where the order is meaningful
df = df.sort_values("Order_Date")
df["Daily_Total"] = df["Daily_Total"].interpolate(method="linear", limit=2, limit_area="inside")

print(df.isna().sum())

limit_area="inside" is the argument that prevents interpolation inventing values before the first and after the last real observation — extrapolation dressed as interpolation, and the source of many a report showing activity in a month that had none. The limit=2 caps how far a gap may be bridged, so a fortnight of missing data stays visibly missing.

Filling from a group's own median rather than the column's is nearly always better: a missing price for one SKU should be filled from that SKU's other rows, not from the average across a catalogue spanning two orders of magnitude.

Report what was filled

Every fill is an assumption, and a report that hides its assumptions is hard to defend. Recording them costs three lines and turns a question into a footnote:

def fill_with_report(df, column, value, label):
    missing = int(df[column].isna().sum())
    if missing:
        df[column] = df[column].fillna(value)
    return df, (f"{column}: filled {missing} value(s) with {label}" if missing else None)

notes = []
for column, value, label in [("Discount", 0, "zero"), ("Units_Sold", 0, "zero")]:
    df, note = fill_with_report(df, column, value, label)
    if note:
        notes.append(note)

print("\n".join(notes))

Writing those notes onto a Notes sheet in the delivered workbook is better still. A reader who can see that eleven prices were filled from the product median understands the number in front of them; one who cannot will eventually ask, and the answer will have to be reconstructed.

Missingness is a number worth reporting

Before deciding what to do about gaps, measure them. A short profile turns "the file has some blanks" into a decision anyone can review:

import pandas as pd

def missingness(df):
    total = len(df)
    report = pd.DataFrame({
        "missing": df.isna().sum(),
        "percent": (df.isna().sum() / total * 100).round(1),
    })
    return report[report["missing"] > 0].sort_values("missing", ascending=False)

frame = pd.read_excel("orders.xlsx", sheet_name="Orders")
print(missingness(frame))

The percentage column is what makes the numbers actionable. A column that is 2% empty is a data-entry issue; one that is 60% empty is usually a column that stopped being populated when a system changed, and no fill strategy is the right answer for it — reporting it and asking is.

Patterns matter more than counts

Gaps that cluster tell you something a total cannot. Missing prices concentrated in one product family suggest a catalogue that was never loaded; missing dates concentrated in one week suggest an export that failed. Grouping the missingness is a two-line check that turns a fill decision into a diagnosis:

by_group = (
    frame.assign(_missing=frame["Unit_Price"].isna())
    .groupby("Region", observed=True)["_missing"]
    .agg(["sum", "mean"])
    .rename(columns={"sum": "missing", "mean": "rate"})
)
print(by_group.round(3))

When one group's rate is far above the others, filling with a global median imports that group's problem into every other. Filling within the group — or refusing to fill at all and reporting the gap — is both more honest and more defensible when someone asks where a number came from.

Excel's blanks are not all the same

A cell can be empty, hold an empty string, hold a space, or hold text such as N/A that a person typed to mean "nothing". pandas sees four different things, and only the first is NaN by default:

import pandas as pd

frame = frame.replace({"": pd.NA, " ": pd.NA, "-": pd.NA, "N/A": pd.NA, "n/a": pd.NA})
print(frame.isna().sum())

Normalising them before any analysis is what makes a missingness report trustworthy. Doing it after a groupby — or not at all — produces a report claiming a column is complete when a fifth of its values are the string "N/A", which is the kind of error that survives every other check.

Missing values and the aggregations that hide them

pandas skips missing values in most aggregations, which is usually helpful and occasionally misleading. sum treats them as zero, mean excludes them from both the numerator and the denominator, and count ignores them entirely — so three summary figures over the same column can each imply a different row count:

import pandas as pd

series = pd.Series([10, 20, None, 30])
print(series.sum(), series.mean(), series.count(), len(series))     # 60.0 20.0 3 4

An average of twenty is arithmetically correct and reports the mean of the three values that exist. Whether that is the number a reader wants depends entirely on what the gap meant — and a report that shows a mean without saying how many rows it covered invites exactly the wrong conclusion.

Publishing the count alongside every average removes the ambiguity for free:

summary = (
    frame.groupby("Region", observed=True)
         .agg(Average=("Revenue", "mean"), Rows=("Revenue", "count"), Total=("Revenue", "sum"))
         .round(2)
)
print(summary)

Say what you did in the report itself

The last step in handling missing data is telling the reader about it. A short note on the summary sheet — "11 unit prices were filled from the product median; 4 rows lacked a customer id and were excluded" — turns an invisible assumption into a stated one. It takes two cells to write and it prevents the conversation where a figure is questioned and nobody can reconstruct why it looks the way it does.

Missing data is a conversation

The technical part of handling gaps is small; the decision about what a blank means belongs to whoever owns the data. A pipeline that fills silently makes that decision by default and hides it, while one that reports the gaps — with counts, percentages and the pattern they follow — hands it back to the person who can answer. That is usually the difference between a report that survives scrutiny and one that quietly loses the reader's trust.

Decide once, write it down

The policy for each column — fill with zero, fill from the group, leave and report — belongs in a constant at the top of the job rather than scattered through the transformation. Keeping it in one place makes it reviewable by whoever owns the data, gives the run log something specific to report, and means the next person to change a rule can see every other rule at the same time. A missing-data policy that lives only in the flow of the code is one nobody will find when it needs revisiting.

Presentation comes after the data

Any write replaces what it covers, so formatting, filters, images and charts belong in a single finishing pass that runs after the last value has been written. Splitting the job that way — build the frame, write it, then decorate the finished sheet — is what stops a style disappearing the month someone adds a to_excel call in the middle. It also gives a report one obvious place to change when the house style moves, instead of a dozen scattered blocks that have to be found first.

Gaps change the shape of an average

Two summaries over the same column can disagree entirely depending on how missing values were treated, and neither is wrong — they answer different questions. A mean that skips gaps describes the rows that reported; a mean that treats gaps as zero describes every row. Publishing the row count next to every average removes the ambiguity, and stating the treatment in a note on the summary sheet removes the argument.

The habit worth forming is to decide the treatment per column, write it down where the code can be read, and report the counts alongside the figures. A report that shows an average without saying what it covered invites exactly the conclusion its author did not intend.

Frequently asked questions

Why does fillna() skip my empty Excel cells? Excel often exports blanks as empty strings, which pandas treats as valid text rather than NaN. Run df.replace(r"^\s*$", pd.NA, regex=True) after loading so whitespace-only cells become real gaps that fillna() can fill.

How do I make read_excel treat "N/A" and "-" as missing? Pass them in na_values, e.g. pd.read_excel(path, na_values=["N/A", "NA", "-", "null"]). Only a default set of tokens is recognized otherwise, so custom placeholders stay as strings.

Why did filling a column turn my numbers into text? A column mixing numbers and a placeholder like "-" reads as object dtype, so a fill coerces everything to strings. Run pd.to_numeric(df[col], errors="coerce") first to isolate the numerics before filling.

Can I still use fillna(method="ffill")? No — the method= argument to fillna() was removed in pandas 3.0. Use the dedicated df.ffill() and df.bfill() methods for forward and backward fills.

My dates show up as numbers like 45215 — why? Excel stores dates as serial numbers. Let read_excel parse them, or convert manually with pd.to_datetime(col, origin="1899-12-30", unit="D").

Key takeaways

• Make every gap visible first. Pass na_values to read_excel and run df.replace(r"^\s*$", pd.NA, regex=True) so placeholders like "N/A", "-", and whitespace-only cells all become real NaN before you decide anything.
• Profile before you fill. Quantify isna().sum() and isna().mean() per column so you fill deliberately — a column that is 80% empty deserves a different decision than one missing a single value.
• Coerce types before aggregating. A column mixing numbers and a placeholder reads as object; run pd.to_numeric(col, errors="coerce") so a later fillna or sum behaves numerically instead of on strings.
• Fill by column type, on purpose. Numeric gaps take a robust statistic like the median, categorical gaps take an explicit label, and dates get sorted then ffill().bfill() — never a single blanket fill across the frame.
• Validate before export. Assert that no NaN survives in the columns your report depends on, so a scheduled run raises rather than silently writing a broken workbook.

Related

Up one level: Advanced Data Transformation and Cleaning — the full ingest-to-export pipeline this missing-data stage sits inside.

Go deeper on the fill itself:

• Fill Missing Values in Excel with Pandas Fillna — every fillna strategy in depth, from constants to per-group fills.

Related workflows in this section:

• Cleaning Excel Data with Pandas — the upstream parse-and-normalize pass that feeds this one.
• Merging and Joining Excel DataFrames — joins are a frequent source of fresh NaN, so clean gaps again after combining.
• Creating Pivot Tables from Excel Data — aggregate the gap-free data once every column is filled.