Analyzing a Likert Questionnaire in Excel and SPSS, Step by Step

The questionnaires are in and you have a file with a few hundred rows. This article is the path from raw data to the tables in your results chapter, with an Excel formula and SPSS syntax for every step. The running example is a hypothetical questionnaire, "satisfaction with online learning," with three dimensions: content (items q1 to q4), interaction (q5 to q8) and technology (q9 to q12), on a five-point scale (1 = strongly disagree to 5 = strongly agree), where q4 and q8 are negatively worded, and 120 respondents.

Step 1: Enter the data correctly, one row per person

The right layout is simple: each row is a respondent, each column an item, the first column an ID. Cells contain numbers, not option text.

If your export has option text ("agree") instead of numbers, build a small helper table in Excel: the five labels ("strongly disagree" to "strongly agree") in column Q and their codes 1 to 5 next to them in column R. Then convert each cell with one formula:

=VLOOKUP(B2, $Q$2:$R$6, 2, FALSE)

If you get #N/A, the cause is almost always characters: a trailing space, a different apostrophe or, in Persian data, Arabic "ي" and "ك" instead of the Persian letters. Normalise them with Find & Replace.

If you built the questionnaire in Porsino you can skip this step: the SPSS (.sav) export builds variable names from each question's key, carries the option codes and labels, and sets the measurement level. Just give the items short, meaningful keys (q1, q2, …) before you publish.

In SPSS's Variable View, set Measure to Ordinal for items and Scale for the dimension scores you'll create in step 5, and write the label for each code in the Values column.

Step 2: Clean before you analyze anything

• Out-of-range codes. Run a frequency table; a 55 on a 1–5 scale is a typo.
• Straight-lining. Someone who chose "strongly agree" for every item, negative ones included, contradicts themselves.
• Speeders. If most people took about six minutes, a 40-second response wasn't read.
• Duplicates from the same person.

FREQUENCIES VARIABLES=q1 TO q12 /ORDER=ANALYSIS.

Write your exclusion rule before you look at the results, and report how many responses you excluded. In Porsino the "suspicious responses" report flags speeders and straight-lined matrices automatically.

Step 3: Reverse the negative items

A negatively worded item ("I usually get bored in online classes") must be flipped before any calculation, so that a higher number always means more satisfaction. Formula: new score = (number of points + 1) − old score; 6 minus the score on a five-point scale, 8 minus the score on a seven-point scale.

Excel:  =6-E2

SPSS:   RECODE q4 q8 (1=5) (2=4) (3=3) (4=2) (5=1) INTO q4r q8r.
        EXECUTE.

In Excel, if q1 to q4 are in columns B to E, write this formula in a new column F (q4r) and fill it down.

Two precautions: always recode INTO a new variable and keep the original, and never reverse an item twice. Forgetting this step is the most common cause of a low alpha; in the worked example in questionnaire validity and reliability, reversing one item took alpha from −0.40 to 0.85.

Step 4: Reliability of each dimension

Compute Cronbach's alpha for each dimension separately, using the reversed items:

RELIABILITY
  /VARIABLES=q1 q2 q3 q4r
  /SCALE('Content') ALL
  /MODEL=ALPHA
  /STATISTICS=DESCRIPTIVE SCALE
  /SUMMARY=TOTAL.

The Item-Total Statistics table is the most useful part of the output. For the eight-person data in that worked example, the dimension's alpha is 0.85 and the table looks like this:

Item	Corrected item-total correlation	Alpha if item deleted
q1	0.83	0.74
q2	0.64	0.83
q3	0.50	0.88
q4r	0.82	0.75

q3 is the weakest item and deleting it would raise alpha to 0.88. But 0.85 is already good, and if q3 covers part of the dimension's content it stays; don't delete items just to gain a few hundredths of alpha. Excel has no built-in alpha function; paste your data into the Cronbach's alpha calculator to see alpha and "alpha if item deleted" at once.

Step 5: Dimension scores: the mean, not the sum

SPSS:   COMPUTE content = MEAN.3(q1, q2, q3, q4r).
        COMPUTE interaction = MEAN.3(q5, q6, q7, q8r).
        COMPUTE tech = MEAN.3(q9, q10, q11, q12).
        EXECUTE.

Excel:  =AVERAGE(B2:D2, F2)

The mean has two advantages: the score stays on the same 1–5 scale and remains interpretable, and dimensions with different numbers of items become comparable. MEAN.3 means "only if at least three items were answered," so one skipped item doesn't wipe out that person's score. In the Excel formula, column F is the reversed item, not the original column E; put the result in column G (the content score).

Step 6: The descriptive statistics examiners expect

For each item, the percentage distribution of answers and the share who agree (4 and 5); for each dimension, mean, standard deviation, minimum, maximum, skewness and kurtosis.

SPSS:   DESCRIPTIVES VARIABLES=content interaction tech
          /STATISTICS=MEAN STDDEV MIN MAX SKEWNESS KURTOSIS.

Excel:  =AVERAGE(G2:G121)    =STDEV.S(G2:G121)    =MEDIAN(G2:G121)
        =SKEW(G2:G121)       =KURT(G2:G121)
        =COUNTIF(B2:B121,">=4")/COUNT(B2:B121)

