Data preparation with free spreadsheets

Learning objectives

• Set up a clean, auditable data table in a free spreadsheet before any analysis.
• Apply units, coding, missing-value and outlier rules consistently.
• Produce a documented workbook that another engineer can reopen and reproduce.

Core idea

Reliable statistics start with disciplined data preparation: one row per observation, one variable per column, explicit units, timestamps preserved and no hidden colour-coded assumptions.

Spreadsheet lab

• Row 1 = header names (Date, Line, Size, Measurement_cm, Defect_flag).
• Freeze row 1 and add =COUNTA(A:A)-1 as a running sample size.
• Flag blanks with =IF(D2="","MISSING","") in a helper column.
• Coerce numbers with =VALUE(TRIM(D2)); never overwrite the raw column.
• Timestamp import with =TEXT(NOW(),"yyyy-mm-dd hh:mm") in a metadata cell.

Garment-factory example

A shift engineer imports 240 chest-width readings from a handheld gauge. Bad rows (blank line ID, text entered as '52,3 cm') are flagged, not deleted, and the workbook records who cleaned what and when.

Method

1. Define variables, units and coding first.
2. Load raw data into a locked sheet; work on copies.
3. Add validation and missing-value flags in helper columns.
4. Record cleaning decisions in a change log tab.

Common mistakes

• Overwriting raw data in place.
• Mixing units (cm vs mm) in the same column.
• Deleting suspicious rows before investigating cause.