How to prepare your data for statistics

The way a spreadsheet is laid out decides how much cleaning the file needs before any analysis can start. A table that is clean from the first row of data entry can go straight into a test or a plot. A table that mixes units, stacks two header rows and marks groups with colour has to be repaired by hand, and every repair is a chance to change a value without a record.

The rules below follow Broman and Woo (2018) on spreadsheet organisation and Wickham’s (2014) definition of tidy data. They apply whatever the subject of the study, and they take a few minutes to follow when a file is first set up. At the end there is a small template that already follows all of them.

One row per observation, one column per variable

Tidy data follows three rules in Wickham’s definition: every variable forms a column, every observation forms a row, and every type of observational unit forms its own table. In a study with one measurement per participant, that means one row per participant. In a study where participants are measured on several days, it means one row per participant and day.

The common mistake is to spread repeated measurements across columns, one column per visit. That wide layout is handy to read on screen but awkward to analyse, because the visit is stored in the column names instead of in the data. The long layout stores it as a variable:

Wide, with one column per visit:

Long, with one row per visit:

Wide is fine when each participant is measured once, or when a method explicitly asks for one column per time point, as a paired test on two measurements does. Choose the layout from the method you plan to use; long vs wide data shows how to convert between them in base R. The data cleaning checklist explains how to decide what one row means in the first place.

One value per cell

A cell holds one value and nothing else. Two numbers in one cell, such as a blood pressure written as “120/80”, cannot be averaged or compared. Split them into two columns, here systolic_mmhg and diastolic_mmhg, each holding a plain number.

Units inside a cell cause the same trouble. A cell reading “5 mg” is text, so the column stops being numeric and most software will treat every entry as a label. Keep the number in the cell and move the unit to the column name (see the units section below). Notes belong in their own column, such as comment, and never inside a number cell: a remark like “repeat sample” next to a value turns the whole column into text.

A single header row with short, consistent names

The first row holds the variable names, and the data start on the row directly below. Do not leave blank rows above the table, add a title line, merge cells across several columns or stack two header rows. A tool that reads the first row as names will pick up the wrong one, and a merged cell leaves the other columns without a name.

Good names are short and use only letters, digits and underscores, with no spaces: systolic_mmhg rather than “Systolic BP (mmHg), visit 2”. Pick one style and keep to it, for example all lower case. Give every column a different name, and do not reuse a name for two measurements, since software will rename the duplicates in ways you did not choose.

Codes in the data, meanings in a codebook

A categorical variable needs one consistent value per category. A short code, such as 1 and 2 for two study groups or 0 and 1 for no and yes, keeps entry fast; the codebook then says what each code means and that the column is categorical, so no tool treats the codes as amounts. The codebook is a small separate table that describes each column, so nobody has to guess what a 2 stands for.

A useful codebook has one row per column of the data and a few fields:

• the column name, matching the header exactly
• the type, such as continuous, categorical, binary, ordinal, date or identifier
• the unit, for numeric columns
• a label in plain words
• missing-value codes, if the file uses any
• the allowed range, for numeric columns
• the levels and their labels, for categorical columns, such as 1 = Control and 2 = Intervention

Spell each category the same way every time. “Male”, “male” and “M” are three different values to software, and one inconsistent entry creates a category of its own. Codes avoid the problem, and so does a drop-down list in the entry sheet.

Missing values: empty, or one documented code

A missing value should look the same everywhere in the file. Broman and Woo suggest filling every empty cell with one marker such as NA; leaving the cell empty is the other common convention. Either is fine, as long as the whole file uses one and the codebook says which.

Never use 0 for a missing value, because 0 is a real measurement. Avoid cells that hold only a space, and avoid dashes, question marks and words like “unknown” in the same column as numbers, and do not mix “NA”, “?” and “-” in one file. Each extra marker is one more thing the person who analyses the file has to find and convert.

A reading reported as “below the detection limit” is not missing: the instrument did measure something, and the information that it was low is worth keeping. The guide to values below the detection limit shows how to record and analyse them. For missing values in general, see how to handle missing data, and multiple imputation vs complete-case analysis for the two main options.

Dates as YYYY-MM-DD

Write every date in the international order year, month, day, with hyphens: 2026-03-09. The format is ISO 8601, it sorts correctly as plain text, and statistics software reads it without the day-month ambiguity of other formats.

Other formats are ambiguous. A cell reading 03/04/2026 means the third of April in one country and the fourth of March in another, and the person analysing the file may not know which. Spreadsheet programs add another risk by converting what you type into dates automatically, sometimes changing the value without a warning. Set the column format to text before typing, or check that the stored value is the one you meant.

Units in the column name or their own column

Each numeric column uses one unit throughout. Put the unit in the column name, as in weight_kg or age_years, so that nobody has to look it up. A column that mixes kilograms and pounds, or minutes and hours, cannot be fixed afterwards unless every row records which unit it used.

When a unit really does vary between rows, give it its own column and record it for every row. Better still, convert at the point of entry and keep a single unit per variable, because that removes a whole class of errors.

Study IDs, not names

Identify each participant with a study ID such as P001, never with a name, initials, address, phone number, date of birth or any other contact detail. Keep the list that links IDs to people in a separate, protected file, stored apart from the data and shared with as few people as possible.

A file with direct identifiers is harder to share, harder to store safely and harder to publish later. Starting with codes avoids that problem. The article on identifier scanning and de-identification describes what counts as an identifier and how it is handled. Exact dates tied to a person, such as visit dates, can also identify someone, so the scan flags them too; keep them only when the analysis needs them.

No colour, bold or comments as data

Software reads values, not formatting. A row highlighted yellow to mean “excluded”, a bold entry for an outlier or a cell comment explaining a result will vanish when the file is saved as CSV or opened in a statistics tool. Anything that carries meaning needs its own column.

If some rows are excluded, add a column such as excluded with the values 0 and 1, and record the reason in another column. The decision is then visible in the data, can be counted and can be reversed.

Save as CSV or xlsx, and keep the raw file

Save the final table either as CSV in UTF-8 or as an Excel workbook. CSV is the plainest option and can be read by any tool. In an Excel file, put the data on the first sheet as one rectangular table starting at cell A1, and keep one table per sheet. A second sheet for the codebook is a good place to document the file.

Keep an untouched copy of the original file and do the cleaning on a copy. When every change after that point is recorded, the analysis can be repeated from the raw file, as described in the guide to reproducible analysis. Avoid formulas in the raw data sheet, since a recalculated value can silently differ from the one first entered.

Download the template

The template follows every rule above in a small, synthetic example with twelve rows and seven columns. The workbook has two sheets. The first, data, holds the table; the second, codebook, describes each column with its type, unit, label, allowed range and category labels.

• Data template workbook (.xlsx)
• Data table only (.csv)
• Codebook only (.csv)

Celeus reads CSV and Excel files. The codebook CSV can be imported as it is with Import codebook in the dataset’s codebook panel; the codebook sheet inside the workbook is not read on upload. Once the file is laid out this way, the choice of statistical test is the next step.

Checklist before you analyse

Run through these six checks before you start an analysis:

1. Every row is one observation and every column is one variable, with one header row.
2. Every cell holds one value: no merged cells, no units or notes inside numbers, no colour used as data.
3. Dates are written as YYYY-MM-DD.
4. Missing values are empty or use one documented code, never 0.
5. Categories are stored as consistent codes, and a codebook says what each code and unit means.
6. Participants are identified by study IDs, with the linking list stored separately.