Export Database Lookup Tables as Excel Dropdown Lists

Templates that people fill in — expense claims, timesheets, budget returns, purchase requests — almost always ask for codes that live in a database: cost centres, project numbers, expense types, suppliers. When the template's dropdowns are typed in by hand, they drift out of date within weeks: a new project is missing, a closed cost centre is still offered, and the returns come back with codes the database rejects. Generating the dropdowns from the database itself keeps the template honest. This page reads three lookup tables, writes the active values to a hidden sheet, defines a named range for each list, attaches data validation to the template's input columns, shows each code with its description so people pick the right one, refreshes the lists in a template that already exists, and checks the values in a returned file against the database. It belongs to Moving Data Between Excel and Databases.

Prerequisites

pip install pandas openpyxl

The examples use a SQLite database with three lookup tables: cost centres with an active flag, projects with a closing date, and expense types with a sort order. With another database only the connection changes.

Step 1 — Read only the values people may choose

import sqlite3
from contextlib import closing
import pandas as pd

QUERIES = {
    "CostCentres": "SELECT code, name FROM cost_centres WHERE active = 1 ORDER BY code",
    "Projects": "SELECT code, name FROM projects WHERE closed_on IS NULL OR closed_on >= date('now') ORDER BY code",
    "ExpenseTypes": "SELECT code, name FROM expense_types ORDER BY sort_order",
}
with closing(sqlite3.connect("finance.db")) as con:
    lists = {name: pd.read_sql(q, con) for name, q in QUERIES.items()}
for name, df in lists.items():
    df["Label"] = df["code"] + " – " + df["name"]
    print(name, df["Label"].tolist())

The queries are where the business rules live: only active cost centres, only projects that are still open, expense types in the order the finance team wants them listed rather than alphabetically. Inactive and closed values — here one closed cost centre and one finished project — are left out of the dropdowns deliberately — they still exist in the database for historical reports, but nobody should pick them for a new claim. Each value gets a label combining code and description, CC110 – Sales, because people recognise the name and the database needs the code. The en dash with spaces is a separator that will not appear inside a code, which matters when the label is split back apart.

Step 2 — Lists sheet, named ranges and validation

from openpyxl import Workbook
from openpyxl.workbook.defined_name import DefinedName
from openpyxl.worksheet.datavalidation import DataValidation
from openpyxl.utils import get_column_letter

def write_lists(wb, lists):
    ws = wb["Lists"] if "Lists" in wb.sheetnames else wb.create_sheet("Lists")
    ws.delete_cols(1, ws.max_column)                         # start clean on refresh
    for col, (name, df) in enumerate(lists.items(), start=1):
        letter = get_column_letter(col)
        ws.cell(1, col, name)
        for r, label in enumerate(df["Label"], start=2):
            ws.cell(r, col, label)
        wb.defined_names[name] = DefinedName(name, attr_text=f"Lists!${letter}$2:${letter}${len(df) + 1}")
    ws.sheet_state = "hidden"

wb = Workbook()
claim = wb.active
claim.title = "Claim"
claim.append(["Date", "Cost centre", "Project", "Expense type", "Amount", "Description"])
write_lists(wb, lists)
for column, name in [("B", "CostCentres"), ("C", "Projects"), ("D", "ExpenseTypes")]:
    dv = DataValidation(type="list", formula1=f"={name}", allow_blank=True, showErrorMessage=True,
                        errorTitle="Choose from the list", error=f"Pick a value from the {name} list.")
    claim.add_data_validation(dv)
    dv.add(f"{column}2:{column}200")
for col, width in zip("ABCDEF", [12, 28, 28, 22, 12, 40]):
    claim.column_dimensions[col].width = width
wb.save("expense_claim_template.xlsx")
print(wb.sheetnames, list(wb.defined_names.keys()))

The lists go on their own sheet, one column each, and that sheet is hidden so the template shows only the input area. Each list gets a workbook-level named range covering exactly its values, and the validation rules refer to the names — =CostCentres — rather than to cell addresses. That indirection is what makes refreshing easy later: when a list grows, only the name's range changes, and every dropdown that uses it follows. Each rule covers the first two hundred input rows and shows an error message naming the list if someone types a value that is not on it, so a mistake is caught while the claimant is still at the keyboard. Dropdowns in depth, including dependent ones, are covered in Create Dependent Dropdown Lists in Excel with Python.

