What Actually Happens to Your Spreadsheet Before Any Test Runs

Why the spreadsheet is the first place a result can go wrong

A statistical test runs on whatever values it's handed. If a header spans two rows, if “Y,” “yes,” and “1” are meant to be the same answer but entered inconsistently, or if a blank cell sometimes means “not assessed” and sometimes a genuine zero, the test doesn't know any of that. It computes exactly what's in the cells.

Data cleaning is usually described as a three-step cycle: detecting an abnormality, diagnosing what caused it, and deciding what to do about it. The deciding step is the one that carries the risk, because the same odd-looking value can be a typo to correct, a genuine outlier to keep, or a code that means something.¹ A software package cannot diagnose. It can only detect a pattern and apply a rule, which is why the interesting question is not whether it cleans your data but whether it tells you which rules it applied.

Every package already makes these calls for you

This is not a problem unique to any one tool, and it is not avoided by using a programming language instead of a menu-driven one. Wherever your analysis happens, something has already decided how to read each column:

• SPSS asks you to set a measure level on every variable, Nominal, Ordinal or Scale, and that label determines which procedures will accept the variable and what the output means. A variable imported with the wrong level is not an error anywhere; it just quietly routes to the wrong analysis.
• R and Python infer a type per column when reading a file. One stray non-numeric value, a stray “NR” or a lone dash, is usually enough for the whole column to come in as text rather than numbers, at which point summary statistics either fail or silently describe something other than what you meant.
• Excel exports are a category of their own. A date written with a comma inside it breaks a plain CSV by pushing extra fields into a row, and a leading zero on an identifier code is often gone before the file ever reaches the analysis tool.

None of these behaviours are bugs. They are reasonable defaults applied without knowledge of your study. The risk is that they are applied invisibly, so the first time you learn a column was read as text is when a test refuses to run, or worse, when it runs on something you did not intend and returns a plausible number.

The problems that should stop an import outright

A small number of problems should not be repaired at all, because any repair would be a guess about your data. Four are worth recognising, and a well-behaved tool should name the cause rather than surfacing a parser error:

• The file is too large. That means too large on disk, or too large once opened: a compressed spreadsheet can expand many times over at parse time, so the size it becomes in memory is worth checking separately from the size it occupies on disk.
• There is no actual data. A header-only file, an empty workbook, or a file that is only blank rows. This one matters because an empty dataset otherwise produces empty results that look like real output.
• A row has more fields than the header. Usually an unquoted comma inside a value, most often a date typed as “Jan 5, 2024” in a plain CSV. Naming that cause turns a cryptic failure into a thirty-second fix.
• The file is not what its extension claims. Better flagged as possibly corrupt or mislabelled than failed with an internal error from whichever parser was tried first.

StatsPlease stops on exactly these four and explains which one applied. The general point holds regardless of tool: an import that fails should tell you what about the file caused it.

What should be repaired, and written down

Everything short of those four can usually be repaired rather than rejected. The repairs themselves are not controversial; most mature tools do some version of them. Character encodings get tried in order, and Excel's Unicode marker is detected. A comma-separated file that was actually exported with semicolons, common from European-locale Excel, is detected and re-read with the right separator. Research spreadsheets often carry a title or blank row above the real column names, so the first rows are scanned to find the true header; StatsPlease scans 15, and where the answer is ambiguous it treats row 1 as the header rather than risk eating a row of real data. Column names get whitespace and line breaks stripped, blank names get a placeholder, and duplicates are de-duplicated (Age, Age_1).

The one that changes results most often is the missing-value list. Blank, NA, N/A, null, a bare period or dash, “missing,” “not recorded,” and Excel's own error strings such as #N/A and #REF! all need converting to true missing before anything else happens. This is the difference between a numeric column and a text one: a column of numbers whose blanks were typed as “-” is, to most parsers, a column of text.

None of this is a reason to skip cleaning your own sheet. It is a safety net, not a substitute for one, and any tool can only recognise the patterns it was built to recognise.

The hard case: a mostly-numeric column with a little text in it

This is where tools genuinely differ, and where a documented rule beats a silent one. A column that is 97% numbers and 3% something else has no obviously correct reading: convert it and you destroy whatever those three percent meant, leave it and you have lost a numeric variable. Most packages resolve this implicitly, by whatever their type inference happens to do. Making the rule explicit is better, and the thresholds StatsPlease uses are one defensible answer:

• If every value parses as a number once thousands separators, currency symbols, or percent signs are stripped, the column converts, and the report notes what was stripped.
• If more than 5% of values fail to parse, the column stays categorical, and the specific offending values are named rather than silently dropped.
• If under 5% fail but the failures are short, one-to-three-character codes (“K,” “X”), they are treated as deliberate codes rather than typos, and the column is not converted. This rule exists because an earlier version deleted exactly that kind of code by mistake.
• If under 5% fail and the failures are longer strings, the column converts, but every value that became missing as a result is listed.

Related refusals follow the same principle. A zero-padded code like “007” is kept as text, because converting it to a number destroys the padding that made it an identifier. Near-duplicate categories like “Yes” and “yes” are merged only when they are a recognised yes/no pair or one casing clearly dominates; two-letter lookalikes such as “ER” and “er” are left separate, because they may be genuinely distinct codes rather than a capitalisation slip. Whatever tool you use, these are the decisions to go looking for.

