Organizing Research Data in Excel

None of the rules below came from a textbook first. Each one is something I learned by getting it wrong — in my own extraction sheets, sometimes more than once — and then paying for it at analysis time. I’m writing them down mostly so I stop repeating them, and in the hope that they save you some of the same detours.

The golden rule: your spreadsheet will be read by software, not just by humans

It took me embarrassingly long to internalize this: a research spreadsheet is not a report or a poster. It is the input to statistical software (R, Python, Stata, RevMan). Whatever looks fine to your eyes but confuses a computer — merged cells, colors, comments inside numbers — will cost hours of cleaning later, or worse, silently produce wrong results. I used to format sheets to look nice for humans; every one of those “nice” touches later turned into a cleaning step.

This is not a hypothetical risk. A well-known study by Ziemann and colleagues found that roughly one in five genetics papers with supplementary Excel files contained gene names that Excel had silently auto-converted to dates (the gene SEPT2 becomes “2-Sep”). By 2021, the error rate had risen to about 30%. Nobody noticed until after publication.

The rules below prevent these problems at the point of data entry, where fixing them is cheapest.

Rule 1: One rectangular table per sheet

Each sheet should contain exactly one table: a single header row on row 1, data starting on row 2, no merged cells, no titles above the table, no notes floating beside it, and no second table somewhere below.

Bad — two tables at offset positions, a title row, and merged cells:

      A            B          C
1   Trial results for Study X (2024)   ← title inside the sheet
2
3   Group      | Baseline  | Week 8
4   Drug       |   21.4    |  14.2
5   Placebo    |   21.9    |  18.8
6
7        Dropouts                       ← a second table, shifted
8        Drug    | 4
9        Placebo | 7

Good — one rectangle, one header row, nothing else:

study_id	group	time_week	isi_mean
StudyX	drug	0	21.4
StudyX	drug	8	14.2
StudyX	placebo	0	21.9
StudyX	placebo	8	18.8

Why: statistical software imports a sheet as one rectangle starting at the top-left. Anything else — titles, offset tables, merged cells — either breaks the import or corrupts it silently. If you have two logical tables (e.g., outcomes and dropouts), use two sheets, each starting at cell A1.

Merged cells deserve a special warning: they look tidy but make it impossible for software to know which row a value belongs to. Never merge. Repeat the value on every row instead — that is what software expects.

One honest exception: if a spreadsheet is truly, permanently human-only — it will never be fed to analysis software, and you are certain of that — then human-friendly touches like a totals row at the bottom are fine. A budget overview or a screening progress tracker can afford them. The trap is that “human-only” sheets have a way of becoming analysis inputs later, so when in doubt, keep the rectangle clean.

Rule 2: One value per cell

A cell holds exactly one value: one number, or one category label. Comments, units, and second measurements go in their own columns.

Bad:

participant_id	weight
001	63 kg
002	71.2 (measured twice, used 2nd)
003	68/70

None of these three cells can be used in a calculation. The column has become text.

Good:

participant_id	weight_kg	weight_note
001	63.0	NA
002	71.2	measured twice; second value used
003	68.0	first of two readings; second was 70

Why: the moment a cell contains anything besides the value itself, the entire column stops being numeric. A dedicated *_note column preserves every comment and keeps the data usable. Units belong in the column name (weight_kg) or the data dictionary — never in the cell.

Rule 3: Be consistent — and let dropdown menus enforce it

Pick one code for each category and use it everywhere. Better yet, don’t rely on discipline: set up Data Validation (Data → Data Validation → List) so the spreadsheet only accepts the agreed codes.

Bad — four spellings of the same design:

study_id	design
Smith2019	RCT
Chen2020	rct
Kim2021	randomized
Sato2022	Randomised trial

To a computer, these are four different designs. Every analysis now needs a cleaning step, and a subtle typo (randomzied) may never be caught.

Good — one controlled vocabulary, entered via dropdown:

study_id	design
Smith2019	rct
Chen2020	rct
Kim2021	rct
Sato2022	rct

The same rule applies to characters themselves. Use standard half-width ASCII characters for all numbers, IDs, and codes. Full-width digits (１２３), full-width spaces, and full-width minus signs look almost identical to their half-width counterparts on screen but are different characters to a computer: １２３ is not a number, and Smith２０１９ will never match Smith2019. If several people enter data on differently configured systems, this problem is guaranteed to appear unless the rule is explicit.

For numeric columns, use validation too: restricting age to 0–120 or prop_female to 0–1 catches most typos at the moment they happen.

Rule 4: Write dates as YYYY-MM-DD

Bad: 3/4/2022 — March 4 or April 3? Both readings are common, and Excel will reinterpret the cell depending on the computer’s locale settings.

Good: 2022-03-04 (ISO 8601). Unambiguous, sorts correctly as text, and understood by every software package. If you want to be fully safe from Excel’s date auto-conversion, use three columns: year, month, day.

Rule 5: No empty cells — use an explicit missing-value code

Bad:

study_id	dropout_n
Smith2019	4
Chen2020
Kim2021	0

Is Chen2020 blank because dropouts were not reported, or because someone forgot to enter the number? Six months later, nobody knows.

Good:

study_id	dropout_n
Smith2019	4
Chen2020	NA
Kim2021	0

Fill every cell. Use one agreed code for missing (NA works well with R). Then an empty cell always means “data entry is not finished yet” — a state you can detect and fix. Never use 0 or -999 to mean missing without documenting it; 0 is a real value.

Rule 6: The spreadsheet holds raw data; scripts do the calculations

This is the rule that took me the longest to learn, and the one that separates a reusable dataset from a dead end.

