Validate Column Data Types Before Publishing a Spreadsheet

Before publishing, assign one intended data type to every column, flag cells that do not match it, and document intentional exceptions. The result should be a table in which numbers sort numerically, dates follow one convention, identifiers retain significant zeros and missing values cannot be mistaken for observations.

This matters especially for CSV. A CSV contains rows and cell values, but the file alone does not declare that a column is a number, date or identifier. The W3C CSV on the Web primer therefore describes separate metadata for column datatypes, expected formats and validation rules (W3C CSV on the Web Primer).

Define each column before editing values

For a sample grants dataset, write down the intended structure:

Column	Intended type	Valid example	Value to investigate
grant_id	text identifier	00421	421 if leading zeros are significant
award_date	date	2026-09-15	September 2026, TBD
amount_usd	decimal number	12500.00	$12,500, unknown
recipient	text	Northside Lab	blank if required
status	controlled text	awarded	Awarded, complete

“Looks numeric” is not a sufficient test. IDs, postal codes and classification codes usually belong in text columns because arithmetic is meaningless and leading zeros may be significant. Conversely, currency symbols and thousands separators should not be embedded in a machine-readable numeric field; put the currency or unit in the column name or metadata.

If a dataset also mixes measurement units, normalize those with the workflow in How to Prepare a Public Data Table With Consistent Units.

Add a temporary type-check column

In Excel or Google Sheets, place this formula beside the first value in a column that should contain numbers, replacing C2 with the relevant cell:

=IF(C2="","blank",IF(ISNUMBER(C2),"number",IF(ISTEXT(C2),"text","other")))

Fill it down, then filter the helper column for anything other than number or an allowed blank. A value stored as the text 12500 will be labelled text, even if its appearance matches nearby numbers. Microsoft documents that ISNUMBER and ISTEXT test the value without converting numeric text into a number (Microsoft Support).

For a broader audit in Google Sheets, =TYPE(C2) returns 1 for a number, 2 for text, 4 for a Boolean and 16 for an error (Google Docs Editors Help). This reports the resulting value type; it does not tell you whether a value was entered directly or produced by a formula.

Do not approve a column merely because most cells have the expected type. Google Sheets’ QUERY function assigns a column type from the majority type and treats minority types as null for query purposes. A mostly numeric column can therefore appear to work while its text-valued exceptions disappear from a query result (Google Docs Editors Help).

Validate dates separately

Dates need two checks: whether the spreadsheet recognizes the value as a date and whether the exported representation is unambiguous.

In Google Sheets, use a temporary helper:

=IF(B2="","blank",IF(ISDATE(B2),"date","check"))

ISDATE tests whether a value is a date (Google Docs Editors Help). In Excel, dates are generally stored as serial numbers, so ISNUMBER alone cannot distinguish an intended date from an ordinary number.

Inspect every value marked check, then use one consistent representation. For calendar dates, YYYY-MM-DD avoids the day/month ambiguity of values such as 03/04/2026 and sorts correctly as text when every value is complete and zero-padded.

Do not mix dates with labels such as TBD, ongoing or Q3 2026. Prefer one of these structures:

• leave the date blank and add a separate date_status or date_note column; or
• preserve a necessary marker only after defining it in the data dictionary and confirming how the publication format will handle it.

For timestamps, time zones and date-only fields, follow Dates and Time Zones in Public Data Tables.

Resolve mixed values without erasing meaning

For every flagged cell, choose one action:

1. Correct a storage error. Convert text 12500 to the number 12500 only after confirming that the column is a measure, not an identifier.
2. Normalize presentation. Remove currency symbols from numeric values and declare the currency in a name such as amount_usd or in the metadata.
3. Represent missingness consistently. Do not convert unknown, suppressed or a blank to zero. Preserve a confirmed zero; otherwise use a documented null convention. CSVW metadata can declare strings that represent null values, allowing exceptions to be documented rather than treated as ordinary numeric observations (W3C). See Empty CSV Field vs Zero vs Missing Value for a practical decision rule.
4. Split a genuinely different concept. Replace under 5 in case_count with a blank numeric value and case_count_status = suppressed. Readers can sort the valid counts without losing the reason that a count is absent.
5. Correct controlled text. Trim stray spaces, standardize case and compare each value with the permitted list. Do not silently map a new category such as complete to awarded unless the definitions genuinely match.

Record these decisions in a data dictionary. At minimum, document the column name, definition, datatype, unit, permitted values, missing-value rule and constraints. The Spreadsheet Data Dictionary Template provides a reusable structure.

Test the exported publication copy

Spreadsheet formatting can hide the underlying value, and CSV does not retain cell formatting. Validate the file that will actually be published:

1. Export the exact sheet intended for publication.
2. Open the CSV or TSV in a plain-text editor and inspect dates, decimal marks, leading zeros, blanks and exception markers.
3. Re-import the exported file into a new spreadsheet.
4. Sort each numeric column from low to high. Text contamination may form a separate block or produce an unexpected order.
5. Sort each date column from oldest to newest and inspect both ends of the result.
6. Re-run the helper checks on the re-imported data.
7. Compare row counts, unique identifiers and important totals with the source workbook.

Publish only a sanitized copy: remove private notes, hidden working columns, personal data and any other information that should not be public. After publishing it with TablePage, inspect the public table itself. Repeat representative sorts and filters where available, search for known exception rows, and compare displayed totals or charts with the validated export. The public result—not the editable workbook—is the final validation target.