Build a Data Dictionary for a Spreadsheet You Inherited

The workbook works until somebody asks what a column means

The monthly workbook opens without an error. Its totals reconcile to the previous report. Then a colleague asks whether Amount includes tax, whether Date means receipt or payment, and whether a blank Status means pending. Nobody can answer from the sheet. The formulas may be functioning while the interpretation remains uncertain.

This is a metadata problem before it is an analysis problem. The team needs a shared account of what the fields mean and how they are used. Asking AI to summarise the rows first risks turning those missing definitions into fluent assumptions. A confident explanation of the wrong unit is still the wrong explanation.

A data dictionary provides a place to separate what can be seen from what must be confirmed. It can begin as a modest table alongside the workbook. It does not require a new platform or a complete data-governance programme. Its value comes from the quality of its definitions, source references and ownership.

The Australian Bureau of Statistics describes metadata as central to interpretability in its national accounts methodology. That is a useful principle for this task, although the ABS does not prescribe this workplace template or endorse an AI-generated dictionary. Readers need enough context to understand what the numbers represent. Source: ABS

Preserve a reference copy and define the inspection boundary

Identify the workbook version before inspecting it. Record its filename, supplied date and responsible role. Work from an authorised copy or read-only view, and note the sheets included. Do not describe a dictionary as covering the entire workbook if the inspection only examined one exported table.

A spreadsheet can contain formulas, hidden sheets, notes, validation rules, named ranges and links to other files. A flat export may not preserve all of these features. Record which features were actually inspected. An AI conversation supplied with pasted rows cannot establish that the original workbook has no hidden dependencies.

Begin with a schema-only extract where possible. Column headings, a few deliberately constructed values and descriptions of formulas may be enough to expose the definition questions. If real data is necessary for a later authorised review, the organisation must assess that use separately. The introductory exercise does not require employee or customer records.

For organisations covered by the Privacy Act, the OAIC's guidance on commercially available AI products recommends checking the product's data flows, terms and settings before any personal information is entered. It does not make a schema automatically safe: a column heading or filename can itself reveal something sensitive. Review the complete source pack rather than checking only the sample rows. Source: OAIC

Distinguish an observed value from a business definition

An observed field named Amount contains numeric-looking entries. That observation does not establish currency, whether the values include tax or whether a negative amount represents a reversal. A display format might show a dollar symbol without identifying which dollar currency applies. Write the observation precisely and leave the business meaning open.

The same discipline applies to dates. A cell displayed as 03/04/2026 may be interpreted differently depending on the format and source convention. Even when the calendar date is known, its business meaning may remain uncertain. It could be the date a request arrived, a transaction was posted or a record was last updated.

Codes are particularly easy to overinterpret. P might mean pending, paid, provisional or a category unrelated to progress. A model can propose these possibilities, but the dictionary must not promote the most plausible one into a definition. The question for the owner is which source establishes the code's meaning in this workbook.

Use distinct fields for observed representation, proposed interpretation and confirmed definition. If the team has no use for hypotheses, omit that field entirely. A dictionary does not need to preserve every model suggestion. It does need to prevent a suggestion from becoming a source for later analysis through repetition.

Work through a constructed inherited schema

The example is a fictional private-sector operations team receiving a monthly service-request workbook. It has eight fields: Request_ID, Amount, Amount_k, Date, Status, Team, Closed and Rate. A second sheet contains a note saying amounts are reported in thousands. The main sheet's Amount column displays values such as 1250 and 980. All values and notes are constructed.

The dictionary begins with field locations and observed representations. Request_ID contains text identifiers with leading zeros, such as 001-A. Amount and Amount_k contain numbers, but their relationship is not established. Date contains displayed dates. Status contains P, C and blanks. Team contains short codes. Closed contains Y, N and a blank. Rate contains decimal values.

The model receives the schema, the constructed sample and the note as separate source items. It is asked to profile these supplied items and produce questions, not to repair the workbook. It must preserve the conflict between the note about thousands and the apparent scale of Amount. It cannot resolve that conflict by multiplying values.

For Request_ID, the proposed dictionary records an identifier stored as text and warns against losing leading zeros. The owner still needs to confirm uniqueness and scope. The observation that a three-row example has no duplicate identifiers does not establish uniqueness across the full workbook or across months.

For Amount, the draft records numeric representation, currency unknown, tax treatment unknown and unit conflict unresolved. It cites the main-sheet sample and the separate note. For Amount_k, it asks whether the field is a derived presentation of Amount or an independently maintained value. Similar values would not be sufficient evidence of the intended relationship.

For Status, the draft lists the observed codes without expanding them. It asks whether blanks mean missing, not applicable or not yet assigned. For Closed, it records the visible Y/N pattern and asks which event changes the field. The model must not conclude that Status C and Closed Y are interchangeable.

Rate deserves a separate question. A decimal such as 0.08 could represent a proportion, a price or another quantity. The dictionary asks for the numerator, denominator, unit and applicable period. A percentage display would improve readability only after the meaning is confirmed. Formatting is not a substitute for a definition.

Ask questions that an owner can answer

A long list of "please clarify" comments makes the dictionary difficult to finish. Convert uncertainty into a specific decision request. Instead of asking what Amount means, ask which currency and unit apply, whether tax is included, and where those definitions are documented. Keep each answer tied to its supporting source.

Prioritise questions by the decisions they affect. If the workbook feeds a financial summary, the unit conflict deserves attention before standardising team-code spelling. If the immediate task is routing requests, the meaning of Status and Closed may be more urgent. The owner and intended use determine priority, not the model's confidence score.

Record who can confirm each definition by role. A workbook maintainer may explain a formula but lack authority to define the business measure. A data owner may define the measure but need a systems colleague to confirm how an export represents missing values. One person does not have to answer every question.

