Cellwise Start free

Excel guide · 4 min read

How to analyze Likert scale data in Excel

Quick answerTurn each answer into a score with =MATCH(TRIM(C2),{"Strongly disagree","Disagree","Neutral","Agree","Strongly agree"},0), which gives 1 to 5. Then report three things: the count for each answer, the top-2-box percentage (Agree plus Strongly agree) with =COUNTIF(F2:F13,">=4")/COUNT(F2:F13), and the median score.

Sample data

Every result on this page was calculated from this sheet by the same spreadsheet engine the Cellwise app uses.

ABCDEF
1ResponseTeamQ1 My workload is manageableQ2 I get useful feedback (1-5)Q3 I often think about leaving (1-5)Q1 score
21SupportAgree424
32SupportDisagree242
43SalesStrongly agree515
54SalesNeutral333
65SupportStrongly disagree251
76OpsAgree424
87SalesAgree514
98OpsNeutral33
109SupportDisagree342
1110OpsAgree 424
1211SalesStrongly agree425
1312OpsN/A15

Download this sheet as an Excel practice file (.xlsx)

Step 1: Turn text answers into 1-5 scores

You ran a team pulse survey and the form exported answers as words: Agree, Strongly disagree, Neutral. You can't average words, so give each answer a score. MATCH looks the answer up in a list written in order from worst to best, and its position is the score.

Wrap the answer in TRIM. One response came back as "Agree " with a trailing space, and without TRIM it matches nothing. The "N/A" answer gets no score, which is right: IFERROR turns it into a blank so it stays out of every average and count.

Column F in this sheet holds the score formula for each row: 4, 2, 5, 3, 1, 4, 4, 3, 2, 4, 5 and a blank for the N/A.

=IFERROR(MATCH(TRIM(C2:C13),{"Strongly disagree","Disagree","Neutral","Agree","Strongly agree"},0),"")

Result: spills 12 rows × 1 column

4
2
5
3
1
4
4
3
2
4
5

4, 2, 5, 3, 1, 4, 4, 3, 2, 4, 5 and a blank: one score per response, with N/A left out.

Step 2: The distribution: how many picked each answer

Start with the counts, because they show what an average hides. COUNTIF with a list of scores in curly braces returns all five counts at once: 1 Strongly disagree, 2 Disagree, 2 Neutral, 4 Agree and 2 Strongly agree, from 11 real answers.

Counting the text directly goes wrong on messy exports. =COUNTIF(C2:C13,"Agree") gives 3, because it misses "Agree " with the space. Counting the clean scores gives 4.

For percentages, divide by the number of scored answers: =COUNTIF(F2:F13,{1,2,3,4,5})/COUNT(F2:F13). A 100% stacked bar chart of these five numbers (Insert > Bar > 100% Stacked Bar) is the standard way to show Likert results.

=COUNTIF(F2:F13,{1,2,3,4,5})

Result: spills 1 row × 5 columns

12242

1, 2, 2, 4, 2: the number of people who chose each answer, from Strongly disagree to Strongly agree.

Step 3: Top-2-box, median and mean

Top-2-box is the share who chose 4 or 5, the Agree side. It's easy to explain in a meeting: "55% say their workload is manageable." Bottom-2-box, =COUNTIF(F2:F13,"<=2")/COUNT(F2:F13), is 27.3%.

The mean is 3.36 and the median is 4. Likert answers are ranked categories, not measurements: the gap between Disagree and Neutral isn't necessarily the same as between Agree and Strongly agree. Many analysts prefer the median and the distribution for that reason. A mean is still handy to compare the same question across teams or over time, as long as you show the counts too.

Use COUNT, not COUNTA, as the bottom number. COUNTA would count the blank-looking "" from IFERROR and the N/A row.

=COUNTIF(F2:F13,">=4")/COUNT(F2:F13)

Result: 0.54545454545 shows as 55% with Percent format

About 0.545: 6 of 11 people agree or strongly agree that their workload is manageable.

Step 4: Compare teams with AVERAGEIF and COUNTIFS

Q2 was already scored 1 to 5 in the form. AVERAGEIF by team shows where to look: Sales 4.25, Ops 3 and Support 2.75. One Ops person skipped the question, and AVERAGEIF ignores the blank instead of counting it as 0.

Top-2-box by team uses COUNTIFS for the top and the count of answers for the bottom. "<>" means "not blank", so skipped answers stay out: Sales 75%, Ops 66.7%, Support 25%.

With only three or four answers per team, one person moves the number a lot. Show the count next to each team, and think twice before reporting a team so small that answers stop being anonymous.

=COUNTIFS(B2:B13,"Support",D2:D13,">=4")/COUNTIFS(B2:B13,"Support",D2:D13,"<>")

Result: 0.25 shows as 25% with Percent format

0.25: only 1 of the 4 Support answers to Q2 is a 4 or 5.

Reverse-scored questions

Some questions are worded the other way around. On Q3, "I often think about leaving", agreeing is bad news. To combine it with other questions, flip it so 5 always means good: on a 1-5 scale, the reversed score is 6 minus the answer.

The raw average for Q3 is 2.83. Reversed it's 3.17, now on the same "higher is better" footing as Q1 and Q2. On a 1-7 scale, use 8 minus the answer.

=AVERAGE(6-E2:E13)

Result: 3.1666666667

About 3.17: the Q3 average once it's flipped so that 5 means "rarely thinks about leaving".

Common mistakes

Averaging text answers

AVERAGE ignores text, so it returns #DIV/0!. Score the answers first with MATCH.

Counting N/A or blanks as zero

A 0 drags the average down. Leave them blank with IFERROR(...,""), then use COUNT and AVERAGE, which skip blanks.

Reporting only the mean

A 3.0 can mean everyone is neutral or the team is split down the middle. Show the distribution or top-2-box next to it.

Forgetting to flip negative questions

Mixing a reverse-worded item into an overall score cancels the others out. Use 6 minus the score on a 1-5 scale.

FAQ

Can you average Likert scale data?

You can, and many reports do, but the answers are ranked categories, not true measurements. Report the median and the distribution or top-2-box too, and use the mean mainly to compare the same question across groups or over time.

How do I convert Strongly agree to 5 in Excel?

Use MATCH against the answers in order: =MATCH(TRIM(C2),{"Strongly disagree","Disagree","Neutral","Agree","Strongly agree"},0). Strongly agree is 5th, so it returns 5. Add IFERROR(...,"") to leave N/A blank.

How do I chart Likert scale results in Excel?

Put the five counts or percentages in a row per question, select them and use Insert > Bar > 100% Stacked Bar. Order the answers from Strongly disagree to Strongly agree so the colors read left to right.

What is top-2-box?

The percentage of answers in the top two options of the scale, Agree and Strongly agree on a 5-point scale. In Excel: =COUNTIF(scores,">=4")/COUNT(scores).

Functions used

Related guides

Updated