Standard data template

Checking the file

Paste columns directly from the spreadsheet (including the header row) – select them in Excel, copy, and paste here (pasted data have no legend – the types will be determined automatically):

Why one template

Every calculator on the site reads the same file: a "data" sheet in wide format – each row is a participant (or any other measured entity: a class, an organization, a town), each column is a variable, and with no distinction between predictor and outcome: both are columns (Field, 2024, Section 4.6.1) – and beside it a "legend" sheet, which is the file's codebook: for each variable a name, a description, the coding of the responses and a level of measurement (Pallant, 2020, Chapter 2). Pallant's idea holds here too: the codebook is written once, kept alongside the data, and there is no more coming back to the file after half a year without knowing what "3" means in the "education" column. How to decide what the "3" will be – coding closed and open questions, codes for missing values, reversing items, grouping and dummy coding – is on the page Coding responses and creating variables. The only difference is that on the site the codebook is a sheet inside the file – so the calculators read it themselves: they know that gender is categorical and age is continuous, display "man" and "woman" instead of 1 and 2, and warn you when you select a categorical variable for a correlation.

The same file then goes into SPSS with no additional work: the syntax file that each calculator downloads loads the data and sets up the Variable View from the legend – labels, value labels and levels of measurement. And in the opposite direction, the page From SPSS to Excel produces this template from a .sav file, including the legend, from the file's dictionary.

The "data" sheet – the rules

1. Row = participant, column = variable. Everything measured for the same participant goes into the same row – including repeated measures (before and after = two columns). What distinguishes between groups of participants (experimental/control, gender) is a single column with a code for each group – a "coding variable" or "grouping variable" (Field, 2024, sections 4.6.1 and 4.6.5; Tip 4.1). The long format (a row per observation, not per participant) is not supported.

2. The first row is the header row: a short, unique name for each variable. On the site any name works – in Hebrew too, and with spaces; in SPSS the name must start with a letter, with no spaces or special characters, and must not be a reserved word (Pallant, 2020, Chapter 2), so the syntax file converts such names and reports it. A duplicate name for two columns is an error: the legend and the syntax will not know which one is meant. Cunningham and Muir (2023) recommend that the variable name include the question number in the questionnaire (Q7_income) – this way "years of schooling" cannot be mislabeled as "years of work", and when recoding you open the questionnaire itself alongside the legend. The checker reports empty names, duplicates and names that will change in SPSS.

3. First column – ID. A unique serial number for each participant, also written on the questionnaire; this way, when an incorrect value is found, you can go back to the source and fix it (Pallant, 2020, Chapter 2). The calculators identify the column by its name (ID, id, participant number, participant no., no, num, serial, index) or by "id" in the legend's type column – and exclude it from every analysis. The ID has a second role, which the section Data cleaning explains: if participant 17 suddenly appears in row 40, someone sorted one column without the rest.

4. Responses as numbers. Every response that is not a number by nature (gender, group, marital status) gets a numeric code – 1, 2, 3 in the order of the options in the questionnaire, or any other number: the codes are arbitrary, because the number itself is not analyzed (Field, 2024, Section 4.6.5; Pallant, 2020, Chapter 2). What the code means is written in the legend. A column whose values are mostly text ("man", "woman") will work in the calculators as categorical – but a single text value inside a numeric column ("don't know" in the age column) will be treated as missing, so it is better to code everything. Numbers: a decimal point (3.5), no thousands separators and no units ("25" and not "25 years").

5. Missing value = empty cell. Not 99, not 999, not a dash, not a space (see Missing values).

6. No formulas, no merged cells, no summary rows and no comments in the data area (Pallant, 2020, Chapter 4). Compute a scale score on the page Cronbach's alpha and scale score – it returns the file with the score column – and not with an Excel formula that will disappear in the conversion.

7. A reverse-worded item is entered as answered; the reversal is done on the alpha page or marked in the legend with the word "reverse".

The "legend" sheet – the five columns

Variable type – how it is determined

