Add a Data Dictionary Sheet to an Excel Report

A report that is passed around for a year collects questions: does "Revenue" include VAT? Is "Margin" a percentage or an amount? Why is "Region" blank on some rows? The answers usually live in someone's head or an old email. A data dictionary sheet answers them inside the workbook: one row per column of every data sheet, with a plain-language description, the type and unit, an example value, and a few facts profiled from the data such as how many cells are blank and the range of values. Generated by the same script that writes the report, it is never out of date. This page builds that sheet with pandas and XlsxWriter, adds a check that refuses to ship undocumented columns, and shows how to profile a workbook someone else made. It belongs to Building Multi-Sheet Excel Dashboards.

Prerequisites

pip install pandas openpyxl xlsxwriter

The examples build a small two-sheet sales report — an Orders sheet and a Customers sheet — and add the dictionary as a third sheet.

Step 1 — Keep descriptions next to the code

The descriptions are the part only people can write. Keep them in the report script, in one structure keyed by sheet and column, so a new column and its description arrive in the same code change:

import pandas as pd

orders = pd.DataFrame({
    "Order ID": ["SO-1001", "SO-1002", "SO-1003", "SO-1004", "SO-1005"],
    "Order date": pd.to_datetime(["2026-09-01", "2026-09-03", "2026-09-03", "2026-09-10", "2026-09-22"]),
    "Customer": ["C001", "C002", "C001", "C003", "C002"],
    "Revenue": [1200.0, 860.5, 430.0, 2400.0, None],
    "Margin %": [0.32, 0.18, 0.25, 0.41, None],
})
customers = pd.DataFrame({
    "Customer": ["C001", "C002", "C003"],
    "Name": ["Oakridge Farms", "Café Señor", "Zenith Logistics"],
    "Region": ["North", "South", None],
})
sheets = {"Orders": orders, "Customers": customers}

DOCS = {
    ("Orders", "Order ID"): ("Sales order number from the order system.", "", "Order system"),
    ("Orders", "Order date"): ("Date the order was placed (not shipped).", "date", "Order system"),
    ("Orders", "Customer"): ("Customer code; see the Customers sheet.", "", "Order system"),
    ("Orders", "Revenue"): ("Order value excluding VAT, after discounts. Blank for cancelled orders.", "EUR", "Finance"),
    ("Orders", "Margin %"): ("Gross margin as a fraction of revenue.", "%", "Finance"),
    ("Customers", "Customer"): ("Customer code, unique.", "", "CRM"),
    ("Customers", "Name"): ("Trading name of the customer.", "", "CRM"),
    ("Customers", "Region"): ("Sales region. Blank until the customer is assigned.", "", "CRM"),
}
print(len(DOCS), "documented columns across", len(sheets), "sheets")

Each entry holds a description, a unit and the source system. The descriptions say what readers actually need: "excluding VAT, after discounts" and "blank for cancelled orders" are exactly the answers that otherwise need an email. Explaining why a value can be blank turns a suspected data error into documented behaviour.

Step 2 — Profile each column from the data

Everything that can be measured should be measured, not typed: the type, an example, how many values are blank, how many are distinct and the range:

def profile(sheet, df):
    rows = []
    for col in df.columns:
        s = df[col]
        desc, unit, source = DOCS.get((sheet, col), ("", "", ""))
        non_blank = s.dropna()
        if pd.api.types.is_datetime64_any_dtype(s):
            kind, rng = "Date", f"{non_blank.min():%d %b %Y} – {non_blank.max():%d %b %Y}"
        elif pd.api.types.is_numeric_dtype(s):
            kind, rng = "Number", f"{non_blank.min():,.4g} – {non_blank.max():,.4g}"
        else:
            kind, rng = "Text", ""
        example = non_blank.iloc[0] if len(non_blank) else ""
        if isinstance(example, pd.Timestamp):
            example = f"{example:%d %b %Y}"
        rows.append({
            "Sheet": sheet, "Column": col, "Description": desc, "Type": kind, "Unit": unit,
            "Example": example, "Blank": int(s.isna().sum()), "Distinct": int(non_blank.nunique()),
            "Range": rng, "Source": source,
        })
    return rows

dictionary = pd.DataFrame([r for name, df in sheets.items() for r in profile(name, df)])
print(dictionary[["Sheet", "Column", "Type", "Blank", "Distinct", "Range"]].to_string(index=False))

The profile shows at a glance that Revenue and Margin % each have one blank (the cancelled order) and that one customer has no region — the same facts the descriptions explain. Distinct reveals identifiers: a column whose distinct count equals its row count, like Order ID, is a key. Because these figures are computed at every run, they track the data month by month without anyone editing the sheet.

Step 3 — Write the report with the dictionary sheet

The dictionary goes in as a normal sheet, formatted for reading: wrapped descriptions, a frozen header, and each sheet name linked to its sheet:

