Skip to content
Download
USE CASE

Reference data and lookup lists, kept current in Excel

Order statuses, currency codes, units, region lists, the small controlled tables the rest of your database joins to. Workbook Connect lets the person who owns each list maintain it in Excel, with the foreign keys and your validation rules guarding every change, and no IT ticket in between.

Download free

Lookup lists that stay in sync

Add a code, rename a label, retire a value, and commit. The dropdowns and joins that depend on the list see the change the moment the transaction lands.

EXCELDATABASE

Small tables, outsized blast radius

Reference tables are tiny, often a few dozen rows, and that is exactly why they get neglected. RefOrderStatus might hold six codes, yet every row in SalesOrder points at one of them, and so does every report, every Power BI slicer, and every join downstream. Add a new status like "On credit hold" and someone hand-edits production with an INSERT. Retire a code and an order somewhere still references it, so a join quietly drops rows or a dashboard shows a blank category. The list nobody owns is the list that breaks the most things, because everything depends on it and nothing protects it.

How a lookup table actually connects

A reference table is the one side of a one-to-many: SalesOrder stores a StatusCode and a CustomerID as foreign keys, and the meaning of each code lives once in RefOrderStatus. Change the list and you change what every order resolves to.

∞1StatusCode∞1CustomerIDRefOrderStatusStatusCodePKLabelSortOrderIsActiveSalesOrderOrderIDPKCustomerIDFKStatusCodeFKOrderDateTotalAmountCustomerCustomerIDPKNameCountry

An illustrative data model, not your exact schema.

Let the list owner maintain it, in the grid they know

Workbook Connect points Excel straight at RefOrderStatus. The owner opens it from the ribbon, sees the live rows, and edits them like any sheet: type a new label, set IsActive to No, add a code in a fresh row, or flag one for deletion. A Configurator sets the table up once, hides technical columns, maps the StatusCode primary key, and that configuration is shared to the whole team automatically on connect, so everyone opens the same guard-railed view. Because the table is live, the codes other sheets look up through their foreign keys are always the codes that exist right now, not a stale picklist someone pasted in last quarter.

Maintaining the status list as you edit it

Rename a label, add a code, flag one for deletion, and let the per-row status track every change. If a change would orphan a referencing row, the commit is blocked until you fix it, and nothing reaches the table until every row passes.

RefData.xlsx, RefOrderStatus
StatusStatusCodeLabelSortOrderIsActive
NEWNew10Yes
ChangedPICKPicking20Yes
SHIPShipped30Yes
NewHOLDOn credit hold25Yes
Needs fixBACKORDBackordered20No
ChangedCANCCancelled40Yes
Commit Validated, then written to dbo.RefOrderStatus
  • Row 2: "Awaiting fulfilment" was renamed to "Picking". The StatusCode stays PICK, so every SalesOrder already pointing at it now reads the new label, with no re-keying.
  • Row 5: IsActive set to No, but 47 rows in SalesOrder still reference StatusCode BACKORD. Deactivating a code that live orders point at would orphan those rows, so the commit is blocked until this row is fixed. Nothing is written until every row passes, then they all commit together.

An illustrative example of the editing workflow, not a product screenshot.

Validation runs before referential integrity can break

Reference data is where a careless edit propagates the furthest, so it is where validation earns its keep. Workbook Connect validates the whole sheet in Excel first, and the per-row status column marks each line as new, changed, or flagged for deletion. Deactivate BACKORD while 47 live orders still point at it and the commit is blocked, with the dependent count and the reason on the row you need to fix; nothing is written until you resolve it. Add a duplicate StatusCode and the primary key catches it before the write too. You fix the flagged rows and commit again, and then every row goes to RefOrderStatus together in one transaction, inside your existing database permissions, and Workbook Connect makes no schema changes of its own. If someone edited the same code while you were working, the conflict surfaces on commit instead of one of you silently winning.

Works with your database

FAQ

Reference data, answered

Master data is the business entities themselves, the rows in Customer, Product, or Employee. Reference data is the short controlled lists those entities point at: order statuses, currency codes, units of measure, region codes. A SalesOrder row carries a StatusCode; the meaning of NEW, PICK, or SHIP lives once in RefOrderStatus. Workbook Connect edits both kinds of table the same way, but reference tables are where a single bad code does the most damage, because dozens of other tables depend on it.

No, and that is the point of a code-and-label list. You change the Label in RefOrderStatus while the StatusCode stays the same. Every SalesOrder still stores PICK, so the new label "Picking" shows up everywhere that joins to the status table the moment you commit. The codes are the contract; the labels are free to read better.

The foreign keys in your own schema, plus any validation rules you author. Delete or deactivate a code that live rows still reference and the commit is blocked, with the count of dependents and the reason on the row you need to fix; nothing is written until you resolve it, so the orders are never orphaned. Workbook Connect never weakens your constraints or changes your schema; it commits inside them.

Yes. Configure a foreign-key lookup on SalesOrder.StatusCode against RefOrderStatus and editors picking a status get the real, current codes offered to them as they type, so an order can only ever reference a status that exists. Keep the reference table tidy in Workbook Connect and the lookups everywhere downstream stay tidy with it.

The person who owns the meaning of the list, usually someone in operations or finance rather than IT. They sign in with your existing authentication (Entra ID, Service Principal, SQL auth, and others) and see exactly the tables your database permissions already grant them. Roles add a second layer: a Configurator sets up the lookups and visible columns once, and Users just maintain the values.

No. The list you open in Excel is the table in your database. Edits are batched into one transaction and written on commit; there is no export, no CSV, and no reload job to schedule afterwards. If someone else touched the same code while you were editing, optimistic concurrency catches the conflict on commit rather than letting one of you overwrite the other.

Related use cases

Keep your lookup tables honest

Download Workbook Connect free and connect it to your database. Hand each reference list to the person who owns its meaning, and let the keys and your rules keep every referencing table safe.

Download free