Excel guide · 7 min read
How to calculate customer effort score (CES) in Excel
=AVERAGE(D2:D15) and the % agree is =COUNTIFS(D2:D15,">=5")/COUNT(D2:D15). Both skip blanks and "N/A" text, so only real answers count.Sample data
Every result on this page was calculated from this sheet by the same spreadsheet engine the Cellwise app uses.
| Row | A | B | C | D | E | F | G | H | I |
|---|---|---|---|---|---|---|---|---|---|
| 1 | Response | Channel | Reason | CES (1-7) | Agree from | Effort (1-5, old survey) | Flipped | ||
| 2 | R-201 | Chat | Billing | 7 | 5 | 1 | 5 | ||
| 3 | R-202 | Phone | Delivery | 3 | 2 | 4 | |||
| 4 | R-203 | Returns | 6 | Min responses | 1 | 5 | |||
| 5 | R-204 | Chat | Delivery | 5 | 3 | 4 | 2 | ||
| 6 | R-205 | Phone | Billing | 2 | 5 | 1 | |||
| 7 | R-206 | Chat | Returns | 7 | 2 | 4 | |||
| 8 | R-207 | Tech issue | N/A | N/A | |||||
| 9 | R-208 | Phone | Tech issue | 5 | 1 | 5 | |||
| 10 | R-209 | Chat | Billing | 6 | |||||
| 11 | R-210 | Delivery | |||||||
| 12 | R-211 | Phone | Returns | 1 | |||||
| 13 | R-212 | Chat | Tech issue | 6 | |||||
| 14 | R-213 | Billing | 5 | ||||||
| 15 | R-214 | Phone | Delivery | 3 |
Download this sheet as an Excel practice file (.xlsx)
CES as an average score
The version of the survey most teams use today asks customers to rate one statement after a contact: "The company made it easy for me to handle my issue", from 1 (strongly disagree) to 7 (strongly agree). Higher is better. The sample has 14 survey rows from a support team, with the answers in column D.
The simplest CES is the mean of the answers. AVERAGE ignores the blank in D11 and the "N/A" text in D8 by itself, so it averages the 12 real answers: 56 / 12 = 4.67. Format it to one or two decimals; reporting 4.6666667 suggests far more precision than 12 answers can give.
Write the scale next to the number wherever you report it ("4.67 out of 7"). A CES of 4.67 is good on a 1-7 agreement scale and terrible on an older 1-5 effort scale where low is good (more on that below). Without the scale, nobody reading the report can tell which.
Result: 4.6666666667 shows as 4.67 with Number format
4.666666667, shown as 4.67: the average of the 12 numeric answers out of 7.
CES as % agree (5, 6 or 7)
Many teams report the share of customers who agree the contact was easy instead of, or next to, the average. Put the cutoff in a cell (F2 holds 5) so the definition is visible and easy to change. COUNTIFS(D2:D15,">="&F2) counts the 5s, 6s and 7s: 8. COUNT(D2:D15) counts the numeric answers: 12. CES is 66.7% agree.
The other end matters more for fixing things. =COUNTIFS(D2:D15,"<=3")/COUNT(D2:D15) is 33.3%: a third of customers disagreed that it was easy. The 4s are neutral and count in neither group (there are none in this sample). Some dashboards show agree minus disagree as a "net" figure, 33.3 points here; if you use it, label it, because it's a house metric, not a standard.
To see the whole distribution, =COUNTIFS(D2:D15,SEQUENCE(7)) spills the count of each answer from 1 to 7 (Excel 365): 1, 1, 2, 0, 3, 3, 2. A middling average can hide a split like this, with a cluster of happy customers and a cluster of frustrated ones.
Result: 0.66666666667 shows as 66.7% with Percent format
0.667: 8 of the 12 customers who answered chose 5, 6 or 7.
Keep N/A and blanks out of the bottom of the fraction
Survey exports always include rows like R-207 ("N/A") and R-210 (blank). Neither customer gave a score, so neither belongs in the count of answers. COUNT, COUNTIFS with a number criterion, AVERAGE and AVERAGEIFS all skip text and empty cells, which is why the formulas above need no clean-up.
The usual mistakes divide by the wrong number. COUNTA counts the "N/A" as an answer and gives 8/13 = 61.5% instead of 66.7%. =SUM(D2:D15)/ROWS(D2:D15) treats both non-answers as zeros and drags the average from 4.67 to 4.00. And =SUMPRODUCT(--(D2:D15>=F2)) finds 9 agrees instead of 8, because in a comparison Excel ranks any text above any number, so "N/A">=5 is TRUE. If you need SUMPRODUCT, multiply in ISNUMBER(D2:D15).
One more check: if COUNT returns fewer answers than you can see, some scores were exported as text (left-aligned, often with a green triangle). COUNT and AVERAGE skip them silently. Select the column and run Data > Text to Columns > Finish to turn them back into numbers.
Result: 0.61538461538 shows as 61.5% with Percent format
0.615: the wrong answer. COUNTA counts the N/A as a response, so % agree drops from 66.7% to 61.5%.
CES by channel and by contact reason
The overall score is a starting point; the breakdown tells you where effort comes from. In Excel 365 one formula builds the table: UNIQUE lists the channels, COUNTIFS with ">=1" counts the numeric answers for each, and AVERAGEIFS and COUNTIFS give the average and % agree.
Chat averages 6.2 with 100% agree from 5 answers, Phone 2.8 with 20% agree from 5, and Email 5.5 with 100% from 2. Swap B2:B15 for C2:C15 to break it down by reason: Billing 5.0, Delivery 3.67, Returns 4.67 and Tech issue 5.5.
Read the two tables together before acting. Delivery looks like the hard reason, but its two low scores are both phone contacts, and the one delivery chat scored 5. =FILTER(A2:A15&" ("&C2:C15&")",(B2:B15="Phone")*ISNUMBER(D2:D15)*(D2:D15<=3)) lists the four phone customers who disagreed (R-202, R-205, R-211, R-214), which is the list worth reading. Add a channel and a reason criterion together in COUNTIFS when you need to separate the two.
Result: spills 3 rows × 4 columns
| Chat | 5 | 6.2 | 1 |
| Phone | 5 | 2.8 | 0.2 |
| 2 | 5.5 | 1 |
Chat 5 answers, 6.2, 100%; Phone 5, 2.8, 20%; Email 2, 5.5, 100%: answers, average and % agree for each channel.
Don't rank on a handful of answers
Email's 100% comes from two customers. One more unhappy email customer would take it to 67%. Put a minimum number of answers in a cell (F5 holds 3 here) and show a warning instead of a score below it. The formula below does that for the % agree column.
What minimum to use depends on your volume and how the number is used. A monthly team score can live with fewer answers than a score that goes into an agent's review. Whatever you pick, show the number of answers next to every CES figure, so a reader can see when a big swing is just two people.
Result: spills 3 rows × 3 columns
| Chat | 5 | 1 |
| Phone | 5 | 0.2 |
| 2 | Too few |
Chat 5, 100%; Phone 5, 20%; Email 2, Too few: Email has fewer answers than the minimum of 3 in F5.
Older 1-5 effort surveys: flip before you compare
The original CES question was worded the other way: "How much effort did you personally have to put forth to handle your request?", from 1 (very low effort) to 5 (very high effort). Here a low number is good. If you still have answers like this (column H), averaging them gives 2.29, which looks like a bad score but means most customers found it easy: 5 of the 7 answers are 1 or 2.
To put a reverse-worded scale the right way up, subtract each answer from the scale's maximum plus one: 6 - score on 1-5 (so 1 becomes 5 and 5 becomes 1), 8 - score on 1-7. Column I uses =IF(ISNUMBER(H2),6-H2,"") so the N/A in H8 stays blank instead of giving #VALUE!. The flipped average is 3.71, which is simply 6 - 2.29.
Flipping fixes the direction, not the scale. A 3.71 out of 5 on an effort question and a 4.67 out of 7 on an ease statement are answers to different questions on different scales, so don't put them on one trend line. When a survey changes, start a new series and note the date.
Result: 3.7142857143 shows as 3.71 with Number format
3.714285714, shown as 3.71: the old effort answers flipped so that 5 = easiest. AVERAGE skips the blank left by the N/A.
CES, CSAT or NPS: which one answers your question
The three surveys look alike in a spreadsheet but answer different questions. CES asks how easy one interaction was, so it's the one to use when you're fixing a process: a confusing returns form, a phone menu, a hand-off between teams. CSAT asks how satisfied the customer was with an interaction. NPS asks how likely they are to recommend you, which is about the relationship as a whole.
The Excel work is nearly the same for all three: count the good answers with COUNTIFS, divide by COUNT, keep N/A out, break it down with UNIQUE and COUNTIFS, and show the number of answers. What differs is the cutoff and the scale, so store both in cells and label them on the report. See CSAT and NPS for those two, and analyzing Likert scale data for any other agreement question.
Common mistakes
Reporting CES without the scale
Write "out of 7" (or "% agree, 5-7") next to the number. A 4.67 means opposite things on a 1-7 ease scale and a 1-5 effort scale.
Dividing by COUNTA or all rows
Use COUNT, or COUNTIFS with ">=1", so N/A answers and blanks stay out of the bottom of the fraction.
Averaging a reverse-worded scale as if high were good
On "how much effort" questions low is good. Flip with 6 - score (1-5) or 8 - score (1-7) before comparing or charting.
Comparing channels with two answers
Show the answer count beside every score and a "Too few" warning below a minimum you set in a cell.
FAQ
What is the formula for customer effort score in Excel?
For a 1-7 agreement scale: =AVERAGE(D2:D15) for the average score, or =COUNTIFS(D2:D15,">=5")/COUNT(D2:D15) for the share who agree the contact was easy.
Is a higher or lower customer effort score better?
It depends on the question. On the common 1-7 "the company made it easy" statement, higher is better. On the older 1-5 "how much effort did you put in" question, lower is better. Check the wording before comparing scores.
Should I report CES as an average or a percentage?
Either works if you stick to one. The % agree is easier to explain and less affected by a few extreme answers; the average moves more smoothly from month to month. Many teams show both, with the number of answers.
How do I calculate CES by month in Excel?
Add date criteria to both counts, for example =COUNTIFS(D:D,">=5",E:E,">="&DATE(2026,11,1),E:E,"<"&DATE(2026,12,1)) divided by the same COUNTIFS with ">=1" instead of ">=5", where column E holds the response date.
Functions used
AVERAGEMean of numbersCOUNTIFSCounts with several conditionsCOUNTCounts numbersCOUNTACounts non-empty cellsAVERAGEIFSAverages with several conditionsSUMPRODUCTMultiply then addISNUMBERIs it a number?IFPicks a result from a testLETNames values inside a formulaUNIQUERemoves duplicates, spillsHSTACKJoins arrays side by sideFILTERKeeps matching rows, spillsSEQUENCENumbered list, spills
Related guides
- How to calculate ticket reopen rate in Excel
- How to calculate CSAT score in Excel
- How to calculate NPS in Excel
- How to analyze Likert scale data in Excel
- How to calculate service level in Excel
- Excel formulas cheat sheet: the 25 everyone should know
- How to calculate units per transaction in Excel
Updated