The level of measurement determines what may be computed: for categorical – frequencies and chi-square; for ordinal – median and Spearman; for continuous – mean, Pearson, t and regression (Pallant, 2020, chapter 2; and more on the page Variables, levels of measurement and research designs). In SPSS this is the Measure column in the Variable View (Nominal / Ordinal / Scale; Pallant, 2020, Chapter 4); on the site – the "type" column in the legend. And when there is no legend, the calculators guess from the values:

Missing values

The simple way to record a missing value is to leave the cell empty – and then SPSS recognizes it as missing with no setting at all (Field, 2024, Section 4.6.8; Pallant, 2020, Chapter 4). This is also the rule on the site: empty cell = missing. A numeric code for missing (99, 999, 666 – an "impossible" value larger than the maximum of the measure) is accepted in SPSS, but only if it is defined there as user-missing; without that definition the software counts it as a value like any other – "with disastrous consequences", in Pallant's words (2020, chapter 4). The site has no such setting, so 99 in the age column will raise the mean. The checker warns about such values (99, 999, 9999, -9, -99, -999) when they are far from the other values, and a file that came from SPSS goes through From SPSS to Excel – there the box "defined missing values will become an empty cell" takes care of it. A response such as "don't know" or "other, please specify" that is not missing but a response – stays a code, but is excluded before a mean or a correlation (see the missing values page – what each handling method does).

Cunningham and Muir (2023) add two distinctions worth having in the legend. Legitimate missing – the question does not apply to the participant (an intentional skip after a filter question) – as opposed to illegitimate missing – the participant refused, did not know, or the question was omitted by mistake (a whole page skipped, a row deleted during cleaning; Osborne, 2013, as cited in Cunningham & Muir). And "don't know" is recorded separately from refusal: sometimes "don't know" ("How many weeks pregnant are you?") is itself a finding, and the decision whether to merge it with the other missing values belongs to the study – but it can be made only if the codes were separated in advance. And another warning from there: a missing code such as 999999 in an income column will be read by someone unfamiliar with the file as an income of 999,999 – exactly the reason for the rule "empty cell = missing", and for documenting every other code in the notes column of the legend.

Files and encoding

Formats: xlsx (recommended), xlsm, old xls, csv and txt separated by commas, semicolons or tabs. A csv has no sheets and therefore no legend. A csv file is read as UTF-8, and if it is not – as windows-1255 (the old Hebrew encoding). Old xls files exported from SPSS often arrive with "broken" Hebrew (Latin gibberish) – the calculators detect this and fix the headers, the values and the legend automatically, with a message. Sheets: the data are read from the "data" sheet (or data); if there is none – from the first sheet that is not the legend or the instructions sheet. Pasting: you can paste columns directly from Excel (with the header row) into any calculator – without a legend.

What each calculator needs

What the checker checks

Pallant (2020, chapter 5) describes the screening that comes before any analysis: for each variable, including every item in a scale, check that there are no values outside the possible range (gender coded 1-2 cannot be 3; education 1-6 and not 22) – usually through a frequency table with minimum and maximum – locate the participant, fix according to the source, and record in a log what was changed. "35 instead of 3" can distort an entire correlation. The checker on this page does the part of the screening that can be done automatically, on the whole file, and leaves the fixing to you:

Header row – empty names, duplicate names, names that will change in SPSS. ID – whether there is one, and whether it is unique. The legend – whether it was detected; variables in the legend that are not in the data (name mismatch) and vice versa; unrecognized words in the type column. Every column – the type that will be determined and its source; non-numeric values in a numeric column (with the row numbers); values outside the codes or the range in the legend; values that look like a missing code; a constant column; a text column; a column that is mostly missing. Table 2 – which calculators will find in the file the variables they need. And at the end – the file itself with a legend sheet ready to fill in: name and type are already written, and the codes found are waiting for a label. What the checker cannot do – compare with the source, cross-check variables, decide what to fix – is in the next section.

Data cleaning – what the checker does not do for you