To interpret a five-point mean, many theses split the 1–5 range into five bands of 0.8: 1–1.80 very low, 1.81–2.60 low, 2.61–3.40 moderate, 3.41–4.20 high and 4.21–5 very high. That is a descriptive convention, not a statistical test; if you want to say "above average," you need the next step.

And always look at the distribution next to the mean: a mean of 3 can mean "everyone is neutral" or "half strongly agree and half strongly disagree." Whether the scale has five or seven points also affects interpretation; see Likert scales: five points or seven?

Step 7: One-sample t-test against 3

A very common thesis hypothesis: "mean satisfaction with content is above the midpoint." On a 1–5 scale the midpoint is 3 (on a seven-point scale, 4).

T-TEST
  /TESTVAL=3
  /VARIABLES=content interaction tech
  /CRITERIA=CI(.95).

Results for the hypothetical sample of 120:

Dimension	Mean	SD	t	df	p (two-tailed)	Cohen's d
Content	3.42	0.81	5.68	119	< 0.001	0.52
Interaction	2.91	0.88	−1.12	119	0.265	−0.10
Technology	3.08	0.95	0.92	119	0.358	0.08

The correct reading: the content mean is significantly above 3, with a medium effect size (d around 0.5). The interaction mean is below 3, but not significantly, so you cannot write "satisfaction with interaction is below average." Technology doesn't differ significantly from 3 either, and "we found no significant difference" is not the same as "we proved they are equal."

In Excel the same test takes two formulas (G is the dimension score column):

t = (AVERAGE(G2:G121)-3) / (STDEV.S(G2:G121)/SQRT(COUNT(G2:G121)))
p = T.DIST.2T(ABS(t), COUNT(G2:G121)-1)
d = (AVERAGE(G2:G121)-3) / STDEV.S(G2:G121)

If the hypothesis is directional ("greater than 3") and the mean is in that direction, the one-tailed p is half the two-tailed p; recent SPSS versions show both. Report the effect size too: with large samples, tiny differences become "significant."

A model sentence for your results chapter: "The mean of the content dimension (M = 3.42, SD = 0.81) was significantly higher than the theoretical midpoint of 3, t(119) = 5.68, p < .001, d = 0.52."

When should you switch to a non-parametric test?

The "is Likert ordinal or interval?" debate is old, but the practical decision is simpler:

• Dimension scores (the mean of several items) with 30 or more respondents and no severe skew: parametric tests such as t are dependable. The usual rule of thumb for skewness and kurtosis is between −2 and +2.
• A single item on its own: ordinal data with only five values; a non-parametric test is safer.
• A small sample (under 30) with obvious skew or a pile-up at one end of the scale: non-parametric.

Normality tests such as Kolmogorov–Smirnov flag even trivial deviations with large samples; Shapiro–Wilk suits small samples better, and with large samples look at the histogram and skewness instead.

Goal	Parametric test	Non-parametric equivalent
Compare with a theoretical value (3)	One-sample t	One-sample Wilcoxon (or a binomial test on the share who agree)
Two independent groups	Independent t	Mann–Whitney
Three or more groups	One-way ANOVA	Kruskal–Wallis
Relationship between two variables	Pearson correlation	Spearman correlation
Ranking dimensions within one group	Repeated-measures ANOVA	Friedman

In SPSS, the one-sample Wilcoxon is under Analyze → Nonparametric Tests → One Sample; in the settings, choose the comparison of the median with a hypothesized value (Wilcoxon signed-rank) and enter 3. To compare the share who agree in two groups (say 62% of women vs 51% of men), the two-proportion significance calculator does it without SPSS.

Excel or SPSS?

For coding, reversing, descriptives and a one-sample t-test, Excel is enough. For alpha with the item table, non-parametric tests, ANOVA and anything that must be reproducible in your appendix, SPSS (or the free JASP) is easier and safer. One simple rule: do everything in SPSS through syntax (the Paste button in every dialog) and keep the syntax file; if an examiner asks or the data change, the whole analysis reruns in one go.

Frequently asked questions

Is it legitimate to average Likert items?

For a single item it is debated; for a dimension score built from several items it is standard, accepted practice, and methodological work (including Norman, 2010) has shown parametric tests to be dependable on such scores.

What do I do with "no opinion"?

If it is the midpoint of the scale, it is simply 3. If it is an option outside the scale, meaning "don't know / not applicable," it must be treated as missing, not as 3. In Porsino's matrix question these are separate: the midpoint is one of the columns, and the separate "no opinion" option arrives in the SPSS export as code 99, declared missing.

Can I run a t-test with 20 responses?

You can, but power is low and you are more sensitive to non-normality; report the Wilcoxon as well. Better still, work out your sample size before collecting.

Should I compute a total questionnaire score?

Only if theory supports one overall construct with the dimensions as its parts (e.g. "overall satisfaction"). Adding up unrelated dimensions produces a meaningless number.

---

Haven't collected your data yet? Set up the coding right from the start: running your thesis questionnaire online, through to the SPSS export walks through it step by step, and Porsino's online questionnaire builder exports a .sav file with codes and labels ready.