How to Calculate NPS Score in Excel (2 Methods)

NPS stands for Net Promoter Score. It usually shows us the ratio of the difference between the total number of the most satisfactory data and the unsatisfactory data with respect to the total number of data. We can get such a result using Microsoft Excel easily. In this article, we are going to demonstrate two handy methods to calculate the NPS score in Excel. If you are also curious about it, download our practice workbook and follow us.

Overview of NPS Score

Promoters: Entities that are extremely satisfied and content with the goods or services they are offering are considered to be promoters. Promoters are ready to speak out on your behalf and encourage others to talk favorably about your entities.
Passives: The average of your entities, passives sit between promoters and dissenters. As their name implies, they are neutral or passive individuals who don’t exhibit excessive excitement or negatively affect others.
Detractors: Those organizations that give you poor ratings are considered detractors. All people are dissatisfied and unhappy with this data. Detractors will never further endorse you.

The mathematical expression of the NPS score is:

Moreover, you can also re-write the expression as shown below:

How to Calculate NPS Score in Excel: 2 Quick Ways

To demonstrate the approach, we consider the mathematics examination marks of 50 students in column B. Moreover, we mention our NPS score criteria in column C. We consider 80-100 as the Promoters’ range, 50-70 as the Passive or Neutral range, and 0-40 as the Detractors’ range.

1. Using Conventional Formula

In this approach, we will use the conventional formula to calculate the NPS score in Excel. The steps of this process are given below:

📌 Steps:

First of all, we have to calculate the No. of Students per Criteria. For that, select cell D5.
Now, write down the following formula into the cell using the COUNTIF function.

=COUNTIF(B:B,"=0")

Press Enter.

You will get the number of students belonging to this criteria.
Similarly, write down a similar type of formula for each criterion.

Then, in cell D16, we are going to write down a formula using the SUM function to get the total number of students.

=SUM(D5:D15)

Press Enter.
You can see that the sum of all the criteria results is 50. Hence, we can say that we have categorized all of the students.

After that, we will estimate the Percentage of Students per Criteria. Write down the formula into cell E5. Make sure that you input the Absolute Cell Reference for cell D16.

=D5/$D$16

Similarly, press Enter.

Next, drag on the Fill Handle icon to copy the formula up to cell E15.

Additionally, you can also apply the SUM function in cell E16 to get the summation of this column.

=SUM(E5:E15)

Press the Enter.

Now, we are going to evaluate the three particulars of the NPS score. To calculate the value of the Promoters, write down the following formula into cell F5.

=COUNTIF(B:B,">=80")

Press the Enter key.

Then, to calculate the value of Detractors, write down the following formula into cell H5 and press Enter.

=COUNTIF(B:B,"<=40")

The number of Neutral is the deduction result of the sum of the other two particulars from the total number of students. Write down the following formula into cell G5 to get the value.

=D16-F5-H5

Press Enter.

Moreover, we can calculate their percentage with respect to the total number of students.
For that, write down the following formula in cell F6. Ensure that you input the Absolute Cell Reference for cell D16.

=F5/$D$16

Press the Enter.

Then, drag the Fill Handle icon to your right to copy the formula up to cell H6.

Finally, to calculate the NPS Score, write down the following formula into cell I5.

=((F5-H5)/D16)*100

Press Enter for the last time.

You will get the value of the NPS score.

Thus, we can say that all of our formulas worked perfectly, and we were able to calculate the NPS score in Excel.

💬 Things You Should Know

You can calculate the NPS from the percentage value of the Promoters and Detractors, which are shown in cells F6 and H6. The deduction value of both cells is numerically equal to the NPS score.

2. Combining COUNTIF and COUNT Functions

In this process, we are going to use the COUNTIF and COUNT functions to calculate the NPS score in Excel. The steps of this method are given as follows:

📌 Steps:

First, select cell D5.

Now, write down the following formula in the cell.

=((COUNTIF(B:B,">=80")-COUNTIF(B:B,"<=40"))/COUNT(B:B))*100

Press Enter.

You will get the value of the NPS score.

Finally, we can say that our formula worked successfully, and we were able to calculate the NPS score in Excel.

🔎 Breakdown of the Formula

We are breaking down the formula for cell D5.

👉 COUNTIF(B:B,”>=80″): The COUNTIF function will count the entities that are greater than or equal to 80 in column B. For this cell, the value is 22.

👉 COUNTIF(B:B,”<=40″): Here, the COUNTIF function will count the entities that are less than or equal to 40 in column B. For this cell, the value is 4.

👉 COUNT(B:B): The COUNT function will count the total number of entities. Here, the value is 50.

👉 ((COUNTIF(B:B,”>=80″)-COUNTIF(B:B,”<=40″))/COUNT(B:B))*100: Finally, this formula first deducts the 8 from 22, then divides the result with the result of the COUNT function, and multiples the result with 100. Finally, it will show the NPS score. Here, the result is 36.

Download Practice Workbook

Download this practice workbook for practice while you are reading this article.

Calculate NPS Score.xlsx

Conclusion

That’s the end of this article. I hope that this article will be helpful for you and you will be able to calculate the NPS score in Excel. Please share any further queries or recommendations with us in the comments section below if you have any further questions or recommendations.

<< Go Back to Scoring | Formula List | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!