Nominal, ordinal, scale: why the label on a column decides the test

Variable type is the single most consequential thing about a column, because it decides which family of test is even eligible. The categories are standard across clinical statistics rather than specific to any package: Nominal, Ordinal, and Scale, plus dates, plus identifier columns that should never enter a comparison at all.⁵ Two traps are worth handling by shape rather than by a generic rule: a four-digit year is a time label, not a quantity, so it does not belong in a correlation; and a bare numeric status code such as 1, 2, 3 is nominal unless something tells you the codes are ordered.

A Likert item entered as 1–5 and left as scale data gets fed to a t-test or ANOVA, which assumes the distance from “agree” to “strongly agree” equals the distance from “disagree” to “neutral.” A single ordinal item does not guarantee that. It is worth being straight that this is contested rather than settled: the best-known argument on the other side reviews work going back to the 1930s and concludes that parametric methods are robust to exactly this kind of violation, and that objecting to them on the grounds that Likert data are ordinal is unfounded.² That position is well argued and widely cited, and a reviewer may hold it.

The conservative reading is still defensible, because the cost of the two errors is not symmetric. Routing an ordinal item to a rank-based test loses a little power in the cases where the parametric test would have been fine. Routing it to a t-test produces a mean of Likert codes, a number that reads as precise and has no clear referent. The opposite mistake, chopping a genuinely continuous measurement into two or three categories to make it “simpler,” is one the methodological literature is unusually blunt about, calling routine dichotomisation of this kind “rarely defensible” because it discards real differences between people who land in the same bucket and weakens the study's power to detect an actual effect.³⁴

Whatever tool you use, the practical move is the same: look at the type assigned to every column before you run anything. In StatsPlease each classification carries a confidence score and anything under 80% is held for you to confirm, and a type you actively confirm overrides the automatic one everywhere downstream. A type that was merely pre-filled at upload and never confirmed does not carry that weight; only a decision you actually made wins.

How all of this changes the numbers in your Results section

Type classification decides which family of test is eligible. Assumption checks then re-route within that family: normality per group, variances across groups, and a choice between the standard test, an unequal-variance version, and the non-parametric alternative.

One detail here is worth borrowing whatever you use. A group too small to test, or one where every value is identical, is untestable, and untestable is deliberately not the same as passing. An assumption that could not be checked should never be recorded as one that was met, which means the honest move is to route to the more conservative test rather than default to a t-test whose condition was never actually established. A report that distinguishes passed, violated, and untestable is telling you something a report with a bare tick cannot.

Some comparisons should be dropped before they run, because they would be artifacts rather than findings: a variable correlated with itself, anything involving a patient or study ID column, or a contingency table too sparse for chi-square to mean anything. A dropped comparison should not disappear silently; it should become a sentence saying the test was not applicable and why.

Sample size deserves the same treatment reviewers actually check for. When missing values are not imputed, each test runs only on rows with complete data for its own variables, so three numbers that are routinely conflated need separating: records supplied, records in the analytic sample, and records excluded for incomplete data. When different tests in the same manuscript end up with different sample sizes, say so, rather than leaving a reader to assume the spreadsheet's row count was the whole cohort. In AMA prose that reads: “Of the 120 records supplied, 84 comprised the analytic sample; 36 (30.0%) were excluded for incomplete data on the analysed variables.”

Finally, every automatic repair should reach the manuscript, not just the screen. In StatsPlease each converted, dropped, or preserved column appears in a Data Handling section of the exported Word or PDF file alongside the assumption checks, and any computed value that comes out as not-a-number, an infinity, or a P-value outside 0 to 1 is replaced with an explicit “not applicable” entry rather than printed as-is, with raw internal error text scrubbed the same way. The general standard is simply that a software error should never be able to sit inside a results table looking like a finding.

Before you upload, whatever you use

None of the above replaces basic housekeeping. Before analysis, in any package, it is worth confirming: one header row, one title per column, never a merged title row above a second row of sub-labels; one coding scheme per variable applied the same way in every row; one missing-value marker used consistently; and every column carrying the type it actually is, nominal, ordinal or scale, rather than whatever the import chose. A clean sheet reaches a confident classification faster and with fewer flags to review. A messy one can still be handled, but you will spend the time checking decisions instead of confirming them.

References

1. Van den Broeck J, Cunningham SA, Eeckels R, Herbst K. Data cleaning: detecting, diagnosing, and editing data abnormalities. PLoS Med. 2005;2(10):e267. https://doi.org/10.1371/journal.pmed.0020267
2. Norman G. Likert scales, levels of measurement and the “laws” of statistics. Adv Health Sci Educ Theory Pract. 2010;15(5):625–632. https://doi.org/10.1007/s10459-010-9222-y
3. MacCallum RC, Zhang S, Preacher KJ, Rucker DD. On the practice of dichotomization of quantitative variables. Psychol Methods. 2002;7(1):19–40. https://doi.org/10.1037/1082-989X.7.1.19
4. Altman DG, Royston P. The cost of dichotomising continuous variables. BMJ. 2006;332(7549):1080. https://doi.org/10.1136/bmj.332.7549.1080
5. Shreffler J, Huecker MR. Types of Variables and Commonly Used Statistical Designs. In: StatPearls. Treasure Island, FL: StatPearls Publishing; 2023.