with pd.ExcelWriter("sales_report.xlsx", engine="xlsxwriter") as writer:
    for name, df in sheets.items():
        df.to_excel(writer, sheet_name=name, index=False)
    dictionary.to_excel(writer, sheet_name="Dictionary", index=False)

    book = writer.book
    ws = writer.sheets["Dictionary"]
    wrap = book.add_format({"text_wrap": True, "valign": "top"})
    top = book.add_format({"valign": "top"})
    link = book.add_format({"font_color": "#4338CA", "underline": 1, "valign": "top"})
    widths = {"Sheet": 12, "Column": 14, "Description": 46, "Type": 8, "Unit": 7,
              "Example": 16, "Blank": 7, "Distinct": 8, "Range": 24, "Source": 13}
    for i, col in enumerate(dictionary.columns):
        ws.set_column(i, i, widths[col], wrap if col == "Description" else top)
    for r, sheet in enumerate(dictionary["Sheet"], start=1):
        ws.write_url(r, 0, f"internal:'{sheet}'!A1", link, string=sheet)
    ws.freeze_panes(1, 0)
    ws.autofilter(0, 0, len(dictionary), len(dictionary.columns) - 1)
    for name, df in sheets.items():
        writer.sheets[name].freeze_panes(1, 0)
print("sheets:", list(sheets) + ["Dictionary"])

internal:'Orders'!A1 creates a link that jumps to another sheet in the same workbook, so readers can go from a dictionary row straight to the data. The autofilter lets them narrow the dictionary to one sheet. Placing the Dictionary sheet last keeps the data first; some teams prefer it right after a cover sheet — either way, mention it on the first sheet so readers know it exists. Internal links are covered in more depth in Add Hyperlinks to Excel Cells with Python.

Step 4 — Refuse to ship undocumented columns

The dictionary is only useful if it is complete. A check before writing — or in the report's tests — fails when a column has no description, and also when a description refers to a column that no longer exists:

def check_documented(sheets, docs):
    actual = {(name, col) for name, df in sheets.items() for col in df.columns}
    missing = sorted(actual - set(docs))
    stale = sorted(set(docs) - actual)
    return missing, stale

orders["Channel"] = ["Web", "Phone", "Web", "Web", "Partner"]
missing, stale = check_documented(sheets, DOCS)
print("undocumented:", missing)
print("documented but gone:", stale)
orders.drop(columns="Channel", inplace=True)
print("after removing it:", check_documented(sheets, DOCS))

When someone adds a Channel column without describing it, the check names it; when a column is renamed, both lists catch it — the new name is undocumented and the old one is stale. Raising an error on either keeps the dictionary honest without anyone having to remember. In a test suite, this is one assertion per report; see Test Excel Output with pytest.

Step 5 — A dictionary for a workbook you did not build

For an inherited workbook there is no description dictionary, but profiling still helps — and openpyxl can add what pandas cannot see, such as the number format of each column:

from openpyxl import load_workbook

def profile_workbook(path):
    wb = load_workbook(path, read_only=True)
    out = []
    for ws in wb.worksheets:
        if ws.title == "Dictionary":
            continue
        rows = ws.iter_rows(min_row=1, max_row=2)
        header, first = next(rows), next(rows, None)
        df = pd.read_excel(path, sheet_name=ws.title)
        for i, cell in enumerate(header):
            col = df.columns[i]
            out.append({"Sheet": ws.title, "Column": col,
                        "Number format": first[i].number_format if first else "",
                        "Blank": int(df[col].isna().sum()), "Distinct": int(df[col].nunique())})
    wb.close()
    return pd.DataFrame(out)

print(profile_workbook("sales_report.xlsx").to_string(index=False))

The profile of the report written above shows the order date carrying the date-and-time format pandas applies (YYYY-MM-DD HH:MM:SS) and everything else General — including Margin %, which readers will see as 0.32 rather than 32%. That is precisely the kind of finding a profile surfaces: the dictionary says the unit is a percentage, so the sheet should get a percentage format. For a workbook from elsewhere, this table is the starting point: fill in descriptions after asking the owner, save them in a DOCS dictionary, and from then on generate the full dictionary sheet. To explore an unfamiliar workbook more broadly first, Read All Sheets from an Excel File into DataFrames is a good companion.

Common pitfalls

• Typing profiled facts by hand. Blanks, ranges and types go stale; compute them.
• Descriptions in a separate document. Keep them in the code that writes the columns.
• No completeness check. New and renamed columns slip through undocumented.
• Describing the obvious. "Revenue: the revenue" helps nobody; state VAT, discounts and blanks.
• Hiding the dictionary. Point to it from the first sheet.

Conclusion

A data dictionary sheet makes a report explain itself. Keep human-written descriptions, units and sources in a dictionary in the report script, profile type, example, blanks, distinct counts and ranges from the data on every run, and write the result as a formatted sheet with links to each data sheet. A check that fails on undocumented or stale columns keeps it complete, and the same profiling gives you a head start on documenting a workbook someone else built.

Frequently asked questions

What should an Excel data dictionary include? One row per column with the sheet, column name, a plain-language description, type, unit, an example value, the number of blanks, the number of distinct values, the range and the source system.

How do I generate a data dictionary with pandas? Loop over each DataFrame's columns, measure the dtype, blanks with isna, distinct values with nunique and the minimum and maximum, merge in descriptions from a dictionary, and write the result as its own sheet.

How do I link from the dictionary to each sheet? With XlsxWriter, use write_url with an internal link such as internal:'Orders'!A1. Clicking the cell jumps to that sheet.

How do I make sure every column is documented? Compare the set of sheet and column pairs in the data with the keys of the description dictionary, and fail when a column is missing or a description refers to a column that no longer exists.

Related

• Up one level: Building Multi-Sheet Excel Dashboards — workbooks with several connected sheets.
• Make an Excel Report Accessible — making the report usable for everyone.
• Generate an HTML Report from Excel Data with Jinja2 — another way to explain a report.
• Tell Blank Cells, Empty Strings and Zeros Apart in Excel Data — what a blank count really counts.