Cronbach's alpha in Excel: the formula, a worked example and item analysis

What Cronbach's alpha measures

Alpha estimates the internal consistency of a scale: how well a set of items that are meant to measure the same thing move together. If the items agree, a person who scores high on one tends to score high on the others, the totals spread out much more than the single items do, and alpha approaches 1. If the items are unrelated, the variance of the total is just the sum of the item variances and alpha is close to 0.

It is a property of a scale in a particular sample, not a fixed property of the questionnaire, so report it for your own data. It also does not prove that the items measure only one thing: a scale that mixes two related topics can still reach a high alpha.

Worked example: Cronbach's alpha for a four-item scale

Rows 2 to 11 are the answers of ten respondents on a 1 to 5 scale, column F the total. Rows 13 to 15 analyse each item, rows 17 to 21 calculate alpha. The scale and the answers are fictional.

Worked example: Cronbach's alpha for a four-item scale

	A	B	C	D	E	F
1	Respondent	Item 1	Item 2	Item 3	Item 4	Total
2	R1	4	5	4	3	16
3	R2	2	2	3	2	9
4	R3	5	4	5	4	18
5	R4	3	3	2	4	12
6	R5	4	4	4	2	14
7	R6	1	2	1	2	6
8	R7	3	4	3	3	13
9	R8	5	5	4	3	17
10	R9	2	1	2	3	8
11	R10	4	3	4	4	15
12
13	Variance (VAR.S)	1.789	1.789	1.511	0.667	16.178
14	Corrected item-total r	0.963	0.717	0.821	0.373
15	Alpha if item deleted	0.695	0.818	0.769	0.926
16
17	Number of items k					4
18	Sum of item variances					5.756
19	Variance of the total					16.178
20	Cronbach's alpha					0.859
21	Spearman-Brown: alpha with 8 items					0.924

A fictional scale with synthetic answers to show the calculation. It is not a validated instrument and not a clinical or diagnostic tool.

Step 1: one row per respondent, items scored in the same direction

Put each respondent in a row and each item in a column, with the total score in the last column: =SUM(B2:E2). Every item must point the same way. A negatively worded item ("I often feel on edge" in a wellbeing scale) has to be reverse-scored first, on a 1 to 5 scale with =6-B2, or it will pull alpha down and can even make it negative. In the example all four items are already scored so that higher means more of the trait.

Decide how to treat missing answers before you calculate anything. VAR.S simply skips blank cells, so a respondent with a missing item would enter some variances but not others. Either drop incomplete rows or apply a clear rule, such as prorating the total when only one item is missing.

Step 2: item variances and the variance of the total

Under each item column, =VAR.S(B2:B11) gives the item variance. In the example they are 1.789, 1.789, 1.511 and 0.667, and their sum is 5.756. =VAR.S(F2:F11) on the total column gives 16.178.

Use the same function everywhere. VAR.S (sample variance) and VAR.P (population variance) give the same alpha as long as you do not mix them, because the factor between them cancels out. Mixing them gives a wrong result.

Step 3: the alpha formula

With k in F17, the sum of item variances in F18 and the total variance in F19, the formula is =F17/(F17-1)*(1-F18/F19): 4 ÷ 3 × (1 − 5.756 ÷ 16.178) = 0.859. The factor k ÷ (k − 1) corrects for the number of items. Without it, a scale with few items would always look less reliable than it is.

Common rules of thumb: about 0.70 is acceptable for research that compares groups, 0.80 and above is good, and decisions about individual people need 0.90 or more. A very high alpha, above about 0.95, often means some items say almost the same thing and the scale could be shorter.

Item analysis: item-total correlations and alpha if item deleted

Alpha for the whole scale does not show which items help. Two numbers per item do:

• Corrected item-total correlation: the correlation of an item with the total of the other items, =CORREL(B2:B11,$F$2:$F$11-B2:B11). Subtracting the item from the total removes its correlation with itself. Values below about 0.30 suggest the item measures something else. Here items 1 to 3 range from 0.717 to 0.963, item 4 reaches only 0.373.
• Alpha if item deleted: alpha recalculated without that item, with k − 1 = 3 items, so the factor becomes 3 ÷ 2. Removing items 1, 2 or 3 lowers alpha to 0.695, 0.818 or 0.769. Removing item 4 raises it to 0.926.

That makes item 4 a candidate for rewording or removal, but not an automatic one. Check its wording and content first: an item that covers an important part of the topic may be worth keeping even if it lowers alpha a little.

Scale length and sample size

Alpha rises with the number of items. The Spearman-Brown formula predicts alpha for a scale n times as long: n × α ÷ (1 + (n − 1) × α). Doubling this scale to eight similar items would give about 0.924. It works the other way too: halving a scale lowers alpha, which is why short forms need especially strong items.

Ten respondents keep the example readable, but alpha from a small sample is very unstable. For a real scale, use at least 100 respondents and report a confidence interval from a statistics package. The same layout of one row per respondent is useful for other survey measures, such as the Net Promoter Score.

Common mistakes

• Forgetting to reverse-score. A negatively worded item that is not recoded lowers alpha and can make it negative. Recode it first, for example with =6-B2 on a 1 to 5 scale.
• Mixing VAR.S and VAR.P. Use the same variance function for the items and the total, or alpha is wrong.
• Correlating an item with the full total. The total contains the item itself, which inflates the correlation. Use the corrected version: the total minus the item.
• Calling alpha proof of one dimension. A high alpha only says the items correlate. Whether they measure one construct needs a factor analysis or at least a look at the content.
• Chasing the highest alpha. Dropping every item that lowers alpha a little can leave a narrow scale of near-duplicates. Decide on content, not on the third decimal.
• Trusting alpha from a tiny sample. With a few dozen respondents alpha can move by 0.10 or more from sample to sample. Report the sample size and, ideally, a confidence interval.