Clean Data Sheets: Prepare a Spreadsheet for Public Sharing

Before publishing a spreadsheet, make a separate table that readers can sort, filter and interpret without knowing how your workbook works. Clean data sheets need more than tidy formatting: each row must mean something consistent, each column must contain a defined field, and missing values must be distinguishable from real zeros.

The target is a reviewed publication copy—not an overwritten source file.

1. Define what one row represents

Write a one-sentence rule before cleaning. For example: Each row represents one park’s visitor count for one calendar month.

Here is a fictional target structure:

park_id,month,visitors,reporting_status
001,2026-01,120,reported
001,2026-02,0,reported
002,2026-01,,missing

In this example:

• park_id is a text identifier; its leading zeros matter. Treat this column as text when importing the sample into a spreadsheet.
• month uses the consistent YYYY-MM convention.
• visitors contains a whole-number count or a blank for missing data.
• reporting_status explains whether a count is available.
• The combination of park_id and month should be unique.

Use one header row and one continuous table. Remove title banners, merged cells, repeated headers, subtotal rows and blank separator rows from the publication copy. If January and February occupy separate tabs containing the same fields, combine them and add a month column. Data Carpentry’s spreadsheet guidance recommends keeping related observations together and recording meaning in fields rather than formatting.

2. Standardize values without changing their meaning

Keep an untouched source copy, a working copy and a publication copy. Record meaningful changes, such as category mappings or excluded records, in a separate cleaning log.

Work column by column:

• Labels: map verified variants such as North, north and North to one category. Do not merge labels merely because they look similar.
• Numbers: separate amounts from units. Prefer a numeric distance_km field to cells containing 12 km.
• Dates and periods: choose one unambiguous convention and verify it against the source. Do not guess whether 03/04/2026 means March 4 or April 3.
• Identifiers: preserve codes as text rather than treating them as quantities.

Excel can remove leading zeros and limits numeric precision to 15 significant digits. For public codes and long identifiers, Microsoft recommends treating the values as text. If conversion has already damaged an identifier, return to the source; changing its format afterward cannot reliably reconstruct it.

For routine whitespace cleanup in Google Sheets on a computer, select the intended range and choose Data → Data cleanup → Trim whitespace. Google notes that this does not remove non-breaking spaces, so inspect labels that still appear inconsistent. See its cleanup instructions.

3. Review duplicates and missing values

A repeated park ID is not necessarily a duplicate: the same park can appear in several months. Compare records using the fields that define one observation—in this example, park ID plus month.

If two records share that combination, inspect their counts and source history. They might be duplicate imports, revisions or conflicting reports. Resolve the conflict deliberately rather than retaining whichever row happens to come first.

Google Sheets offers Data → Data cleanup → Remove duplicates, with choices for comparison columns and headers. It considers identical values with different letter cases, formatting or formulas duplicates, so use it only on a reviewed working copy. Google documents those matching rules.

Keep measured zero as 0. Do not replace missing counts with zero or numeric stand-ins such as -999; these can enter calculations as real values. Data Carpentry distinguishes zero from missing data. For this sample, a blank count plus missing status makes the distinction explicit. Check how your publishing tool displays that blank.

4. Validate the file and its public page

Before export, record a few checks you can repeat afterward:

Check	Expected result for the fictional sample
Data rows	3
Unique park–month combinations	3
Reported counts	2
Missing counts	1
Sum of reported visitors	120
Park identifiers	001 and 002, unchanged

Also scan for formula errors, unexpected categories and implausible values. Investigate outliers; do not delete them just to make a chart look smoother.

Add a short description covering row meaning, units, missing-value rules, original source, coverage period and cleaning decisions. Confirm that you have permission to publish. Exclude sensitive information and assess whether combinations of fields could identify people. The UK Information Commissioner’s Office explains why removing names alone does not ensure anonymity.

Use the CSV export checklist to check the exported file. TablePage accepts CSV, TSV, XLSX and XLS files and generates a public dataset page with a filterable table. Upload only the reviewed publication copy.

On the resulting page, repeat your checks: confirm headers, row count, leading zeros, missing values and totals. Try a numeric sort and a category filter. Share the link only when the public table matches the file you approved.