Document Every Spreadsheet Column So Other People Can Use the Data

A spreadsheet data dictionary is a companion table that explains each dataset column. A practical starting set includes the column’s exact name, a readable label, its definition, datatype, units or format, and permitted values or constraints. For many projects, a dedicated worksheet is enough. When exported CSV data needs software-readable structure or validation rules, CSVW metadata can provide an optional additional layer.

Copy-ready spreadsheet data dictionary template

Create a worksheet named data_dictionary. Copy the following tab-separated header row and paste it into the first cell. In spreadsheet software that recognizes tab-separated text, each heading should occupy a separate column.

variable_name   readable_label  definition  datatype    format_or_unit  allowed_values_or_constraints   description

As practical completeness guidance, add one dictionary row for each source-data column. A dataset with 12 columns would therefore have 12 corresponding dictionary rows.

Field	What to enter	Status
variable_name	Column name copied exactly from the dataset	Core
readable_label	Short, human-friendly label	Core
definition	Intended meaning of the field	Core
datatype	Expected kind of value, such as text, number, date, or boolean	Core
format_or_unit	Date format, measurement unit, or another formatting rule	Core
allowed_values_or_constraints	Accepted categories, ranges, required status, or uniqueness rules	Core
description	Additional interpretation or context	Optional

This is a practical template, not a mandatory standard. Its human-facing fields build on OSF Support’s guidance for recording exact variable names, readable names, measurement units, allowed values, definitions, and optional descriptions.

datatype is a useful addition for tabular exports because CSV does not itself declare the datatype or uniqueness requirements of a column, as explained in the W3C CSV on the Web primer.

Depending on the project, you might also add example_value, notes, source, missing_value_convention, or calculation_rule. These are optional editorial recommendations rather than universal data-dictionary requirements.

Worked example: document a small synthetic dataset

The dataset below is entirely synthetic. Its records, categories, ranges, and constraints were invented for this tutorial.

Source-data preview

record_id	status	duration_min	visit_date
R-001	active	35	2026-04-03
R-002	inactive	0	2026-04-04
R-003	unknown	125	2026-04-06

A completed dictionary can separate semantic documentation—what a field means—from technical documentation—what valid values should look like.

Names and meanings

variable_name	readable_label	definition	description
record_id	Record ID	Identifier assigned to one synthetic record.	Distinguishes records and has no meaning outside this example.
status	Record Status	Category indicating the record’s current example status.	unknown means the status was not determined; it does not mean inactive.
duration_min	Visit Duration	Number of minutes assigned to the synthetic visit.	The example range is intentionally bounded for demonstration.
visit_date	Visit Date	Calendar date on which the synthetic visit occurred.	Store and exchange the value in the documented date format.

Types, formats, and constraints

variable_name	datatype	format_or_unit	allowed_values_or_constraints
record_id	Text	R- followed by three digits	Required and unique in this example
status	Category	Lowercase text	active, inactive, or unknown
duration_min	Number	Minutes	Minimum 0; maximum 480
visit_date	Date	YYYY-MM-DD	Required; must follow the stated format

These distinctions matter. record_id is the exact machine-facing header, while “Record ID” is a concise display label. Its definition states what the field represents, and its description adds context that would make the definition unnecessarily long.

Likewise, declaring duration_min as numeric does not reveal whether it measures seconds, minutes, or hours. The unit supplies that information. The 0–480 range and every other rule in the example apply only to this invented dataset and should not be transferred to a real one without confirming its actual definitions and operating rules.

How to complete each dictionary field

Start by copying every variable_name exactly as it appears in the source spreadsheet. Preserve underscores, capitalization, abbreviations, and spelling. An approximate name can break the connection between the documentation and the column it is intended to explain.

Next, add a short readable_label. Labels may contain spaces and conventional capitalization, and they should be suitable for a table, chart, or figure. Keep both fields: replacing duration_min with “Visit Duration” would discard the exact reference needed to match the documentation to the source column.

Write a concise definition that reflects what the dataset creator intends the term to mean. Avoid circular wording such as “Status is the status of the record.” One useful pattern is genus-differentia: A is a B that C. For example:

Visit duration is a measurement that records elapsed visit time in minutes.

This pattern is a writing aid, not a requirement for every field.

Record units whenever reasonable alternatives could be confused. A time value might represent seconds, minutes, or hours. Another number might represent individual items, thousands of items, currency, distance, or a percentage. The datatype alone cannot resolve those ambiguities.

For categorical fields, list the accepted values and explain codes whose meanings are not obvious. For a genuinely bounded numeric field, record the applicable minimum and maximum. Do not invent a range merely to fill the cell.

Use description for interpretation notes, caveats, provenance, calculation details, or citations that would make the concise definition unwieldy. Synonyms and longer explanations can also be maintained in optional columns when readers are likely to search for alternative terminology.

Document constraints that a header row cannot express

A header identifies a column, but it ordinarily does not communicate the column’s complete definition, datatype, unit, required status, uniqueness rule, accepted categories, or valid range. A header such as score, for example, does not reveal whether the value is an integer, percentage, standardized score, or text code.

Separate two kinds of documentation:

• Semantic documentation explains what the field means and how readers should interpret it.
• Technical constraints describe what valid contents should look like.

