‘On a scale of 0-10, how likely are you to recommend our product to your family/friends?’

Who would think that a simple question like this could help businesses around the world to take giant leaps towards growth? But, surprisingly or not, it has. Yes, I am talking about the Net Promoter score (NPS).

Over the years NPS has become incredibly popular. The metric helps measure customer loyalty in a matter of seconds. NPS surveys, when conducted helps you garner better response rates too. The respondents need not spend much time on such surveys, all they have to do is pick a score!

But the metric that is apparently very simple can pose a threat when it comes to calculation. All your hard work can go down the drains if you are not adept at net Promoter score calculation. Ultimately the overall net promoter score is what that provides you insights and guidance to take appropriate steps to fortify your company. Your calculation needs to be intact. There ought to be no room for errors because your NPS influences the future of your enterprise.

There are various ways to calculate your net promoter score. In this article, we will be delving into how to calculate NPS in Excel sheet. In other words, we will be helping you construct your own Net Promoter Score calculator in Excel.

Net Promoter Score Categories

Before starting off, let’s first explore the categories of respondents pertaining to Net Promoter Score.

Promoters: The respondents who have scored you a 9 or 10 comes under the category of promoters. They are very much happy with your brand and can help promote it through positive word of mouth.

Passives: The customers who have answered a 7 or 8 are considered passives. They hold a neutral stand. They aren’t really happy with your product nor are they disappointed but you can’t expect them to be loyal to your brand. A better product can lure them towards your competitors.

Detractors: Customers falling under the category of detractors will have scored you in the range of 0-6. This group of customers are extremely unhappy with your brand and you are at the risk of losing them to the competition.

The formula for Calculating NPS

Basically, the following steps are approached during net promoter score calculation.

• Find the total number of respondents.
• Find the percentage of Promoters – Divide the number of promoters by the total number of respondents and then multiply by 100.
• Find the percentage of Detractors – Apply the same step mentioned above with numbers pertaining to the detractors.
• So, the formula for NPS = % Promoters – % Detractors

Designing a Net Promoter Score Calculator in Excel

Now that we know the categories and formula, let’s explore the method to calculate NPS in excel.
The COUNTIF function is by far one of the easiest and effective ways to calculate NPS in excel. Essentially, the function delivers the count of cells meeting a certain clause or criteria given by you. Here, we will be using the COUNTIF function to segregate and find the number of respondents belonging to each of the categories being, Promoters, Detractors and Passives.

• Export the data from your NPS survey into a .CSV or .XLS file.
• Now copy the data from the respective file and paste it into an excel sheet.
• When that much is done, you will have to find the count of responses in each category. Let’s assume the scores given by respondents are contained in Column B. So, the function for finding the number of responses for:
  • Promoters: COUNTIF (B: B,”>=9”)
  • Detractors: COUNTIF (B: B,”
  • Passives: COUNTIF (B: B,”=7”) + COUNTIF (B: B,”=8”)

You can feed in the function to any of the cells, but here’s a warning; do not paste the function into the same column that contains the scores to your NPS survey (in this case column B).

• Since you have received the total number of respondents in each category, you can find the percentage of each category and then apply the NPS formula in excel i.e % promoters – % detractors.
• If the data set is quite large, then you may want to use a simpler method to calculate NPS in excel. Here’s a single excel formula to find your net promoter score:

ROUNDUP((100*((COUNTIF(B:B,”>8″)-COUNTIF(B:B,”

As I mentioned before, you can paste this formula in any cell of your excel sheet except in the column that contains your NPS responses. Use the formula and you can calculate and find your overall net promoter score in excel in a jiffy! Indeed a DIY Net Promoter Score calculator in excel! Now you may have realized that though it sounds bland and daunting at the same time, calculating NPS in excel is not really a task!

Easy, easier and easiest, which one would you go for? Of course, the superlative, right? Well, we have also come with the easiest method to calculate NPS in excel. Guess what, we have a Net Promoter Score excel template all set for you. With our excel template, you literally don’t have much to do. All you have to do is paste your data into the NPS calculator excel template and then sit back and relax!

Here’s how you can find your scores in our NPS excel template:

1. Firstly, make a copy of our NPS spreadsheet template or simply download as a  .XLS file.
2. Now, export your NPS survey responses into a .CSV or .XLS file.
3. Copy data from your file. Paste it into the section,’Responses to your NPS Survey’ of our Net Promoter Score Calculation Template.

And Voila! The number of respondents in each category to getting your final score, everything will be displayed in seconds.

The post How to Calculate Net Promoter Score in Excel appeared first on SurveySparrow.