How to calculate NPS® in Excel?

4.7
How to calculate NPS® in Excel?

Calculating the Net Promoter Score manually has become a tedious and time-consuming task for you? Learn, easy way to calculate NPS using Excel.

Besides the quality of products, services, and competitive pricing, customer experience is also a vital element that connects your customers to your brand or business.

Walker predicts Customer Experience and Satisfaction as the brand differentiator. They added that price and product will be overtaken by customer experience and satisfaction as the key brand differentiators.

A good customer experience not only leads to customer satisfaction but also reduces churn and increases customer retention.

So, if you want to improve the customer experience, satisfaction, and reduce churn, you can do all these things with one single metric that is Net Promoter Score® (NPS).

According to Adobe, “businesses that invested heavily in Net Promoter Score® (NPS) have 1.6x higher brand awareness, 1.9x higher order value, 1.6x higher customer satisfaction, 1.7x higher customer retention.”

Besides the mentioned advantages, collecting and acting on NPS data will also help you to close deals and drive new customers to the business.

What is Net Promoter Score® (NPS)?

Net Promoter Score (NPS) is a leading Customer Experience metric that measures loyalty by gauging the likelihood of customers to recommend a company’s Products, Services, or Brand to their friends or relatives.

NPS ranges from -100 to 100. Along with customer loyalty, NPS also predicts the purchase and referral behavior of your customers.

Over the years, the Net Promoter Score metric has become the gold standard Customer Experience metric that more than two-thirds of the Fortune 1000 list currently use this metric to determine customer loyalty.

Benefits of Measuring the Net Promoter Score®

According to Nielsen, “83% of online respondents say they trust the recommendations of friends and family.” The recommendations and reviews of friends and family have become a dominant element in the journey of customers.

Let’s explore other benefits of measuring the NPS:

1. Measures Customer Loyalty

Whether your customers are loyal to your brand or not depends on many factors like good customer experience, quality-grade products & services, customer support services, etc. So, Net Promoter Score® helps you to know the customers’ perception regarding the above-mentioned factors.

Standard NPS Question - How likely are you to recommend us to a friend or colleague? Helps you to gauge customers willingness to recommend your brands to others. Based on their likelihood of recommending your brand, you can test their loyalty.

2. Works as a Customer Satisfaction Indicator

NPS Survey is known for its versatility. It works as a Customer Satisfaction Indicator if it is used effectively. Various NPS Software ensure real-time customer satisfaction at different touchpoints throughout the customer journey through Transactional NPS Surveys.

Transactional surveys help you to gauge customer satisfaction immediately after the specific interaction or transaction, or after any events like Post-Order, Post Ticket or Call Raised, Post-Delivery, and Post-interaction.

3. Finds Detractors, Passives, and Promoters

NPS Survey helps you to know the nature of your customers. Whether they are detractors (unhappy customers), Passives (Neutral customers), or Promoters (Happy Customers).

When customers scale their likelihood of recommending your business on a scale of 1 to 10. If they give 0-6, they are Detractors, if 7-8, they are Passives and if they give 9-10, they are considered to be Promoters.

Now after identifying the nature of your customers, you can quickly follow up with them and solve their queries to improve their experience and turn them into loyal customers.

4. Enables to Follow-up Customers

NPS Survey is divided into two crucial parts:

  • NPS Question - The standard NPS Question - How likely is it that you would recommend [Organization/Product/Service] to a friend or colleague?
  • Follow-up Questions - The reason for the Score - What is the primary reason for your score? So, answers to Follow-up questions help you to know the SWOT (Strength, Weakness, Opportunities, and Threats) associated with your business.

Asking follow-up questions from detractors, passives, and promoters has its significance. When you ask the reason for the score from your Detractors, they provide you with suggestions that contribute to enhancing product development potential along with the customer experience.

Measuring & Calculating the Net Promoter Score

To calculate the Net Promoter Score, you can use the standard NPS Calculation Formula established by Bain & Co. In this formula, you can calculate the Net Promoter Score by subtracting the percentage of detractors from the percentage of promoters, i.e. Net Promoter Score = % of Promoters - % of Detractors.

