Visualise Missing Data Patterns in an Excel File

"12% of the values are missing" is the least useful thing you can say about missing data. What matters is the pattern. Blanks scattered at random through a column are a nuisance; blanks that all start in March point to a broken export; blanks that always occur in three columns together mean a whole form section was skipped; blanks concentrated in one branch's data mean one team is not filling something in. Each pattern has a different fix, and none of them is visible from a percentage. This page builds a missing-data report in Excel with pandas and openpyxl: a shaded map of every blank cell, the most frequent combinations of missing columns, missing rates over time and by source, and pairs of columns that go missing together. It belongs to Handling Missing Data in Excel Reports.

Prerequisites

pip install pandas openpyxl

orders.xlsx holds 240 orders from four branches between January and early October 2026, with ten columns. It has the kinds of gaps real exports have — and part of the point of this page is to find out what they are without being told.

Step 1 — The headline numbers, and why they are not enough

Start with the usual per-column count, so the more detailed views have something to explain:

import pandas as pd

orders = pd.read_excel("orders.xlsx")
missing = orders.isna()
per_col = pd.DataFrame({"missing": missing.sum(), "share %": (missing.mean() * 100).round(1)})
print(per_col[per_col["missing"] > 0].sort_values("missing", ascending=False).to_string())
print(f"overall: {missing.to_numpy().mean():.1%} of cells blank")

Six columns have gaps, from 2% of Amount to 45% of Discount %, and about a tenth of all cells are blank. This tells you where to look, not why — Discount % being blank for almost half the rows could be people not entering it, a column that was retired, or a perfectly normal "no discount". The isna() frame built here is reused by every view below: one boolean per cell, True where the value is missing.

Step 2 — A missingness map sheet

The most direct picture is the data itself with every blank cell shaded. Writing 1 for missing and 0 for present, with conditional formatting on top, gives a map Excel users can scroll, filter and sort:

from openpyxl import Workbook
from openpyxl.formatting.rule import CellIsRule
from openpyxl.styles import Alignment, Font, PatternFill

wb = Workbook()
ws = wb.active
ws.title = "Missing map"
cols = list(orders.columns)
ws.append(["Row", "Date", "Branch"] + cols)
for i, (row_missing, date, branch) in enumerate(zip(missing.itertuples(index=False), orders["Date"], orders["Branch"]), start=2):
    ws.append([i, date.date() if pd.notna(date) else None, branch] + [int(v) for v in row_missing])
last_col = ws.cell(1, ws.max_column).column_letter
ws.conditional_formatting.add(f"D2:{last_col}{ws.max_row}", CellIsRule(operator="equal", formula=["1"],
                              fill=PatternFill("solid", fgColor="D9534F"), font=Font(color="D9534F")))
ws.conditional_formatting.add(f"D2:{last_col}{ws.max_row}", CellIsRule(operator="equal", formula=["0"],
                              fill=PatternFill("solid", fgColor="E8EEF7"), font=Font(color="E8EEF7")))
for c in ws[1]:
    c.font = Font(bold=True)
    c.alignment = Alignment(text_rotation=90 if c.column > 3 else 0)
for col in range(4, ws.max_column + 1):
    ws.column_dimensions[ws.cell(1, col).column_letter].width = 3.2
ws.freeze_panes = "D2"
ws.auto_filter.ref = ws.dimensions
wb.save("missing_report.xlsx")
print("map:", ws.max_row - 1, "rows ×", len(cols), "columns")

Each data column becomes a narrow strip with a rotated header, red where a value is missing and pale blue where it is present; the font matches the fill so the 0s and 1s disappear into colour. The date and branch columns stay readable on the left, and the autofilter lets someone filter to one branch and watch its pattern. Scrolling down, the map shows a solid red band in Discount % from June onwards, three delivery columns that turn red on the same rows, and Sales rep gaps that appear only on some rows. These are patterns no percentage reveals.

Step 3 — The most common combinations of missing columns

Turning each row's missingness into a pattern string and counting them shows which columns go missing together:

patterns = missing.apply(lambda r: " + ".join(c for c in cols if r[c]) or "(complete)", axis=1)
top = patterns.value_counts().head(8).rename_axis("Missing columns").to_frame("Rows")
top["share %"] = (top["Rows"] / len(orders) * 100).round(1)
print(top.to_string())

