Table of Contents
Cronbach’s Alpha is a measure of internal consistency of a questionnaire and can be calculated in Excel using the CORREL function. This function takes the set of variables from the questionnaire and calculates a coefficient of correlation between each item and the total score of the questionnaire. This coefficient is then multiplied by the number of items minus one to calculate the Cronbach’s Alpha value. The Cronbach’s Alpha coefficient ranges from 0 to 1, where a value closer to 1 indicates a higher level of internal consistency.
Chronbach’s Alpha is a way to measure the of a questionnaire or survey.
Cronbach’s Alpha ranges between 0 and 1, with higher values indicating that the survey or questionnaire is more reliable.
The following step-by-step example explains how to calculate Cronbach’s Alpha in Excel.
Step 1: Enter the Data
Suppose a restaurant manager wants to measure overall satisfaction among customers. She decides to send out a survey to 10 customers who can rate the restaurant on a scale of 1 to 3 for various categories.
First, let’s enter the data that contains the survey responses for each of the 10 customers:
Step 2: Perform a Two-Factor ANOVA Without Replication
Next, we’ll perform a two-way ANOVA without replication.
To do so, click the Data tab along the top ribbon and then click the Data Analysis option under the Analysis group:
If you don’t see this option available, you need to first .
In the dropdown menu that appears, click Anova: Two-Factor Without Replication and then click OK. In the new window that appears, fill in the following information and then click OK:
The following results will appear:
Step 3: Calculate Cronbach’s Alpha
Cronbach’s Alpha turns out to be 0.773.
The following table describes how different values of Cronbach’s Alpha are usually interpreted:
| Cronbach’s Alpha | Internal consistency |
|---|---|
| 0.9 ≤ α | Excellent |
| 0.8 ≤ α < 0.9 | Good |
| 0.7 ≤ α < 0.8 | Acceptable |
| 0.6 ≤ α < 0.7 | Questionable |
| 0.5 ≤ α < 0.6 | Poor |
| α < 0.5 | Unacceptable |
Since we calculated Cronbach’s Alpha to be 0.773, we would say that the internal consistency of this survey is “Acceptable.”
Bonus: Feel free to use this to automatically find Cronbach’s Alpha for a given dataset.