Before a questionnaire or rating scale can answer a research question, two things have to be true: it measures what it claims to measure (validity), and it measures it consistently (reliability). The Department of Education’s (DepEd) Action Research Toolkit covers how to build such instruments in section 4.2, including a table of specifications and expert validation, but it does not show how to compute reliability. Below, validity and reliability are explained in plain terms, then Cronbach’s alpha is computed in Excel, step by step, on a 20-teacher pilot of an eight-item scale.
It is written for teachers building a survey or rating scale for action research, and for school heads and research committees who review instruments.
Key points
- Validity first. Plan the items with a table of specifications, write more items than you need, and have a subject-matter expert (and if possible a measurement expert) review them, as the toolkit advises.
- Pilot before you collect. Try the instrument with 20 to 30 people like your participants, but not your participants, and fix items that confuse them.
- Cronbach’s alpha checks internal consistency. It is one formula: alpha = k ÷ (k − 1) × (1 − sum of item variances ÷ variance of total scores). In Excel,
VAR.SandSUMare all you need. - Reverse-score negative items first. In our pilot, alpha is 0.47 when a negatively worded item is left as answered, and 0.81 after it is reverse-scored.
- Check each item. A weak item shows a low or negative item-total correlation; removing our weakest item raises alpha from 0.81 to 0.84.
- Alpha is not validity. A scale can be consistent and still measure the wrong thing.
Validity: does it measure what you intend?
Validity is built in while you write the instrument, then checked by others. The toolkit’s steps for preparing paper-and-pencil instruments include:
- state the objective of the instrument;
- describe the ability, knowledge, skill or attribute to be measured, after reviewing the literature for its components;
- prepare a table of specifications, “a two-way grid that specifies the content of the test or dimensions of the trait to be measured, with the proposed number of items for each”;
- write more items than you need, with “about equal number of positively stated” and “negatively stated items” in rating scales;
- select the draft items and prepare the scoring plan, reversing the scoring of negatively stated items; and
- “present the draft of instrument to a subject matter expert and, whenever possible, to a test or measurement expert for validation”, and revise.
The kinds of validity evidence a teacher can gather:
| Kind | Question it answers | How a teacher checks it |
|---|---|---|
| Content validity | Do the items cover what they should, in the right proportions? | Table of specifications; review by two or three subject-matter experts, such as a master teacher and an education program supervisor |
| Face validity | Do the items look relevant and clear to the people who will answer them? | Pilot with a few people like your participants; ask what each item means to them |
| Construct validity | Do the scores behave as the trait should? | Check that items intended to go together correlate (the item analysis below), and that scores differ between groups where they should |
| Criterion validity | Do the scores agree with another measure of the same thing? | Correlate with an existing measure, such as a standardized test or teachers’ ratings |
For a test of learning, the table of specifications ties items to the learning competencies; for a rating scale, to the components of the trait. The toolkit’s example for self-esteem allots five items each to significance, competence, power and virtue.
Reliability: does it measure consistently?
Reliability means that the same people would get similar scores if measured again, or that items meant to measure the same thing agree with each other.
| Kind | What it checks | How |
|---|---|---|
| Internal consistency | Do the items of one scale agree with each other? | Cronbach’s alpha (for right-or-wrong items, the same formula is called KR-20) |
| Test-retest | Do scores stay similar over a short time? | Give the instrument twice, two to three weeks apart, and correlate the scores |
| Inter-rater | Do two raters score the same work alike? | Two people score the same responses; compare their scores |
For a questionnaire or rating scale in action research, internal consistency is the usual check, because it needs only one pilot.
The pilot
Give the draft to 20 to 30 people similar to your participants, such as teachers in another school or learners in another section, not the people in your study. Watch for items that are skipped, misread or answered the same way by everyone, and ask a few respondents what they understood each item to mean.
Our example is a pilot, simulated for this guide, of an eight-item scale on teachers’ confidence in using reading assessment results, answered by 20 teachers from 1 (strongly disagree) to 5 (strongly agree). Item Q4 is negatively worded (“I am unsure what to do with my learners’ reading levels”), and item Q7 is an item that turns out not to fit. The raw answers, as the teachers marked them:
| Respondent | Q1 | Q2 | Q3 | Q4 | Q5 | Q6 | Q7 | Q8 |
|---|---|---|---|---|---|---|---|---|
| R1 | 4 | 3 | 4 | 2 | 2 | 2 | 4 | 2 |
| R2 | 2 | 2 | 3 | 4 | 3 | 3 | 3 | 2 |
| R3 | 4 | 3 | 5 | 1 | 4 | 4 | 4 | 5 |
| R4 | 3 | 4 | 3 | 3 | 3 | 3 | 4 | 2 |
| R5 | 4 | 1 | 4 | 2 | 3 | 3 | 3 | 3 |
| R6 | 4 | 4 | 4 | 2 | 4 | 3 | 2 | 4 |
| R7 | 2 | 2 | 3 | 2 | 3 | 3 | 3 | 2 |
| R8 | 4 | 5 | 4 | 2 | 4 | 5 | 2 | 4 |
| R9 | 2 | 2 | 3 | 2 | 5 | 4 | 3 | 4 |
| R10 | 3 | 3 | 4 | 3 | 2 | 5 | 2 | 4 |
| R11 | 3 | 3 | 2 | 2 | 3 | 3 | 2 | 3 |
| R12 | 4 | 2 | 4 | 2 | 4 | 4 | 3 | 2 |
| R13 | 5 | 5 | 4 | 1 | 3 | 4 | 3 | 2 |
| R14 | 2 | 3 | 2 | 4 | 1 | 2 | 2 | 3 |
| R15 | 1 | 1 | 1 | 5 | 2 | 1 | 3 | 1 |
| R16 | 4 | 4 | 3 | 3 | 4 | 2 | 3 | 2 |
| R17 | 4 | 2 | 3 | 2 | 2 | 3 | 3 | 2 |
| R18 | 3 | 3 | 3 | 3 | 3 | 3 | 4 | 2 |
| R19 | 3 | 3 | 4 | 3 | 3 | 2 | 4 | 4 |
| R20 | 3 | 3 | 4 | 2 | 3 | 3 | 3 | 3 |
Copy the table into Excel with the respondents in A2:A21 and the items in B2:I21.
Cronbach’s alpha in Excel, step by step
The formula is:
alpha = k ÷ (k − 1) × (1 − Σ item variances ÷ variance of total scores)
where k is the number of items. Alpha rises when items vary together, so that the total varies much more than the items do separately.
Step 1. Reverse-score the negative item. In an empty column, say L, enter =6-E2 and copy it down to row 21: a 1 becomes 5, a 2 becomes 4, and so on. Copy the results and paste them over column E with Paste Special, Values, so that column E holds the reversed Q4. Keep the original answers in another sheet.
Step 2. Compute each item’s variance. Under each item’s column, in row 23, enter =VAR.S(B2:B21) and copy it across to the eighth item (with the reversed Q4 in place of the original).
Step 3. Compute each respondent’s total. In a new column, =SUM(B2:I2) across the eight items (again with the reversed Q4), copied down for all 20 respondents.
Step 4. Compute the variance of the totals. =VAR.S(J2:J21) on the totals column.
Step 5. Apply the formula. With the item variances in B23:I23 and the variance of the totals in J23:
=(8/7)*(1-SUM(B23:I23)/J23)
In our pilot, the eight item variances add up to 7.71 and the totals have a variance of 26.09, so alpha = (8 ÷ 7) × (1 − 7.71 ÷ 26.09) = 0.81.
What happens if you skip Step 1? With Q4 left as answered, alpha is 0.47. The negatively worded item pulls against the others, and a sound scale looks unreliable. This is the most common reason a teacher’s alpha comes out low.
Item analysis: which items hold the scale together?
Two more columns show how each item behaves:
- Corrected item-total correlation: the correlation of an item with the total of the other items. In Excel, for Q1: add a column of totals without Q1,
=J2-B2, then=CORREL(B2:B21, K2:K21). Items should correlate positively with the rest, usually at 0.30 or more. - Alpha if item deleted: alpha computed again without that item. If it rises when an item is removed, that item weakens the scale.
| Item | Mean | Variance | Corrected item-total correlation | Alpha if item deleted |
|---|---|---|---|---|
| Q1 | 3.20 | 1.01 | 0.66 | 0.76 |
| Q2 | 2.90 | 1.25 | 0.41 | 0.80 |
| Q3 | 3.35 | 0.87 | 0.81 | 0.74 |
| Q4 | 3.50 | 1.00 | 0.76 | 0.75 |
| Q5 | 3.05 | 0.89 | 0.49 | 0.79 |
| Q6 | 3.10 | 1.04 | 0.58 | 0.77 |
| Q7 | 3.00 | 0.53 | -0.04 | 0.84 |
| Q8 | 2.80 | 1.12 | 0.47 | 0.79 |
The table uses Q4 reversed. Every item correlates with the rest except Q7, whose correlation is −0.04; removing it raises alpha from 0.81 to 0.84. Q7 also has the smallest variance, a sign that most teachers answered it the same way. Before deleting it, read it again: an item that does not fit may be unclear, may measure something else, or may belong in a different scale. Revise or remove it, and say which in your report.
How high should alpha be?
A common rule of thumb treats 0.70 as acceptable for research instruments, 0.80 as good, and 0.90 or more as excellent, with values below 0.60 a sign the items do not hang together. Keep three cautions in mind:
- Alpha depends on the number of items. A long scale can reach a high alpha even if its items are only loosely related; a three-item scale may be sound with an alpha near 0.70.
- Very high alpha can mean repetition. Above about 0.95, items may be asking the same thing in different words.
- Alpha assumes one trait. If a questionnaire has several parts, such as attitudes, practices and needs, compute alpha separately for each part, not for the whole questionnaire.
Alpha says that items agree with each other, not that they measure what you intended; that is the work of the validity checks above. And alpha belongs to the scores of a group, so report the alpha from your own pilot or study, not only one from another study.
What to write in your proposal or report
In the methods section, describe:
- how the instrument was developed (table of specifications, item pool, expert review, and who the experts were);
- the pilot: who answered it, how many, and what was changed;
- reliability: “Cronbach’s alpha for the eight-item scale in the pilot was 0.81; item Q7, which did not correlate with the other items, was removed, giving 0.84 for the final seven items”; and
- how negatively worded items were scored.
The action research scoring guide in the Research Management Guidelines (Annex 4.b of DO 16, s. 2017) gives full points for data gathering when “research instruments, if any, are appropriate for obtaining the desired kind of data / information”; a short account of validity and reliability is the evidence.
Common mistakes
- Not reverse-scoring negative items before computing alpha.
- Computing one alpha for a whole questionnaire with different parts.
- Piloting with your own participants. They will have seen the items before the study.
- Treating alpha as proof of validity. It shows consistency, not meaning.
- Deleting items only to raise alpha. Remove an item for a reason you can explain, and keep the content the table of specifications requires.
- Reporting someone else’s alpha only. Report the alpha for your own respondents.
Frequently asked questions
How do you compute Cronbach’s alpha in Excel?
Compute each item’s variance with VAR.S, each respondent’s total with SUM, and the variance of the totals with VAR.S. Then alpha = (k ÷ (k − 1)) × (1 − sum of item variances ÷ variance of totals), where k is the number of items: for eight items, =(8/7)*(1-SUM(item variances)/variance of totals). Reverse-score negatively worded items first.
What is an acceptable Cronbach’s alpha?
A common rule of thumb is 0.70 or higher for research instruments, with 0.80 or more considered good. Interpret it with the number of items in mind, and compute it separately for each part of a questionnaire that measures a different trait.
How many respondents do I need for a pilot test?
Usually 20 to 30 people similar to your participants. Alpha from a small pilot is a rough estimate, so report the alpha from your main study as well.
What is the difference between validity and reliability?
Reliability is consistency: the instrument gives similar results each time and its items agree. Validity is accuracy: it measures what it is meant to measure. An instrument can be reliable without being valid, but not valid without being reliable.
Does the DepEd Action Research Toolkit require Cronbach’s alpha?
The toolkit asks for expert validation of paper-and-pencil instruments and discusses the reliability and validity of standardized tests, but it does not give a reliability statistic for teacher-made instruments. Cronbach’s alpha is the standard way to report the internal consistency of a rating scale or questionnaire.
More on DepEd research
- Start here: DepEd Action Research Guide: From Topic to Report
- Weighted Mean and Likert Scale Interpretation in Excel
- Pearson Correlation in Excel for Action Research
- Chi-Square Test in Excel for Action Research
- Paired and Independent t-Test in Excel for Action Research
- DepEd Action Research Proposal Format, With a Sample
- Interview and FGD Guide Questions for Action Research
Sources
- Department of Education and the Basic Education Sector Transformation (BEST) Program. (2019). Action Research Toolkit: Teacher’s Guide, section 4.2 (paper-and-pencil instruments), with support from the Australian Government.
- DepEd Order No. 16, s. 2017, Research Management Guidelines, Annex 4.b.
- Cronbach, L. J. (1951). Coefficient alpha and the internal structure of tests. Psychometrika, 16(3), 297–334.
- Tavakol, M., & Dennick, R. (2011). Making sense of Cronbach’s alpha. International Journal of Medical Education, 2, 53–55.
- Nunnally, J. C. (1978). Psychometric theory (second edition). McGraw-Hill.
Analysis by Mark Anthony Llego for NASH Reviewer. The pilot data are simulated for this guide. NASH Reviewer is not affiliated with DepEd.