The output reads like a diagnosis. After the 95 complete rows, "Discount %" alone is the largest group — 86 orders from the June export change. "Delivery postcode + Delivery date + Courier" always appear together and never separately, which is the signature of a skipped section: these are the store-collection orders, which genuinely have no delivery details. Those gaps are not errors at all; they should be documented and probably given an explicit "Collected" value instead of blanks. Mixed combinations such as "Discount % + Delivery postcode + Delivery date + Courier" are simply two independent causes landing on the same row — a June order that was also collected in store. A combination that appears only once or twice is often a genuine data-entry slip worth fixing by hand.

Step 4 — Missing rate over time and by source

Many gaps are caused by when or where data was entered. A month-by-column table and a branch-by-column table of missing rates expose both:

by_month = (missing.groupby(orders["Date"].dt.to_period("M")).mean() * 100).round(0)
by_branch = (missing.groupby(orders["Branch"]).mean() * 100).round(0)
show = ["Amount", "Discount %", "Delivery date", "Sales rep"]
print(by_month[show].astype(int).to_string())
print(by_branch[show].astype(int).to_string())

By month, Discount % jumps from 0% to 100% in June and stays there — something changed in the export, not in customer behaviour, and the fix is to ask whoever owns the export. By branch, Sales rep is missing on 68% of Cardiff orders and never elsewhere — a process issue in one branch, fixed by a conversation rather than by code. Amount's small, even gap rate across months and branches looks like genuine random omissions. The delivery columns, by contrast, show a steady 10–20% in every month and branch — consistent with a normal share of store collections. These tables go in the report next to the map so a data owner can see the cause at a glance.

Step 5 — Columns that go missing together

For wider sheets, a correlation matrix of the missingness indicators summarises co-missing columns in one table. A value near 1 means two columns are almost always blank on the same rows:

gaps = missing.loc[:, missing.any()].astype(int)
co = gaps.corr().round(2)
print(co.to_string())

with pd.ExcelWriter("missing_report.xlsx", engine="openpyxl", mode="a") as xw:
    per_col.to_excel(xw, sheet_name="By column")
    top.to_excel(xw, sheet_name="Patterns")
    by_month.astype(str).to_excel(xw, sheet_name="By month")
    by_branch.to_excel(xw, sheet_name="By branch")
    co.to_excel(xw, sheet_name="Co-missing")
with pd.ExcelFile("missing_report.xlsx") as book:
    print("sheets:", book.sheet_names)

The three delivery columns correlate perfectly (1.0) with each other, confirming the section pattern from Step 3; Discount % and Sales rep are unrelated to everything else. Converting the month periods to strings before writing avoids Excel showing them oddly. mode="a" appends the summary sheets to the workbook that already holds the map, so the whole picture arrives in one file. A colour scale on the co-missing sheet, as in Turn a Pivot Table into a Heatmap in Excel, makes strong pairs jump out.

Common pitfalls

• Reporting only percentages. They hide when, where and in which combinations data is missing.
• Treating every blank as an error. Some blanks are legitimate; give them an explicit value.
• Imputing before diagnosing. A column blank since an export change needs fixing at the source.
• Huge maps. For very large files, map a sample or aggregate by period first.
• Writing periods straight to Excel. Convert them to strings or dates first.

Conclusion

Missing data has patterns, and the patterns point to causes: an export that changed on a date, a form section that is skipped together, a branch that does not fill in a field, or genuine random omissions. Build the isna() frame once, then show it as a shaded map, as counted combinations of missing columns, as rates by month and by source, and as a co-missing correlation table — all in one Excel report a data owner can read. Fix each pattern at its cause before reaching for imputation.

Frequently asked questions

How do I see missing data patterns in Python? Build df.isna(), then count each row's combination of missing columns, group the flags by month or source to get missing rates, and correlate the flags to find columns that are blank together.

How do I highlight blank cells across a whole sheet in Excel? Write a 1 for missing and 0 for present for every cell and add conditional formatting that fills 1s in a strong colour. Matching the font colour to the fill hides the numbers.

How can I tell if missing values are random? If gaps are spread evenly across periods, sources and other columns they are likely random. Gaps that start on a date, cluster in one source or occur together in several columns have a specific cause.

What does it mean when several columns are always missing together? Usually a whole section was skipped or does not apply, such as delivery details for collected orders. Document it and use an explicit value rather than blanks.

Related

• Up one level: Handling Missing Data in Excel Reports — finding and treating gaps.
• Find and Report Missing Values in an Excel File — the counts this page goes beyond.
• Profile an Excel Dataset with pandas — a wider look at a new file.
• Impute Missing Values by Group with pandas — filling gaps once you understand them.