Useful constraint categories include:

• Datatype: text, number, integer, date, date-time, or boolean.
• Required status: whether a value must be present.
• Uniqueness: whether duplicate values are permitted.
• Enumeration: the accepted set of categories.
• Numeric range: an applicable minimum and maximum.
• Date rules: the expected representation or relevant bounds.
• Boolean representation: for example, true/false or another explicitly defined pair.

CSVW metadata can express datatypes, required and unique values, enumerations, ranges, dates, and booleans. These constraints can expose values that merit investigation, but they cannot establish that a dataset is accurate or complete.

Document the missing-value convention the dataset actually uses. A blank cell, unknown, and not applicable can carry different meanings. There is no need to impose one supposedly universal code; define the project’s real convention clearly and use it consistently.

A dictionary is a reference rather than an automatic enforcement mechanism. Its documented rules may later be implemented in an appropriate validation tool, but writing down a constraint does not by itself cause spreadsheet software to enforce it.

Quality-check the dictionary against the dataset

Before sharing the files, compare the dictionary with the current dataset:

• [ ] Every dataset column has one corresponding dictionary row.
• [ ] Every recorded variable_name matches its source header exactly.
• [ ] Every readable label is concise and understandable.
• [ ] Definitions state intended meanings without circular wording.
• [ ] Numeric measures include units where applicable.
• [ ] Date and identifier formats are explicit where needed.
• [ ] Category lists and numeric ranges reflect the intended rules.
• [ ] Missing, unknown, and not-applicable values are explained.
• [ ] Descriptions add context instead of repeating definitions.
• [ ] Example values agree with the documented datatypes and constraints.

Compare actual values with the dictionary, not just the headers. In the synthetic example, pending in the status column would be undeclared because the documented list contains only active, inactive, and unknown. A duration_min value of 600 would fall outside the invented 0–480 range.

Either result should be investigated, but neither automatically proves that the data is wrong. The value might be erroneous, or the dictionary might be outdated or incomplete.

Revisit the dictionary whenever a column is added, renamed, removed, or redefined. This is practical maintenance advice rather than a formal versioning standard. A current dictionary improves interpretation and supports consistency checks, but it cannot guarantee accuracy or prevent every data-entry problem.

Choose a worksheet dictionary or machine-readable metadata

Choose the least complex format that satisfies the needs of the people and systems using the data.

Approach	Best suited to	Main advantage	Main tradeoff
Dedicated dictionary worksheet	People maintaining one spreadsheet	Easy to read beside the data	Constraints may not be machine-enforced
Separate documentation file	Data shared in several formats or locations	Can accompany CSV and other exports	Data and documentation can become separated
CSVW JSON metadata	Exported tabular data used by compatible software	Machine-readable structure and constraints	More technical and dependent on tool support

A worksheet is often the approachable choice when people primarily need to read and maintain definitions alongside a spreadsheet. That is workflow guidance, not a prescribed standard.

Because CSV lacks built-in declarations for datatypes, uniqueness, and similar rules, external metadata can supply a structured layer. CSVW can describe table structure and constraints and provide instructions for transformations involving exported CSV data. One metadata document can describe either one CSV file or a group of CSV files.

A minimal illustrative fragment looks like this:

{
  "@context": "http://www.w3.org/ns/csvw",
  "url": "visits.csv"
}

This fragment identifies the CSVW metadata context and the target CSV; it is not a complete production schema. The W3C primer recommends file-specific names formed by appending -metadata.json, such as countries.csv-metadata.json, and explains how metadata can cover one or several CSV files. The primer is non-normative and points to separate W3C Recommendations for normative definitions.

Treat CSVW as an optional layer for exported tabular data. Do not assume that it directly validates native XLSX files or that every current tool supports it.

Prepare the documented dataset for public sharing

Run one final column-to-dictionary comparison before exporting or publishing. Confirm that someone outside your team can interpret abbreviated field names, coded categories, units, missing values, and constraints without relying on undocumented institutional knowledge.

Use synthetic or already-public tutorial data. A dictionary makes fields easier to interpret; it does not make confidential, personal, or otherwise sensitive information suitable for public release.

TablePage publishes spreadsheets as public interactive data pages, so review both the data and its documentation with public visibility in mind. Keep the dictionary with the publishing workflow, but do not assume that a publishing service will create, validate, enforce, or preserve dictionary metadata unless its current product documentation explicitly says so.

Frequently asked questions

Is there one official spreadsheet data dictionary standard?

The copy-ready structure in this guide is a practical synthesis for human-facing documentation, not an official universal standard. Different disciplines, organizations, repositories, and software workflows may require additional fields or another metadata model.

The central principle is consistent correspondence: each dataset column should have an exact matching entry that explains its meaning and valid contents.

Can one CSVW metadata file describe several related CSV files?

Yes. A CSVW metadata document can describe one CSV file or a group of related CSV files. For multi-table publishing, this can keep table structures and constraints together, although you should confirm compatibility with the software that will consume the metadata.

Start by creating a dictionary worksheet, copying every column name exactly, adding a readable label and precise definition, documenting units and permitted values, and checking the dictionary against the dataset. Keep the data and dictionary together when sharing. Add machine-readable metadata such as CSVW only when exported CSV data needs software-readable structure or constraints.