Python Excel AutomationPractical guides for developers
How-to
Advanced Data Transformation And CleaningDeep dive

Build a Data Quality Scorecard for an Excel File

Score an incoming Excel file on data quality with pandas, column by column: completeness, validity against a type, pattern or range, uniqueness for key columns, consistency with an allowed list and freshness of dates — declared once in a small rules table, combined into a score per column and for the file, compared with last month's file, and written as a colour-coded scorecard with sample failing values.
Validation & data qualityReport layouts & templates

When a team receives the same spreadsheet every month — a supplier's price file, a branch's sales return, a customer extract from another system — the question is not only is this file valid? but how good is it, and is it getting better or worse? A list of individual errors answers the first; a scorecard answers the second. It measures each column on the standard dimensions of data quality — how complete it is, how many values are valid, whether keys are unique, whether coded values come from the allowed list, whether dates are recent — rolls those into a score per column and an overall score, and puts this month next to last month. That turns a vague "the data from region X is always a mess" into a number both sides can track. This page builds such a scorecard with pandas from a small table of rules, scores a customer extract, compares it with the previous one, and writes a colour-coded Excel sheet with examples of failing values. It belongs to Validating Excel Data with Python.

Prerequisites

Bash
pip install pandas openpyxl

Two monthly customer extracts, August and September 2026, each with four hundred rows: customer ID, name, email, country code, credit limit and date of last order. September's file is the one being scored; August's provides the comparison.

Step 1 — Declare the rules once

Python
import pandas as pd

RULES = {
    "Customer ID":  {"required": True, "pattern": r"^C\d{5}$", "unique": True},
    "Name":         {"required": True},
    "Email":        {"required": True, "pattern": r"^[^@\s]+@[^@\s]+\.[a-z]{2,}$"},
    "Country":      {"required": True, "allowed": {"GB", "IE", "FR", "DE"}},
    "Credit limit": {"required": True, "range": (0, 100_000)},
    "Last order":   {"required": False, "max_age_days": 730},
}
WEIGHTS = {"completeness": 0.3, "validity": 0.4, "uniqueness": 0.3}
AS_OF = pd.Timestamp("2026-09-30")

The rules describe what good looks like for each column, in one place: whether a value is required, a pattern it must match, a range or a list of allowed values it must fall within, whether it must be unique, and how old a date may be before it counts as stale. Keeping the rules as data rather than as scattered if statements means the same scorecard code serves any file — a new file type is a new rules dictionary — and the rules themselves can be reviewed by the people who own the data. The weights decide how much each dimension contributes to a column's score; here validity counts most, because a present but wrong value does more damage than a missing one.

Step 2 — Measure each column

Python
def score_column(s, rule):
    blank = s.isna() | s.astype(str).str.strip().isin(["", "n/a", "N/A", "-"])
    present = s[~blank]
    completeness = 1 - blank.mean()
    ok = pd.Series(True, index=present.index)
    if "pattern" in rule:
        ok &= present.astype(str).str.match(rule["pattern"])
    if "allowed" in rule:
        ok &= present.isin(rule["allowed"])
    if "range" in rule:
        lo, hi = rule["range"]
        ok &= pd.to_numeric(present, errors="coerce").between(lo, hi, inclusive="right")
    if "max_age_days" in rule:
        ok &= (AS_OF - pd.to_datetime(present, errors="coerce")).dt.days <= rule["max_age_days"]
    validity = ok.mean() if len(present) else 1.0
    uniqueness = 1 - present.duplicated().mean() if rule.get("unique") else None
    parts = {"completeness": completeness, "validity": validity, "uniqueness": uniqueness}
    used = {k: WEIGHTS[k] for k, v in parts.items() if v is not None}     # only the dimensions that apply
    score = sum(parts[k] * w for k, w in used.items()) / sum(used.values()) * 100
    failing = present[~ok].astype(str).unique()[:3].tolist() + (["(blank)"] if blank.any() else [])
    return {"Completeness": completeness, "Validity": validity, "Uniqueness": uniqueness,
            "Score": round(score, 1), "Examples": ", ".join(failing[:4])}

def scorecard(path):
    df = pd.read_excel(path)
    rows = {col: score_column(df[col], rule) for col, rule in RULES.items()}
    card = pd.DataFrame(rows).T
    return card, round(card["Score"].astype(float).mean(), 1)

card, overall = scorecard("customers_2026-09.xlsx")
print(card.drop(columns="Examples").astype({"Completeness": float, "Validity": float, "Uniqueness": float}).round(3).to_string(na_rep="—"))
print("overall score:", overall)

Each column gets three numbers between 0 and 1. Completeness is the share of values that are present, counting empty strings and placeholders like n/a as missing, because a cell containing n/a is no more useful than an empty one. Validity is the share of present values that pass every rule for the column — pattern, allowed list, range and age. Uniqueness is the share of values that are not repeats, measured only for columns that should be unique, such as the customer ID. The weighted average of the dimensions that apply, times 100, is the column's score — a column with no uniqueness rule is scored on completeness and validity alone, rather than being handed uniqueness points for free, which would push every column towards a flattering score — and the file's score is the average over its columns. Up to three failing values are kept as examples, which is what turns a low score into something actionable: UK and Germany in the country column point straight at the fix.

The dimensions behind a column score Completeness counts present values, with blanks and placeholders such as n/a treated as missing. Validity checks present values against the column's pattern, allowed list, range or maximum age. Uniqueness checks key columns for repeats. The weighted sum gives the column score out of 100. Completeness present, not blank or n/a — weight 0.3 Validity pattern, allowed list, range, age — 0.4 Uniqueness key columns only — 0.3 Column score 0–100; file = average

