Normalize Units Before Readers Sort a Public Data Table

A column containing 1.2 km, 850 m and 3000 ft may look readable, but its rows are not directly comparable. Before publishing, retain the source measurements and add a numeric column converted to one declared unit. Readers can then sort, filter and chart the values without silently comparing different scales.

Use a raw-plus-normalized structure

Start with a harmless fictional dataset such as walking-route lengths:

route_id	route_name	length_raw	unit_raw	length_km
R01	North Loop	1.2	km	1.2000
R02	River Walk	850	m	0.8500
R03	Hill Trail	4000	ft	1.2192
R04	Garden Path	3000	ft	0.9144

This layout separates provenance from comparison:

• length_raw and unit_raw preserve what the source supplied.
• length_km is numeric and contains only kilometers.
• The normalized unit appears in the header, so it remains visible when the table is exported or reused.

Do not replace the raw values unless they are genuinely disposable. Keeping them makes it possible to audit a conversion or apply a corrected rule later.

CSV itself cannot declare a column’s datatype, which makes validation harder; the W3C’s CSV on the Web approach uses metadata and schemas to describe and validate table structure (W3C). For a lightweight publication workflow, put the essential contract in the file and its documentation: numeric column, declared unit, conversion rule and rounding policy. A separate spreadsheet data dictionary is useful when several measured fields need that treatment.

Convert with an explicit rule

Choose a target unit appropriate to the audience and expected magnitude. For this sample, use kilometers. A spreadsheet formula for row 2 could be:

=IF(D2="km",C2,IF(D2="m",C2/1000,IF(D2="ft",C2*0.3048/1000,NA())))

The formula deliberately returns an error for an unrecognized unit rather than guessing. NIST records the international foot as exactly 0.3048 m, while the prefix kilo represents one thousand of the named unit (NIST conversion factors, NIST metric writing guide).

For a larger or recurring dataset, use a lookup table instead of nested conditions:

unit_raw	multiplier_to_km
km	1
m	0.001
ft	0.0003048

Keep that mapping with the working files and record its source. Do not treat labels as interchangeable merely because they look similar: establish the measured quantity first, then convert units for that same quantity.

Keep display formatting out of the data value

Store 1.2192, not 1.2192 km, in length_km. The first is a number; the second may be interpreted as text. Put the unit in the header and dataset notes instead.

Use consistent unit symbols. NIST advises that unit symbols are not pluralized and that a space separates a number from its symbol in prose—for example, 250 mm rather than 250 mms (NIST). Those presentation rules are useful in captions and notes even when the machine-readable column contains numbers only.

Do not create false precision during conversion. NIST’s guidance says converted values should not contain more significant digits than the original data justify (NIST). Keep enough working precision to avoid compounding rounding errors, then choose and disclose an appropriate display precision.

Validate before publication

Run these checks on the finished file:

1. Inventory units. Filter unit_raw and investigate every unexpected spelling, blank or abbreviation.
2. Check numeric types. Confirm every non-missing length_km value behaves as a number. Keep unknown measurements missing rather than converting them to zero; see empty fields, zero and missing values.
3. Recalculate samples. Check at least one row for each source unit manually. In the sample, 3000 × 0.3048 ÷ 1000 = 0.9144 km.
4. Sort both ways. Ascending order should be River Walk, Garden Path, North Loop, Hill Trail. Descending order should reverse it without separating fields from their rows.
5. Check boundaries. Inspect the smallest, largest, zero, negative and missing values. A negative length would warrant investigation rather than automatic correction.
6. Review labels. Make sure the header, caption, chart axis and download documentation all name the same target unit.

Publish the method with the table

Add a short note such as:

Route lengths were normalized to kilometers. Original values and units are retained in length_raw and unit_raw. Feet were converted using 1 ft = 0.3048 m. Normalized values are displayed to four decimal places. This is a fictional sample dataset.

Also identify the real dataset’s publisher, source URL, retrieval or release date, license, missing-value convention and any rows excluded. A useful table caption and summary can explain the unit and row structure without forcing readers to infer them.

As of September 21, 2026, TablePage accepts CSV, TSV, XLSX and XLS uploads and creates a public dataset page with a filterable table (TablePage). Because that page is public, remove sensitive fields before upload. After publishing, repeat the ascending and descending sort on the public page and verify several displayed values against the prepared file.

If reliable conversion is impossible, do not manufacture comparability. Publish separate numeric value and categorical unit columns, explain that units are mixed, and avoid presenting a cross-unit ranking until the missing context is resolved.