Cunningham and Muir (2023), in the chapter "Data cleaning" in the Cambridge handbook of research methods, open with a remark on the word: data are not dirty hands that a good "scrubbing" cleans. A file consists of values – some correct, some incorrect; some of the incorrect ones can be detected and some cannot, and a correct value can look incorrect. That is why "hasty and uninformed" cleaning introduces new errors: a correct value replaced by an incorrect one, an incorrect one by another incorrect one, or – worst of all – rows deleted, duplicated or mixed up. For them, cleaning is not a stage before the analysis but a stance throughout the study: anticipate errors in the planning, prevent them in the collection, detect and resolve them in data entry and management, and report them in the writing. The checker on this page covers part of the detection; what is left for you – four rules and three types of error.

The source is locked, the work is done on a copy. The first rule: never change the original data file – not even a value you are sure is wrong. When data entry is complete the file is "locked", and every change is made in a working copy; and even in the copy, change only when there is real evidence of the correct value – the participant was located, the original questionnaire was checked. Their example: a weight of 310 kg, and the researcher is "sure" that 310 was typed instead of 130; until the suspicion is verified against the source, the value does not change. Change log: who changed, when, what and why – in a notebook, in a text column in the file, or, best of all, as comments in the software's command file (on the site: the syntax file that each calculator downloads documents what was run; the changes to the data are recorded in the template's instructions sheet or in the notes column of the legend – the site's note). Two copies of a corrected variable – "age" and "age_corrected" – so the analysis can be run with and without the correction to see whether it changed anything. And a second pair of eyes on every change, preferably those of a more experienced researcher (Cunningham & Muir, 2023).

Spreadsheets – the danger the site lives with. Cunningham and Muir recommend entering and managing data in database software and not in a spreadsheet, for one reason: in Excel you can accidentally sort one column without the rest of the columns – and from that moment all the values belong to the wrong participants – delete or duplicate a row, or delete an entire column. The site's template is nonetheless an Excel file, because that is what most researchers have; the safeguards are a site convention, not a citation: an ID column (rule 3 – a number out of place gives away a partial sort), sorting only the whole table (select all the columns before "Sort"), the locked source, and a working copy for each analysis – and the checker reports an ID that is not unique, which is the sign of a duplicated row.

Three types of error. (1) Missing values – the easiest to detect (Missing values). (2) Impossible values – age 200, gender 3. What defines them is knowledge of the subject, set before data collection: hard limits – a value that cannot be, requiring immediate checking – and soft limits – a possible but rare value (a tenth marriage) that may be entered and is flagged for checking. In an online questionnaire a warning is better than a block: if the form allows breastfeeding up to 24 months only, a mother who breastfed for four years will type 24 or "don't know" – two errors no one will discover, because no impossible value was created. Some impossible values show up only in a cross-check: 12 children – possible; 12 children at age 15 – not. An impossible value that cannot be verified against the source does not stay – it becomes missing, documented so that it can be distinguished from other missing values. (3) Possible incorrect values – 10 years of schooling typed as 1, an answer to question 29 recorded under question 30 – the hardest, because nothing in the file gives them away; they are detected only against the source, in cross-checks with other variables, and in a random sample of questionnaires compared with the file (Cunningham & Muir, 2023). For manual entry from paper, they present double entry – two typists, comparing the differences, and at least 25% of the questionnaires if not all of them – and the opposing view, that simple range checks catch most of the errors that change a result.

The descriptive screening they describe is what the calculator Frequencies and descriptive statistics gives for each variable: mean, median, minimum, maximum, number of missing values, a histogram for continuous – and a correlation matrix and crosstabulations to see an association in an unexpected direction. The checker on this page does the step before it: ranges against the legend, text in a numeric column, missing codes, constant columns. What it does not do: cross-checks (age × children), comparison with the source, and the decision whether a variable is useful at all – for Cunningham and Muir, a variable in which 90% of the values are identical, or that is mostly missing, is dropped from the analysis. And reporting, in the method section: how many missing (by type – refusal, "don't know", not applicable), how many impossible, how many suspicious, what was done with them, and results with and without the corrected values. For them the codebook also includes the definitions of the impossible and the skip patterns – on the site, the "notes" column in the legend.