Step 3 — Compare with last month

Python
prev_card, prev_overall = scorecard("customers_2026-08.xlsx")
compare = pd.DataFrame({"Aug 2026": prev_card["Score"].astype(float), "Sep 2026": card["Score"].astype(float)})
compare["Change"] = compare["Sep 2026"] - compare["Aug 2026"]
compare.loc["Overall"] = [prev_overall, overall, round(overall - prev_overall, 1)]
print(compare.to_string())

A score on its own is hard to judge; a score next to last month's is not. Running the same rules over the previous file and putting the two side by side shows whether quality is improving or slipping, and in which columns. September's extract is worse than August's in every column — more missing names, more malformed emails, more country names instead of codes — and the change column says by how much: the overall score drops from 98.8 to 97.4, with email down three points to 95.3 and country nearly two points to 95.8.

Column scores, August against September Every column scores lower in September. Email falls from 98.3 to 95.3 and Country from 97.7 to 95.8. The overall score falls from 98.8 to 97.4. Customer ID is nearly perfect apart from one duplicated ID. A B C D E 1 Column Aug 2026 Sep 2026 Change Typical failure 2 Customer ID 100.0 99.9 −0.1 one duplicate ID 3 Name 99.1 98.1 −1.0 blank 4 Email 98.3 95.3 −3.0 missing @, n/a 5 Country 97.7 95.8 −1.9 UK, Germany 6 Credit limit 98.9 98.0 −0.9 0, −500, 9,999,999 7 Last order 98.6 97.3 −1.3 older than two years 8 Overall 98.8 97.4 −1.4

Scores in the high nineties can look reassuring, so it helps to translate them back into rows. Behind the country column's 95.8 are 31 customers out of 400 whose country cannot be used for tax or shipping: 13 marked UK and 11 Germany instead of the codes GB and DE, and 7 with no country at all. Those are 31 records someone will fix by hand later if they are not fixed at source now — and the UK and Germany entries suggest the fix is a single change in the system that produces the extract. That is exactly the evidence needed for a conversation with whoever produces the file, and it is far more persuasive than a list of individual errors.

Step 4 — The scorecard workbook

Python
from openpyxl import load_workbook
from openpyxl.formatting.rule import ColorScaleRule

path = "data_quality_customers_2026-09.xlsx"
out = card.copy()
for c in ["Completeness", "Validity", "Uniqueness"]:
    out[c] = out[c].astype(float)
with pd.ExcelWriter(path) as xw:
    out.reset_index(names="Column").to_excel(xw, sheet_name="Scorecard", index=False)
    compare.reset_index(names="Column").to_excel(xw, sheet_name="Trend", index=False)
wb = load_workbook(path)
ws = wb["Scorecard"]
for col in "BCD":
    for (cell,) in ws.iter_rows(min_row=2, min_col=ord(col) - 64, max_col=ord(col) - 64):
        cell.number_format = "0.0%"
ws.conditional_formatting.add(f"E2:E{ws.max_row}", ColorScaleRule(start_type="num", start_value=90, start_color="F8696B",
                              mid_type="num", mid_value=96, mid_color="FFEB84", end_type="num", end_value=100, end_color="63BE7B"))
ws.column_dimensions["F"].width = 45
wb.save(path)
print(out[["Score", "Examples"]].to_string())

The scorecard sheet lists each column with its three measures as percentages, its score coloured on a fixed red–amber–green scale, and the example failing values; the trend sheet holds the comparison with last month. The colour scale is anchored to fixed scores — red at 90 and below, amber around 96, green at 100 — so the colours mean the same every month and a column cannot look green just because all the others are worse. The scorecard is typically emailed back to the file's owner alongside the detailed list of failing rows from Validate Excel Columns Before Import with pandas, the scorecard for the overview, the row list for the fixing.

Scorecard or error list A scorecard summarises quality per column with scores and a trend, suits management and the file's owner, and shows direction over time. An error list gives every failing row and value, suits the person fixing the data, and says exactly what to change. Scorecard one score per column trend against last month for owners and managers examples, not every row Error list every failing row exact values to fix for whoever corrects the file no sense of trend

Common pitfalls

  • Placeholders counted as present. Treat n/a, - and empty strings as missing.
  • Rules scattered through code. Declare them per column in one table.
  • Uniqueness on every column. Measure it only where values must be unique.
  • Scores without examples. Show a few failing values so the fix is obvious.
  • No baseline. Score last month's file with the same rules for comparison.

Conclusion

A data quality scorecard measures every column of a file for completeness, validity and, where it applies, uniqueness, using rules declared once per column — required, pattern, allowed values, range, maximum age. Weighted into a score per column and averaged for the file, with examples of failing values and a comparison against the previous file under the same rules, it gives the owner of the data a clear, trackable measure of how good it is and where to improve.

Frequently asked questions

How do I measure data completeness in pandas? Treat NaN, empty strings and placeholders such as "n/a" as missing and take one minus the share of missing values.

How do I check values against a pattern in pandas? Use Series.str.match(pattern) on the non-blank values and take the share that match.

What dimensions should a data quality scorecard include? Commonly completeness, validity, uniqueness, consistency with allowed values and timeliness.

Why compare the score with the previous file? A trend shows whether quality is improving and makes the conversation with the data's owner concrete.

More on validation & data quality

More on validation & data quality →