A list sheet is not just tidier than typing the values into the validation rule itself; it is necessary for real lookup tables. A list written directly into a rule — formula1='"CC100,CC120,CC140"' — is limited to 255 characters in Excel, which a few dozen cost centres with descriptions exceed easily, and the rule then has to be rewritten every time the list changes. A range on a sheet has no such limit, can hold thousands of values, and is updated in one place.

Step 3 — Refresh the lists in an existing template

from openpyxl import load_workbook

with closing(sqlite3.connect("finance.db")) as con, con:
    con.execute("INSERT OR REPLACE INTO projects VALUES ('P-2060', 'Office fit-out', NULL)")
    con.execute("UPDATE cost_centres SET active = 0 WHERE code = 'CC110'")
    lists = {name: pd.read_sql(q, con) for name, q in QUERIES.items()}
for df in lists.values():
    df["Label"] = df["code"] + " – " + df["name"]

wb = load_workbook("expense_claim_template.xlsx")
write_lists(wb, lists)
wb.save("expense_claim_template.xlsx")
wb = load_workbook("expense_claim_template.xlsx")
print({n: d.attr_text for n, d in wb.defined_names.items()})
print([c.value for c in wb["Lists"]["B"]])

When the lookup tables change — a new project opens, a cost centre closes — the template is refreshed rather than rebuilt: load it, rewrite the lists sheet, redefine the names, save. The input sheet and its validation rules are untouched, because they point at names, not at fixed ranges. After the refresh the Projects list has the new office fit-out project and its named range has grown by one row, to B2:B5, and Sales has disappeared from the cost centres, whose range has shrunk to A2:A4. Running the refresh on a schedule, or whenever the lookup tables change, keeps every copy of the template generated from then on in line with the database.

Step 4 — Check returned files against the database

returned = pd.DataFrame({"Cost centre": ["CC100 – Marketing", "CC110 – Sales", "CC999 – Typo"],
                         "Project": ["P-2041 – Website rebuild", "P-2060 – Office fit-out", "P-2042 – Warehouse move"]})
codes = returned.apply(lambda col: col.str.split(" – ").str[0])
valid = {"Cost centre": set(lists["CostCentres"]["code"]), "Project": set(lists["Projects"]["code"])}
problems = [(i + 2, col, returned.at[i, col]) for col in codes for i, code in codes[col].items() if code not in valid[col]]
print(codes.to_string(index=False))
print("problems:", problems)

Data validation stops mistakes at typing time, but not values pasted in from elsewhere, nor a template that was downloaded before a cost centre closed. When filled-in files come back, split each label at the separator to recover the code — the part the database stores — and check it against the current active values. Here the check catches a claim against CC110, valid when the template was issued but deactivated since, a typed CC999 that never existed, and a closed project, each reported with its row number so the claimant can correct it. The first line, CC100 on the website project, passes. It is worth deciding in advance what happens to a code that was valid when the template went out but has since closed: some teams accept it for a grace period, others ask for the claim to be recoded, and either rule is a small addition to this check.

Common pitfalls

• Dropdowns typed by hand. Generate them from the lookup tables.
• Offering inactive values. Filter in the query; keep history in the database, not the dropdown.
• Validation pointing at fixed ranges. Use named ranges so refreshes extend the lists.
• Labels that cannot be split back. Use a separator that never appears in a code.
• Trusting validation alone. Check returned values against the database on import.

Conclusion

To give a template dropdowns that match the database, query only the values people may choose, write them as code – description labels to a hidden lists sheet, define a named range per list and point each column's data validation at the name. Refresh the lists by rewriting that sheet and its names, which leaves the input sheet untouched, and when files come back, split the labels back into codes and check them against the current lookup tables.

Frequently asked questions

How do I create an Excel dropdown from a database table with Python? Read the values with pandas, write them to a sheet, define a named range over them, and add a DataValidation(type="list", formula1="=Name") to the input cells.

How do I hide the sheet that holds the dropdown values? Set ws.sheet_state = "hidden" on the lists sheet; the dropdowns still work.

How do I update dropdown lists in an existing template? Load the workbook, rewrite the lists sheet and redefine the named ranges; validation that refers to the names follows automatically.

Why show "code – description" in the dropdown? People recognise descriptions while the database needs codes; splitting the label at the separator recovers the code on import.

Related

• Up one level: Moving Data Between Excel and Databases — Excel and databases.
• Add Dropdown Data Validation to Excel with openpyxl — data validation basics.
• Define Names and Protect Sheets with XlsxWriter — named lists in XlsxWriter.
• Read Excel Attachments into a Database Nightly — loading the returns.