The master chart is the spreadsheet that holds your study. Everything downstream, every table, every test, every number in your results, is read out of it. Design it in the week you write your protocol, before a single patient is enrolled, because a chart designed after collection has to be rebuilt from case sheets, and that rebuild is measured in weeks rather than evenings.
One row per participant, one column per variable
That is the entire structure, and most broken charts break it. One row is one participant. One column is one thing you measured about them. The first row of the sheet holds variable names and nothing else: no title, no merged heading, no department name, no blank spacer row.
Things that look convenient and are not: a column called 'Investigations' holding several results in one cell; a column called 'Comorbidities' holding a comma-separated list; two rows for one patient because they were measured twice. Free text in a cell cannot be analysed. It has to be read by a human and retyped, which is exactly the rebuild you were trying to avoid. Split it. Haemoglobin is its own column. Diabetes is its own column holding 0 or 1.
Repeated measurements are where the decision matters most. Measuring blood pressure at baseline, one month and three months can be three columns across one row, or three rows per patient with a column saying which visit it is. SPSS wants the first arrangement for a paired test and the second for some models; R handles both. Pick one when you write the protocol and tell your statistician which you chose.
Coding conventions, written down first
Write a codebook on the second sheet of the same file before you enter the first patient. One line per variable: the exact column name, what it means, its unit, and for a categorical variable every permitted code and its meaning. Without it, a 3 sitting in a column called grade is unreadable six months later. With it, a statistician who has never met you can analyse your data unaided.
- Lower case column names, no spaces, no punctuation beyond the underscore: age_years, hb_gdl, los_days
- Put the unit in the column name and never in the cell, because a cell reading 120 mmHg turns the whole column into text
- Binary variables as 0 and 1, with 1 meaning the thing is present; never Yes in one row and Y in the next
- Code each category as a documented number, or as one consistent spelling, but never both in the same column
- Keep ordered categories in their real order, 1 to 4 for grade I to IV, so a rank-based test behaves correctly
- Never colour a cell to mean something. Colour is not data, it does not survive an export, and no software reads it
No identifier, ever
No name, no hospital registration number, no address, no telephone number, no Aadhaar number, no date of birth, no photograph goes into the master chart. Each participant gets a study number, 001 and onwards, and that is their identity in the file for the rest of the study. Age goes in the chart; date of birth does not.
In a prospective study you will need a route back to the patient, for a follow-up visit or a missing report. Keep that link in one separate file that never travels with the analysis file: study number against hospital number, nothing else, held by you, stored the way your ethics committee's approved plan says. The master chart can then be emailed to a statistician, opened on a shared computer and printed into your thesis appendix without exposing anybody.
This is not only good practice. Confidentiality of participant data is a requirement of ICMR[5]'s 2017 national ethical guidelines, which your ethics committee works from, and you have already told that committee in writing how you would achieve it. Names left in the chart are the quiet breach of your own undertaking.
Dates
Store dates as YYYY-MM-DD and in no other format. It sorts correctly even as text, it is unambiguous in a way that 03/04/2026 is not, and it survives the move between software. Decide once whether a column holds true date values or text, and do not mix the two inside one column.
Spreadsheets interfere with anything that looks like a date. Type a value that resembles one and the spreadsheet will convert it silently, so format columns of codes, ratios and gene-like labels as text before you type into them, and inspect a column after every import. In most cases what you actually want is not a date at all but a number: keep date of admission and date of discharge if you need them, and also compute and store los_days, because that is the variable you will analyse.
Missing data, honestly
Data will be missing. A report was never collected, a patient did not return, a question was skipped. The dishonest options are filling the gap with a guess, with the group mean, or with a zero.
Leave the cell genuinely empty, or use one consistent marker such as NA, declared in your codebook and in your software's missing-value settings. Never use 0, 9, 99 or 999. A sentinel number is invisible to the software and gets averaged into your results as a real value, which is how a paediatric series acquires a mean age in the nineties.
Distinguish missing from absent, because they are different facts. A blank under 'duration of ventilation' for a patient who was never ventilated is not missing data; it is a zero, or better, a separate variable recording whether they were ventilated at all. Confusing the two changes your denominator and therefore every percentage built on it.
Then report it. State how many values were missing for each variable that matters and what you did about them. A results section that declares its missingness is believed. One that implies a complete dataset from 120 consecutive emergency admissions is not.
Raw variables and derived variables
Store what you measured. Compute what follows from it. Height and weight belong in the chart; BMI is calculated by your software from those two columns and is not typed in by hand. The same goes for length of stay from two dates, for mean arterial pressure, for a score that sums several items, and for change from baseline.
A typed derived value is a transcription error waiting to surface in your viva, and it cannot be rechecked because the working is not in the file. Name derived columns clearly and keep them to the right of the raw data, then regenerate them rather than editing them. When a raw value is corrected, the derived value corrects itself.
One file, with versions
One file is the master. It lives in a folder that syncs to cloud storage and it carries a date in its filename: master_chart_2026-09-19.xlsx. When you make a substantial change, save a new dated copy instead of overwriting, because at some point you will need to know what a value looked like before you cleaned it. Never put the word final in a filename.
Do not edit the master chart while a statistician is analysing a copy of it. You will end up with two divergent files and no way to tell which one produced your tables. Send a dated copy, keep collecting in the master, and send an updated dated copy when collection closes.
Getting it ready for SPSS or R
- One sheet of data, starting at cell A1, variable names in row 1, the first participant in row 2
- No merged cells, no blank rows or columns, no subtotal or total row, no notes underneath the data
- No asterisks, footnote marks or comments inside data cells
- Numeric columns containing only numbers: no 'approx', no '<5', no trailing spaces
- One consistent spelling for every category; sort the column and read its unique values before you trust it
- Export a copy as CSV and reopen it, because anything that looks wrong there is what your software will see
- Check the obvious: does every row have a study number, are any study numbers duplicated, is any age 2 or 200, is any discharge date earlier than its admission date
Half an hour on that checklist is the difference between an analysis that starts today and one that starts after four rounds of questions. If you would rather hand over a designed chart than build one, MDThesis sets it up with its codebook from your approved proforma at protocol stage, which is the point at which the work is an hour rather than a rebuild.