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.
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
- Use the first row for variable names only. One header row, no titles or merged cells above it.
- 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. - One row per participant. Repeated measurements of the same person go in separate columns (for example,
Hb_baseline,Hb_3m). - 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.
- Use numeric codes for categories (1 = Male, 2 = Female), and keep the meaning of each code in a separate codebook sheet.
- Remove formatting that carries meaning. Colours, bold text and comments are lost on import.
- 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
- IBM. IBM SPSS Statistics documentation. ibm.com/docs/en/spss-statistics
- Broman KW, Woo KH. Data organization in spreadsheets. Am Stat. 2018;72(1):2–10. doi:10.1080/00031305.2017.1375989
- 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.