Now after calculating the NPS check the Net Promoter Score Scale. Let’s understand the Net Promoter Score Scale.

Net Promoter Score

Net Promoter Score Scale

In an NPS Survey, a score scale is provided to the customers where they can rate their likelihood of recommending your brand further. NPS Score Scale ranges from 1 to 10.

Based on the perceived experience, customers give the score. Based on their responses, customers will be segregated into three categories.

Detractors (0-6)

They are considered to be unhappy & unsatisfied customers who give you a score from 0 to 6. Detractors never recommend you as a brand and your product/service to other potential buyers. They are likely to spread bad word of mouth for your brand.

Passives (7-8)

They give you 7 or 8 and are considered to be moderately satisfied customers. They neither promote your brand nor spread any negative word-of-mouth.

Promoters (9-10)

They are happy & very satisfied customers who range their likelihood of recommending your business to 9 or 10. They will not only recommend your brand to others but also advocate your brand by spreading positive word of mouth for your business.

How to Calculate NPS?

There are 3 ways to calculate the Net Promoter Score:

  1. Via NPS Software
  2. Via Online NPS Calculator
  3. Via Excel Sheet

Calculating NPS via NPS Software

You can measure the Net Promoter Score of your business by using the NPS Software or professional NPS Tools. NPS Software helps you to create and distribute NPS surveys across tablets, iPads, and kiosks. You can also send NPS questions via email and SMS surveys or simply embed NPS Surveys on your website.

Calculating NPS via Online NPS Calculator

You can also use the Online NPS Calculator to know the NPS Score of your business. You just need to follow the simple steps:

  1. Sum up all the responses by detractors (those who rated between 0-6) and promoters (those who rated 9-10) separately.
  2. Find the % promoters and %, detractors.
  3. Now subtract the percentage of detractors from the percentage of promoters. You’ll get your Net Promoter Score.
NPS Calculator

Calculating the Net Promoter Score via Excel

Calculating the Net Promoter Score manually is a tedious and time-consuming task. But thanks to Excel that has made NPS Calculation an easy task. You can calculate NPS by using the COUNTIF function.

It is the statistical function that counts the number of cells that contain data (NPS Scores). Calculating the NPS via Excel is not rocket science, with just a few steps you can create your own NPS Calculator in Excel or Google Sheet.

Step 1 - Export or copy-paste the data from the file into an excel sheet.

NPS Calculation

Step 2 - Now separate the responses in each category (detractors, passives, and promoters) and count them. Say, let’s assume the scores given by respondents are contained in Column C. So, the function for finding the number of responses for:

  • Detractors (0-6): =COUNTIF(C:C,”<=6”)
nps software
  • Passives (7-8): =COUNTIF(C:C,"7")+COUNTIF(C:C,"8")
nps calculator
  • Promoters (9-10): =COUNTIF(C:C,">=9")
nps surveys

Step 3 - Now you can find the percentage of each category by using the simple formula:

Number of Responses from (different NPS audience - Detractors, Passives, and Promoters) ÷ Total Number of Respondents x 100.

For Example: Say, you've sent a NPS Survey with the NPS questions and you've received 40 responses from your customers:

10, 7, 5, 9, 9, 9, 6, 4, 9, 8, 9, 9, 9, 10, 10, 10, 4, 5, 5, 7, 7, 10, 10, 10, 5, 6, 10, 10, 9, 8, 10, 10, 8, 7, 9, 9, 9, 10, 9, 7

Now paste the score and arrange them in your Excel sheet.

  • Detractors - 8 responses
  • Passives - 8 responses
  • Promoters - 24 responses

When you calculate the percentages for each group, you get 20%, 20%, and 60% respectively.

In an excel sheet, you can get your NPS Score either by calculating manually or by using the simple Net Promoter Score formula: Net Promoter Score = % of Promoters - % of Detractors

NPS = 60% - 20% = 40

So, Your Net Promoter Score is 40

Note: Net Promoter Score is always shown as just an integer and not a percentage.

nps surveys

You can 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)