The spreadsheet’s job is to record the raw data — in a systematic review, the numbers exactly as extracted from each paper. Means, SDs, sample sizes, event counts: enter them as reported. All downstream computation — pooled effects, standardized mean differences, subtotals, unit conversions — belongs in analysis scripts (R, Python), where every step is written down, reviewable, and re-runnable.

Bad — a total typed in by hand:

group	n_site1	n_site2	n_total
drug	42	38	80
placebo	40	41	80 ← typed by hand; actually 81

Hand-typed (“hard-coded”) summary numbers are silently wrong the moment any input changes — and often wrong from the start, as here. Nobody will ever re-check them.

Good option A (preferred) — the sheet stores only inputs; the script computes totals:

group	n_site1	n_site2
drug	42	38
placebo	40	41

data <- read_csv("extraction.csv") |>
  mutate(n_total = n_site1 + n_site2)

Good option B (acceptable) — if a calculated column must live in Excel, it must be a live formula, never a typed number:

group	n_site1	n_site2	n_total
drug	42	38	`=B2+C2`
placebo	40	41	`=B3+C3`

A formula updates itself when the inputs change and can be audited by clicking the cell. A typed number can do neither.

The common exception: derived statistics — keep the provenance

In systematic reviews we constantly need a value the paper did not report directly — most often an SD that must be converted from a standard error, confidence interval, or IQR. Converting before entry is fine. What is not fine is entering only the converted number, because then no one can ever verify it.

Bad — only the derived value, origin lost:

study_id	mean	sd
Chen2020	14.2	8.94 ← where did this come from?

Good — record what the paper reported, and derive transparently:

study_id	mean	sd_reported	se_reported	n	sd	sd_source
Smith2019	12.1	7.9	NA	60	7.9	reported
Chen2020	14.2	NA	1.0	80	`=E3*SQRT(D3)`	converted from SE

The paper’s own numbers (sd_reported, se_reported) are preserved verbatim as raw data; the working value (sd) is computed by a formula (or in the script); and sd_source says which path was taken. Anyone — including you, a year from now — can trace every number back to the publication. If a value in your sheet cannot be traced back to its source, it is not data; it is an assertion.

Two further habits protect the raw data:

Never overwrite raw entries. If a value needs correcting, keep an audit trail (a correction_note column, or file versioning) rather than silently replacing it.
Keep the raw extraction file read-only once entry is complete, and do all cleaning downstream.

Rule 7: Choose good names

Bad: column headers like Mean score at endpoint (ITT population!!), files like final_data_FINAL_v2 (use this one).xlsx. (I have personally created files with names very much like that second one.)

Good: short, meaningful, machine-friendly names — lowercase, underscores instead of spaces, no special characters; files like sleepi_extraction_2026-08-08_v02.xlsx (content + ISO date + explicit version).

Why: spaces and special characters in headers become garbled or renamed on import, and ambiguous file names cause the single most expensive mistake in collaborative research — analyzing the wrong version.

After enough inconsistent sheets, I settled on a small set of naming conventions. They are arbitrary in the details, but having one convention — any convention — beats deciding cell by cell:

snake_case for everything: lowercase, words separated by underscores (sleep_onset_min, not SleepOnsetMin or sleep onset (min)).
Sheets and tables: plural nouns (studies, outcomes, arms) — a sheet holds many rows. Variables: singular (study_id, arm, dose_mg) — a column describes one attribute of each row.
Put the unit at the end of the name whenever the variable has one: weight_kg, duration_week, dose_mg. This is the cheapest form of documentation and prevents the classic mg-vs-µg class of error.
Continuous outcomes: end with _mean, _sd, _n: isi_mean, isi_sd, isi_n. The shared stem (isi_) keeps related columns together and makes them easy to select programmatically.
Binary (dichotomous) outcomes: start with n_, recording the event count: n_responder, n_dropout, paired with the group total n. A count is raw data; a percentage is a calculation (see Rule 6).

These conventions align with the tidyverse style guide used in the R community — worth a skim if you will ever touch the analysis side, since data entered under these rules flows into R with no renaming at all.

Rule 8: Ship a data dictionary with the dataset

A dataset without documentation is unusable by anyone but its author, and eventually by the author too. Add one sheet (or a separate file) with one row per variable:

variable	label	unit	allowed_values	missing_code	notes
study_id	First author + year	—	text	—	must match reference list
design	Study design	—	rct, quasi_rct	NA	dropdown enforced
isi_mean	Insomnia Severity Index, mean	points	0–28	NA	endpoint, ITT
sd_source	Origin of sd value	—	reported, converted from SE, converted from CI	NA	see conversion formulas in README

Finally, export a copy of the master table as CSV (UTF-8). Plain text opens in every tool, survives every software transition, and diffs cleanly under version control.

The checklist

This is the list I now run through before sharing any spreadsheet — each item exists because I once skipped it:

One table per sheet; single header row; data start at A1
No merged cells anywhere
One value per cell — comments in a notes column, units in the header
Categorical variables entered via dropdown (Data Validation); one spelling per category
Half-width ASCII characters only for numbers, IDs, and codes
Dates as YYYY-MM-DD
No empty cells — missing values coded as NA
Numbers entered exactly as reported in the source; derived values traceable (formula + source column)
No hand-typed totals or summary statistics — scripts (preferred) or live formulas only
Machine-friendly names: snake_case, units as suffix (_kg), _mean/_sd/_n for continuous outcomes, n_ for event counts; versioned file names with ISO dates
Data dictionary included
CSV (UTF-8) copy exported