QuickStats Healthan Ayom Healthcare venture
SPSS and Statistical Software

How to Prepare an Excel Dataset for SPSS

Set up your Excel master chart so it imports cleanly into SPSS, then define variable names, value labels, measurement levels and missing values correctly.

By the QuickStats Health editorial teamUpdated 10 November 20263 min readLevel: Beginner

Most students enter data in Excel and analyse it in IBM SPSS Statistics. The move from one to the other goes smoothly when the Excel sheet is set up correctly first. This guide covers both parts: preparing Excel, then setting up the data in SPSS.

Part 1: Prepare the Excel sheet

  1. Use the first row for variable names only. One header row, no titles or merged cells above it.
  2. Use short, simple names. Start with a letter, use no spaces or special characters, for example Age, Sex, SBP_0, SBP_12wk. SPSS will change names it cannot accept.
  3. One row per participant. Repeated measurements of the same person go in separate columns (for example, Hb_baseline, Hb_3m).
  4. Enter numbers only in numerical columns. No units, no text such as "NA" or "not done". Leave missing cells blank or use one defined code.
  5. Use numeric codes for categories (1 = Male, 2 = Female), and keep the meaning of each code in a separate codebook sheet.
  6. Remove formatting that carries meaning. Colours, bold text and comments are lost on import.
  7. Keep only the data on the sheet you import. Move summaries, charts and notes to other sheets.

Part 2: Import into SPSS

In recent versions of SPSS, use File → Import Data → Excel, choose the sheet and tick the option to read variable names from the first row. Check that the number of cases and variables matches your Excel sheet.

Part 3: Set up Variable View

Column in Variable View What to set Example
Name Short variable name Hb_gdL
Type Numeric for numbers and coded categories Numeric
Label Full description used in output Haemoglobin (g/dL)
Values Value labels for codes 1 = Male, 2 = Female
Missing Codes that mean missing 999
Measure Scale, Ordinal or Nominal Scale for haemoglobin

The Measure setting matters: numerical measurements are Scale, ordered categories are Ordinal, and unordered categories are Nominal. Some procedures and charts depend on it.

Part 4: Check before analysis

  • Run Analyze → Descriptive Statistics → Frequencies for categorical variables to spot wrong codes.
  • Run Descriptives for numerical variables to check minimum and maximum values.
  • Fix errors at the source and re-import, or correct them in SPSS and keep a record of every change.
  • Save the cleaned file as a new .sav file and keep the original Excel file unchanged.

Common mistakes

  • Text such as "<1" or ">100" in numerical columns, which turns the whole variable into text.
  • Leaving codes without value labels, so tables show "1" and "2" instead of "Male" and "Female".
  • Wrong Measure settings, such as haemoglobin set as Nominal.
  • Editing the only copy of the data without keeping the original.

Key takeaways

  • Clean, simple Excel structure makes the SPSS import straightforward.
  • Define labels, value labels, missing codes and measurement levels in Variable View.
  • Check frequencies and ranges before running any analysis.
  • Keep the original data file and a record of changes.

References

  1. IBM. IBM SPSS Statistics documentation. ibm.com/docs/en/spss-statistics
  2. Broman KW, Woo KH. Data organization in spreadsheets. Am Stat. 2018;72(1):2–10. doi:10.1080/00031305.2017.1375989
  3. Field A. Discovering Statistics Using IBM SPSS Statistics. 5th ed. London: SAGE; 2018.

This article is for education. Always follow the requirements of your university, guide, ethics committee and target journal.