How to Calculate NPS (Net Promoter Score) in Excel

To calculate NPS (Net Promoter Score) in Excel, you need two COUNTIFs and one division. Count the promoters, subtract the detractors, divide by all responses and multiply by 100. This guide is for anyone who reports customer feedback, from support teams to marketing analysts, and it builds every formula on one small set of survey answers.

What is NPS and how is it calculated?#

NPS comes from one survey question: "How likely are you to recommend us to a friend or colleague?" People answer on a scale from 0 to 10, and each answer falls into one of three groups:

The formula is:

NPS = (% promoters − % detractors) × 100

where each percentage is out of all responses, passives included. The result runs from -100 (everyone is a detractor) to 100 (everyone is a promoter). NPS is written as a plain number: a score of 25, not 25%.

The NPS formula in Excel#

Here are eight example responses in A1:C9:

Click an empty cell, such as F2, and type:

=(COUNTIF(C2:C9,">=9")-COUNTIF(C2:C9,"<=6"))/COUNT(C2:C9)*100

Each part does one job:

• COUNTIF(C2:C9,">=9") counts promoters: 4 (two 10s and two 9s).
• COUNTIF(C2:C9,"<=6") counts detractors: 2 (the 3 and the 6).
• COUNT(C2:C9) counts every numeric response: 8.
• *100 turns the fraction into the usual NPS scale.

The result is 25: (4 − 2) ÷ 8 × 100. The brackets around the subtraction matter. Without them, Excel divides the detractors by 8 first and gives the wrong answer. The COUNTIF function guide explains how the ">=9" criteria work, and the article on how to use COUNTIF has more criteria examples.

How to show promoters, passives and detractors separately#

Most reports show the three groups as well as the score. Calculate each share with COUNTIF and COUNT, and format the cells as percentages:

The three shares add up to 100%, which is a good check. Then NPS is simply promoters minus detractors: 50 − 25 = 25. The guide to COUNTIF as a percentage covers this "count divided by total" pattern in more detail.

To label each response, add a column with IF. In D2, type and fill down:

=IF(C2="","",IF(C2>=9,"Promoter",IF(C2>=7,"Passive","Detractor")))

The first IF leaves the label blank when there's no score. Without it, an empty cell counts as 0 and gets labelled "Detractor".

How to calculate NPS for a region, product or month#

To get NPS for one group, add a condition with COUNTIFS. For the North region:

=(COUNTIFS(B2:B9,"North",C2:C9,">=9")-COUNTIFS(B2:B9,"North",C2:C9,"<=6"))/COUNTIF(B2:B9,"North")*100

North has four responses: one promoter (10), one passive (7) and two detractors (3 and 6). So its NPS is (1 − 2) ÷ 4 × 100 = -25. Change "North" to "South" and you get 75: three promoters, one passive and no detractors.

Put the region names in a column and point the formula at those cells, so one formula fills the whole table. The COUNTIFS function guide explains how each range and criteria pair works.

How to report a change in NPS#

Report a change in NPS in points, not as a percent change. If last quarter's score was 18 and this quarter's is 25, NPS rose by 7 points:

=F2-F3

A percent change formula would say (25 − 18) ÷ 18 = 38.9%. That is mathematically correct but misleading, because NPS is already built from percentages. It also breaks when the old score is 0 or negative. Use a percent change for things like revenue, and a points change for scores. The guide to percentage formulas shows when each one fits.

How to keep the NPS formula up to date#

Turn the responses into an Excel table so new answers are included automatically. Click any cell in the data and press Ctrl+T (Mac: ⌘T), tick My table has headers and click OK. Then rename the table on the Table Design tab, for example to Responses.

Now write the formula with the column name instead of a fixed range:

=(COUNTIF(Responses[Score],">=9")-COUNTIF(Responses[Score],"<=6"))/COUNT(Responses[Score])*100

When you paste new responses below the table, it grows to include them and the score updates. With a fixed range like C2:C9, row 10 would be left out without any warning, which is one of the easiest ways to report the wrong number.

Common NPS mistakes in Excel#

These slips change the score without any error message:

1. Dividing by promoters plus detractors only. Passives belong in the total. Leaving them out makes the score look more extreme.
2. Using COUNTA instead of COUNT. COUNTA also counts text such as "N/A" or "skipped", which lowers the score.
3. Wrong boundaries. A 6 is a detractor and a 7 is a passive. Writing "<6" instead of "<=6" quietly leaves out every 6.
4. Scores stored as text. Imported survey data sometimes stores numbers as text. COUNTIF then misses them. Convert them with VALUE first.
5. Reading too much into a small sample. With eight responses, one person changing their answer moves the score by 12.5 points. Show the number of responses next to the score.

For the wider survey workflow, from cleaning answers to PivotTables, see how to analyse survey results in Excel. If you work in a support role, the guide to Excel skills for customer service shows where NPS fits among the other reports you'll build.

Practise it#

Try the exercise on this page to build the full NPS formula yourself. Then practise COUNTIF in a live spreadsheet to get fast with criteria like ">=9", and practise percentages and % change so you know when a change should be shown in points and when in percent.