How to Calculate Net Promoter Score in Excel/Google Sheets [with Download]

How to Calculate Net Promoter Score in Excel/Google Sheets [with Download]

Download the NPS Calculation Spreadsheet

In this post we will make ourselves a simple but effective Net Promoter Score calculator in Excel and Google Sheets using the COUNTIF function.

Before we do that let’s have a quick review of the formula for NPS.

Calculating Net Promoter Score in Excel and Google Sheets

NPS is calculated by using the responses to the following customer survey question:

NPS Question: How likely are your to recommend Company A to a friend to colleague, where 0 is very unlikely and 10 is very likely.
The standard “would recommend” question used to derive Net Promoter Score

You will notice the response is an integer (whole number) between 0 and 10 – in the standard questions no fractional responses are allowed, e.g. 9.5.

We then define three groups of respondents:

  • Promoters: are responses of 9 or 10
  • Neutrals: are responses of 7 or 8; and
  • Detractors are responses of 0 to 6.

Then the formula for NPS is:

The Net Promoter Score calculation

It will make our spreadsheet easier to create if we apply a little algebra and re-write the equation as:

The simplified Net Promoter Score equation

This score, although it looks like a percentage, should be referred to as a number.

You can see that NPS ranges all the way from:

  • -100: where you have 100% Detractors to
  • +100: where you have 100% Promoters
The full Net Promoter Score range
The full NPS range

Building an NPS Calculator in Excel or Google Sheets

Now we have the basic equation we can calculate the score in a couple of different ways in Excel and Google Sheets.

The easiest way to calculate it is to use the Excel COUNTIF function or the, basically identical, Google Sheets COUNTIF version:.

This function allows you to count the number of times the cell contents meets a certain criteria.

In this case we’ll count the responses in each of the three categories we are interested in: Detractors, Neutrals and Promoters.

For Promoters we’re looking for 9’s or 10’s and so the function looks like this: =COUNTIF(R:R,”>=9″)

For Detractors we’re looking for 0’s up to 6’s so the formula is : =COUNTIF(R:R,”<=6″)

And lastly for Neutrals we’re looking for just 7’s and 8’s: =COUNTIF(R:R,”=7″) +COUNTIF(R:R,”=8″)

In this case R:R is the whole column of responses that you have received for your survey.

The nice thing about using this function is that you can just copy and paste your scores into the column and Excel will count them for you.

Now that you have the number of Promoters, Neutrals and Detractors you can add the equation for NPS to the spreadsheet.

MS Excel and Google Sheets NPS formula: =(Promoters – Detractors)/Responses * 100
MS Excel and Google Sheets NPS formula

This is how the calculation looks in the spreadsheet itself. Remember to include the “(” and “)” or the calculation will be wrong.

Formula for Net Promoter Score in Excel and Google Sheets

Of course, you don’t have to do these two calculations separately. You can combine them into the one formula. Here, again, R:R is the column where you have your scores.

MS Excel and Google Sheets NPS compact formula:=COUNTIF(R:R,8")-COUNTIF(R:R,"<7"))/COUNT(R:R) * 100" loading="lazy" decoding="async" >
MS Excel and Google Sheets Compact NPS formula
I’ve created a free Excel spreadsheet with the NPS calculation already included. Download it here.