How to Calculate NPS in Excel or Google Sheets: The Formula and the Traps

Check your sheet in the free calculatorDownload the NPS spreadsheet formula card (PDF)

The formula, in spreadsheet terms

Net Promoter Score is the percentage of promoters (answers of 9 or 10) minus the percentage of detractors (0 to 6), as defined on Bain's Net Promoter System site. Because both percentages share the same total, that is the same as:

NPS = (number of promoters − number of detractors) ÷ number of answers × 100

In a spreadsheet, each count is one COUNTIF. Microsoft's COUNTIF page and Google's COUNTIF page describe the same function: count the cells in a range that meet a condition, such as ">=9". COUNTIFS does the same with several conditions at once, which is what you need for "between 0 and 6".

Step by step

1. Put the scores in one column

Export your survey answers and paste the 0 to 10 column into column A, with a heading in A1 and the first score in A2. If you have the date of each answer, put it in column B.

2. Count promoters

In D2: =COUNTIF(A2:A1000,">=9")

3. Count detractors

In D4: =COUNTIFS(A2:A1000,">=0",A2:A1000,"<=6"). A plain "<=6" works too, but the lower limit stops a stray negative number from counting.

4. Count valid answers

In D5: =COUNTIFS(A2:A1000,">=0",A2:A1000,"<=10"). This is the denominator. Using COUNTIFS instead of COUNTA means blank rows, headings repeated in the middle of an export and a mistyped 11 are all left out.

5. Work out the score

In D6: =ROUND((D2-D4)/D5*100,1). If you want the passives too, D3 is =COUNTIFS(A2:A1000,">=7",A2:A1000,"<=8").

Example: 20 answers of 10, 9, 9, 8, 7, 10, 6, 9, 3, 10, 8, 9, 5, 10, 7, 9, 10, 2, 9, 8. Promoters 11, passives 5, detractors 4. (11 − 4) ÷ 20 × 100 = +35.

6. Check it against a calculator

Copy the same column and paste it into the free NPS calculator in "Paste scores" mode. It accepts commas, spaces or new lines, skips anything that is not a whole number from 0 to 10, and tells you how many it skipped. If the two scores match, the sheet is right.

The three data problems behind most wrong scores

Numbers stored as text. Survey exports often arrive as text. COUNTIF with ">=9" does not count the text "9", so promoters quietly drop out. Check with =COUNTA(A2:A1000)-COUNT(A2:A1000): anything above zero is text. Fix it by selecting the column and converting it to numbers (in Excel, the warning triangle offers Convert to Number; in Sheets, Format, Number).

Values outside 0 to 10. A typed 11, a 5-star rating pasted into the wrong column, or a −1 used to mean "no answer". =COUNT(A2:A1000)-D5 shows how many numbers fall outside the range; they are already left out of the score, but you should know why they are there.

The wrong denominator. Dividing by the number of rows in the export instead of the number of answers to this question lowers both percentages. People who skipped the question are not passives. The COUNTIFS in step 4 avoids this; a final check is =D2+D3+D4-D5, which should be 0.

A related trap: a 1 to 5 survey cannot be turned into NPS with these formulas. NPS is defined on the 0 to 10 scale, and any conversion is an approximation, which is why the calculator only accepts 0 to 10.

Percentages for a report or chart

For a report, show the three groups as shares of the valid answers rather than raw counts, so rounds of different sizes can sit side by side. With the counts in D2 to D5:

• Promoters %: =D2/D5
• Passives %: =D3/D5
• Detractors %: =D4/D5

Format the three cells as percentages. They should add up to 100%; if they do not, one of the counts uses a different range. A stacked 100% bar chart of the three cells, one bar per round, shows at a glance whether a better score came from more promoters or from fewer detractors. Those are different stories: more promoters usually means something went right, fewer detractors means a problem was fixed.

Keep the score itself as a plain number with a sign (+35, −10), not a percentage. NPS is the difference between two percentages and is written without the % sign; formatting the score cell as a percentage would show 3500% for +35.

The score by month

With dates in column B, the same idea splits the score by period. Put the first day of a month in F2 and the first day of the next month in G2, then:

=(COUNTIFS(A:A,">=9",B:B,">="&F2,B:B,"<"&G2)-COUNTIFS(A:A,">=0",A:A,"<=6",B:B,">="&F2,B:B,"<"&G2))/COUNTIFS(A:A,">=0",A:A,"<=10",B:B,">="&F2,B:B,"<"&G2)*100

Copy the row down with the next months in F and G. Add a column with the number of answers each month (the last COUNTIFS on its own), because a month with 12 answers swings far more than one with 120; our guide to the NPS margin of error shows by how much, and what counts as a good score covers reading the trend.

If you would rather skip the export, the free form builder's NPS survey template works the score out in its Summary tab for any 0 to 10 question, and on paid plans Fynl's Reviews tool can then email your review link to every recent customer.

The NPS calculator sits with the lifetime value calculator, the form builder and the other no-cost tools on our free tools page.

Questions people also ask

Sources

• Net Promoter System (Bain & Company) — Measuring your Net Promoter Score
• Microsoft Support — COUNTIF function
• Microsoft Support — COUNTIFS function
• Google Docs Editors Help — COUNTIF