How to prepare your Excel file before analyzing survey data in SPSS

You may have a suitable statistical test and a good analysis plan, but a messy data file will make the results misleading or cost you a long time in cleaning. Simply put, the quality of the analysis starts with the quality of the file. IBM’s documentation explains that SPSS can read variable names from the first row of an Excel file, so the way you arrange that row and the rest of the table is not cosmetic; it is an essential part of preparing the data.

1. Make each row one respondent

The first rule is simple: the second row represents the first respondent, the third row represents the second respondent, and so on. Do not split one person’s answers across more than one row, and do not leave empty rows between respondents. If you have an identifier for each participant, put it in a separate column such as ID.

Having an identifier does not mean entering the person’s name or mobile number. It is better to use an internal number that does not reveal the participant’s identity, especially when the questionnaire contains sensitive information.

2. Make each column one question or variable

Each column should measure one thing only: age in one column, gender in another, and the first question in a column of its own. Do not put two questions in one cell, and do not combine the answers to several questions in one column. This layout lets SPSS treat each column as a variable that can be described and tested.

If a multiple-choice question allows more than one answer, it is better to create a separate column for each option, usually coded 1 when the option is selected and 0 when it is not.

3. Reserve the first row for variable names

In the first row, write short, clear names such as age, gender, q1, and q2. Avoid repeating the same name, and avoid very long headings or introductory rows above the table. You can keep the full text of each question later inside SPSS as a Variable Label.

A practical example: instead of writing the whole question in the column header, use q1_service_quality, then put the full text of the question in the codebook or in the variable description inside SPSS.

4. Code the answers consistently

Do not mix text and numbers in the same column. If you decide to code gender, for example, use the same scheme in every row, such as 1 = male and 2 = female. On a five-point Likert scale you can use:

1 = strongly disagree

2 = disagree

3 = neutral

4 = agree

5 = strongly agree

What matters is not choosing particular numbers so much as sticking to the same scheme and documenting it. Create a separate sheet in the file called “Codebook”, or a companion file, that explains the meaning of each variable and each value.

5. Watch out for reverse-worded items

Some scales include reverse-worded statements to reduce response patterns, such as “I cannot use the system easily” among a set of positive statements. Do not reverse the values by hand without documenting it. Mark the item clearly in the codebook, then do the recoding inside SPSS while keeping the original variable for review.

6. Handle missing values clearly

Do not use arbitrary numbers such as 0 or 99 to indicate a missing answer unless you document them and define them as missing values in SPSS. Sometimes an empty cell is the best solution, and sometimes we need different codes to distinguish “refused to answer” from “not applicable”. The decision depends on the study design, but it must be consistent and recorded in the codebook.

7. Remove anything that is not actual data

Before importing, remove from the main data sheet:

The study title written above the table.

Logos, images, and shapes.

Merged cells.

Total and average rows.

Notes written between respondents’ rows.

Completely empty columns.

Filters or formulas you do not need for the analysis.

Keep a separate original copy before deleting anything, then create a clean copy for the analysis.

8. Check the data type in each column

Check that ages are real numbers and not text, that dates are written in one format, and that the Likert options are coded the same way. A common mistake is a value such as “five” in the middle of a column that contains the numbers 1 to 5, or a hidden space that makes two values look identical to the eye while they differ when read by software.

9. Run a logic check before importing

Ask these questions:

Are there implausible ages?

Did a value of 6 appear in a question whose range is 1 to 5?

Are there completely duplicated responses?

Is there a respondent who left most of the questions empty?

Does the number of rows match the number of expected responses?

Are there questions that were not exported from the survey platform?

An outlier is not necessarily an error; it may be a valid answer. What is required is to verify it before deleting it, not to delete everything that looks different.

10. Save the file safely

Save the clean copy in .xlsx format and give it a clear name that includes the preparation date or a version number, such as survey_clean_2026-07-13.xlsx. Make the first data sheet the one needed for the analysis, and make sure the file is closed before you upload or import it.

An example of the correct structure

A quick checklist

One row per respondent.

One column per question or variable.

The first row for names only.

No merged cells or headings above the table.

Consistent, documented coding.

Missing values clearly defined.

No empty rows or totals inside the data.

An original copy saved before cleaning.

When do you need specialist help?

If the file contains multiple-answer questions, dimensions that need to be combined, reverse-worded items, or a lot of missing data, preparation is not only about arranging columns. That is when reviewing the structure of the instrument and the analysis plan becomes important before running any tests.

You can look at ABENAN’s survey data analysis with SPSS service, or browse all our services.

Frequently asked questions

Should all text answers be converted into numbers?

Not always. Open-ended questions stay as text if they will be analyzed qualitatively, while closed questions are better coded as numbers, with a codebook that explains what each number means. What matters is not mixing text and numbers in the same column without a reason.

Can I import more than one sheet from an Excel file?

You can choose a sheet or a range when importing, but it is better to keep one clean sheet for the analysis and put the codebook or notes on separate sheets. This reduces the chance of choosing the wrong range or reading rows that are not data.

Should I delete outliers before the analysis?

They should not be deleted automatically. First check whether they are entry errors or valid values. Then assess their effect on the analysis and state your decision clearly. Sometimes an outlier is important information, not a problem to be hidden.