When an answer arrives, retain the date and evidence reference. "Confirmed by owner" is better than an unlabelled assertion, but a durable dictionary also explains what was confirmed. If the answer covers only the current reporting period, do not silently generalise it to historical workbooks with different layouts.

Keep formulas and missing values visible

Document formulas as calculations, including their inputs and intended output. Where feasible, retain the exact formula or a stable cell/range reference in supporting material. A plain-language paraphrase can help a reader, but it must not conceal a denominator, filter or rounding step that affects the result.

Distinguish formula results from manually entered values. Two cells displaying the same number may have different maintenance requirements. If an export contains only values, record that limitation. Do not claim to have inspected calculation logic when the supplied material did not include it.

Missing values need their own rules. A blank, zero, dash and not-applicable code may each have a different meaning. Replacing them with a common value can change totals and classifications. The dictionary should state the permitted representations, their meanings and any rule for excluding them from a calculation.

In the constructed workbook, the blank Closed value stays unresolved. It is not converted to N. That preserves the difference between a confirmed open request and a record with no closure information. The distinction may be important to the downstream task even though both rows look unfinished to a casual reader.

Review the dictionary against the actual workbook

Once a draft exists, check a selection of fields back to their source locations. Confirm spelling, data type, display format and any stated rules. Inspect examples that challenge the proposed definition, such as negative amounts, repeated identifiers or dates outside the expected period. Record the inspection scope so a limited check is not mistaken for a full audit.

Ask a colleague to use the dictionary to explain a row. If they cannot distinguish an observed value from a confirmed business meaning, revise the labels. A technically complete table can still mislead if every column looks equally authoritative or if unresolved fields are hidden in footnotes.

Do not let a clean dictionary imply that the underlying data is high quality. The dictionary explains intended meaning and observed structure. Separate checks are needed for completeness, validity, consistency and fitness for the particular decision. A documented field can still contain incorrect values.

Likewise, a field definition does not authorise a new use. Knowing what an employee-related column means is different from having permission to use it in an AI workflow. Keep data interpretation, access and decision authority separate. The dictionary helps people ask better questions; it does not answer every governance question itself.

Connect the dictionary to future changes

Give the dictionary its own version and link it to the workbook version or export specification it describes. Keep field identifiers stable where possible. A heading can change from Date to Received_Date without changing the underlying concept, while an unchanged heading can conceal a changed calculation.

When a field changes, record both the old and new meaning and the effective period. Downstream users need to know whether historical comparisons remain valid. Do not overwrite the earlier definition and leave previous reports appearing to have used the new rule. A small change log can prevent a large interpretive mistake.

Assign a review trigger that fits the workflow. A new export version, changed formula or revised code list is a concrete reason to revisit the dictionary. A periodic review can supplement those triggers, but it should not become a substitute for checking known changes when they occur.

Keep the dictionary accessible to the people using the workbook. If it lives in a separate file, make the relationship obvious and test the reference. If it lives in another sheet, ensure users of exported data receive the relevant definitions. Documentation that disappears during export cannot support the recipient's interpretation.

Start with the fields that determine the answer

Choose a small group of consequential columns and document them properly. In this example, Amount, Amount_k, Status and Rate deserve early attention because their unknown meanings could change the result of a summary. The remaining fields can be added without pretending that the first pass covered everything.

Use AI to organise the observations and sharpen the owner questions. Keep the confirmed definitions under human control. The useful output is a dictionary that makes uncertainty visible and can be maintained alongside the workbook. Before asking what the spreadsheet says, establish what its fields mean.

Reusable materials

Data-dictionary working pack

Fictional private-sector service-request workbook. Values below are deliberately constructed, not observed in a production workbook.

Schema and sample

Source S1: main-sheet sample above. Source S2: fictional Notes sheet says "Amounts reported in thousands". S2 does not identify which amount column it describes. [blank] denotes an empty cell, not literal source text.

Draft dictionary

Maintained template

Prompt

Use only S1, S2 and the supplied workbook scope. Build a draft data dictionary, preserving exact field names and source locations.
Separate directly observed structure from hypotheses and confirmed definitions. No supplied definition means UNKNOWN.
Identify conflicting units, ambiguous dates, codes, missing-value conventions and formulas not supplied. Do not convert values or infer currency, tax treatment, denominators, business meaning or approval.
Exclude real employee/customer information and confidential workbook content. This exercise uses the constructed sample only.
Return the dictionary plus prioritised, specific questions for owner roles. State which workbook features were not available for inspection.
Do not claim data quality, uniqueness across the full workbook or successful formula testing. A human validates the dictionary before downstream use.

Review checklist

• Preserve eight field names and the S1/S2 unit conflict.
• Keep leading zeros and negative values visible.
• Do not expand P/C or interpret blanks without an owner source.
• Record formula inspection separately from displayed values.
• Link dictionary version to workbook/export version and retain changes.

Related reading

• Reconcile Two Spreadsheets Without Trusting the Answer
• What Does Your Risk Appetite Measure Actually Mean?

References

1. Australian Bureau of Statistics, Australian System of National Accounts: Concepts, Sources and Methods, Edition 8, Chapter 24 Quality of the National Accounts, released 20 January 2026, paragraphs 24.83 to 24.85: https://www.abs.gov.au/book/export/46789/print
2. Office of the Australian Information Commissioner, Guidance on privacy and the use of commercially available AI products, published 21 October 2024 and updated 17 January 2025: https://www.oaic.gov.au/privacy/privacy-guidance-for-organisations-and-government-agencies/guidance-on-privacy-and-the-use-of-commercially-available-ai-products

General information only. Not legal, compliance, financial, or professional advice.