From the template to SPSS and back

An Excel file is opened in SPSS through File › Open › Data (file type Excel, with "Read variable names from the first row of data" checked), and then the label, value labels and level of measurement of each variable are set manually in the Variable View (Pallant, 2020, Chapter 4; Field, 2024, Section 4.6.9). The syntax that the checker (and every calculator on the site) downloads saves the manual part: a single CD line sets the folder (that is the only thing to update), a GET DATA command loads the data sheet, and the dictionary commands – VARIABLE LABELS, VALUE LABELS, VARIABLE LEVEL – are written from the legend:

CD 'C:\Rdata'.
GET DATA
  /TYPE=XLSX
  /FILE='DATA_template_example_eng.xlsx'
  /SHEET=name 'data'
  /CELLRANGE=FULL
  /READNAMES=ON.
DATASET NAME data WINDOW=FRONT.

VARIABLE LABELS
  sex 'Sex of participant'
  age 'Age in years'
  education 'Highest level of education'.
VALUE LABELS
  /sex
    1 'male'
    2 'female'
  /education
    1 'high school'
    2 'post-secondary'
    3 'academic – bachelor's degree'
    4 'academic – graduate degree'.
VARIABLE LEVEL sex (NOMINAL).
VARIABLE LEVEL education (ORDINAL).
VARIABLE LEVEL age (SCALE).
SAVE OUTFILE='DATA_template_example_eng.sav'.

In the opposite direction – if you received a .sav file and want to run it on the site (or simply open it in Excel) – From SPSS to Excel produces this template from it: a data sheet and a legend sheet built from the file's dictionary (label, level of measurement, value labels, defined missing values).

Common mistakes

Two header rows (the variable name and the question wording below it). The calculators read one row; the question wording goes into the "description" column of the legend.

A summary or mean row at the bottom of the sheet. It is read as an additional participant.

Text instead of a code ("man"/"woman" instead of 1/2). It works as categorical, but breaks as soon as there is a spelling mistake in one of the cells – "wman" becomes a third category. Code + legend is more robust.

99 as missing without telling anyone. See Missing values.

A decimal comma (3,5) or units in the cell (25 years, 80%). The cell is read as text, that is, as missing.

A different variable name in the legend ("age " with a space, "Gender" versus "gender"). The row in the legend is ignored – the checker reports it.

A scale score computed with an Excel formula – it is saved as a formula, and disappears when copied or converted. The alpha page computes and returns values.

Long format (a row per observation, a "time" column and a "value" column). Repeated measures go into columns (Field, 2024, Section 4.6.1).

Sorting one column without the rest. From that moment the values belong to the wrong participants; the ID gives it away, the backup saves you (Cunningham & Muir, 2023).

"Fixing" a value without evidence from the source, in the original file. 310 kg stays until the questionnaire is checked; the change – in the copy, in the log (Cunningham & Muir, 2023).

"Don't know" and refusal under the same code. The distinction is lost forever; two codes, both in the legend (Cunningham & Muir, 2023).

References

Cunningham, S. A., & Muir, J. A. (2023). Data cleaning. In A. L. Nichols & J. E. Edlund (Eds.), The Cambridge handbook of research methods and statistics for the social and behavioral sciences: Vol. 1. Building a program of research (pp. 443-467). Cambridge University Press. https://doi.org/10.1017/9781009010054.022

Field, A. (2024). Discovering statistics using IBM SPSS statistics (6th ed.). SAGE.

Pallant, J. (2020). SPSS survival manual: A step by step guide to data analysis using IBM SPSS (7th ed.). Routledge.

According to the site rule, the reference list includes only sources that were read in the original. Field (2024) and Pallant (2020) in a reflowable digital copy – cited by section and chapter; Cunningham and Muir (2023) – by the whole chapter; Osborne (2013) – as cited in them. The template rules themselves (the sheet names, the words recognized in the type column, the 7-values rule) are site conventions and are not taken from a source.