Sr Content Writer Zonka Feedback
Blogger
Share this content

How to calculate NPS in Excel

7th Jan 2021
Sr Content Writer Zonka Feedback
Blogger
Share this content

Customer perception and satisfaction play a dominant role in the success of your business. Based on how customers perceive your products, services, or brand helps them to decide whether they want to stay or leave your business. It is this reason why businesses spend a lot of time and effort to gauge customers’ perceptions by collecting customer feedback, and the same reason why you should too if you don’t already. Taking customer feedback goes beyond just knowing customer perception and satisfaction. It helps you quickly identify expected customer behaviors and challenges, and helps you in strategizing plans to improve and enhance the customer experience.

If you’re looking at measuring customer satisfaction and also predicting their loyalty and behavior, Net Promoter Score® (NPS), measured through NPS Surveys, is the suited CX Metric for you to use. The NPS question is simple: On a scale of 0-10, how likely are you to recommend our product to your family and friends? It is rated on a scale of 0-10 and based on the response you can understand the customer's likelihood to recommend your business, and in turn know their satisfaction and loyalty towards your business. According to Marketing Week, “increase in Net Promoter scores leads to a doubling of a company’s growth rate on average.”

There are different ways to calculate your Net Promoter Score based on how you conduct the NPS Survey. For instance, if you’re using Feedback Software to conduct NPS Surveys, your NPS score will be calculated automatically in your reports. If you’re doing it manually, you can either use Excel to make your calculations or use a Free Online NPS Calculator. In this article, we will be helping you construct your own Net Promoter Score Calculator in Excel. Before that, we’ll discuss how Net Promoter Score® works. (Click here to directly jump to how NPS is calculated in Excel)

How Net Promoter Score® Works?

According to Bain & Company, the founding company of NPS, the Net Promoter Score® system is a management philosophy that commits to customers and employees alike. To understand the workflow of the NPS Score System, you need to first understand the Net Promoter Score Scale along with the classification of the NPS audiences. 

NPS Score comes from the simple NPS question -"How likely would you be to recommend our products/services to a friend?" Customers range their willingness of recommendation on a scale of 0-10. NPS Survey provides a score scale ranging from 0 to 10. Based on the perceived experience, customers give the score. Based on their responses, customers will be segregated into three categories:

  1. Detractors
  2. Passives 
  3. Promoters

What are Detractors?

Detractors are audiences who give low scores that are between 0 to 6. These customers are unhappy and unsatisfied with the products or services you are providing. Detractors never recommend you further. 75% of consumers stated that they share a negative experience with their friends and colleagues. Detractors need your attention and by following the right strategies, you can turn your detractors into promoters. 

What are Passives?

Passives come in between the detractors and promoters and score you moderately, 7-8. As the name suggests, they are passives in nature i.e they neither show too much excitement in your business nor spread bad word-of-mouth for your business. Following up with passives can help you to improve their experience and increase the number of happy customers.

What are Promoters?

Promoters are those customers who are super happy and fully satisfied with the products or services you are providing them. They score their likelihood of recommending you high, around 9-10, and are likely to be more loyal to you. Promoters are willing to advocate for your brand and spread positive word-of-mouth for your brand.

Net Promoter Score® Formula

Net Promoter Score can be calculated using a simple NPS formula where we subtract the total percentage of Detractors from the percentage of Promoters to determine your overall Net Promoter Score. NPS Score ranges from -100 to 100.  

Net Promoter Score = % Promoters - % Detractors

How to Calculate NPS in Excel or Google Sheet?

If you have the NPS Scores handy with you and would like to calculate NPS yourself, then you can set up a quick NPS Calculator on Excel. In Excel, you can calculate the NPS Score using the COUNTIF function - the statistical function that counts the number of cells that contain data (NPS Scores).

Creating an NPS Calculator in Excel or Google Sheets

Here are the steps to follow to get your NPS Score in Excel:

Step 1 - Enter all of the survey responses into an Excel spreadsheet by exporting all the data from your NPS Software into an excel sheet.

paste-your-scores-here-nps

 

 

Step 2 - Now segregate all the responses into 3 categories Detractors, Passives, and Promoters using the COUNTIF functionality. 

For example, scores given by respondents are placed in Column A. So, the function for finding the number of responses for:

  • Detractors (0-6): =COUNTIF(A:A,”<=6”)

formula-for-detractors-nps

 

 

  • Passives (7-8): =COUNTIF(A:A,"7")+COUNTIF(A:A,"8")

formula-for-passives-nps

 

 

  • Promoters (9-10): =COUNTIF(A:A,">=9")

formula-for-promoters-nps

 

 

Step 3 - Now add up the total responses from each group and get the percentage by dividing the total number of groups by the total number of survey responses.

total-responses-nps

 

 

Step 4 - Now, get your NPS score by subtracting the percentage total of Detractors from the percentage total of Promoters or you can also use the COUNTIF functionality formula to find your net promoter score:

ROUNDUP((100*((COUNTIF(B:B,”>8″)-COUNTIF(B:B,”<7″))/COUNT(B:B))),0)

nps-score-calculation-formula

 

 

Conclusion

Start determining your NPS Score using NPS Calculator or NPS Software. Net Promoter Score can do wonders for your business if it is used effectively. NPS drives data that helps you to understand the perceptions and purchase behavior of your customers. Based on NPS feedback you can upgrade your products or services to modify your customer experience accordingly.

Replies (0)

Please login or register to join the discussion.

There are currently no replies, be the first to post a reply.