Do you want to calculate NPS in Excel?
Over the years, the Net Promoter Score (NPS) has become incredibly popular. The metric helps measure customer loyalty in a matter of seconds.
Here’s the simple question that helps you measure the loyalty of your customers:
‘On a scale of 0-10, how likely are you to recommend our product to your family/friends?’
All your respondents have to do is pick a score!
But the metric itself isn’t easy to calculate. Net Promoter Score calculation can be done in multiple ways.
Ultimately the overall net promoter score is what provides you insights and guidance to take appropriate steps to fortify your company.
While NPS calculation can be done in Microsoft Excel or Google Sheets, you can, however, automate the same using SurveySparrow’s NPS software. You just run the survey, we do the rest. To automate your NPS surveys and calculation, you can sign up for FREE on SurveySparrow.
14-Day Free Trial • No Credit Card Required • No Strings Attached
If you still prefer excel, 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 an excel sheet. In other words, we will be helping you construct your own Net Promoter Score calculator in Excel.
The Formula to Calculate NPS in Excel/Google Sheets
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
Net Promoter Score Categories
Let’s first explore the categories of respondents pertaining to the Net Promoter Score.
- Promoters: The respondents who scored you a 9 or 10 come under the promoters category. 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 is extremely unhappy with your brand, and you risk losing them to the competition.
Designing a Net Promoter Score Calculator in Excel
Now that we know the categories and the formula let’s explore the method to calculate NPS in excel.
The COUNTIF function is by far one of the easiest and most 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,”<=6”)
- 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:
As I mentioned before, you can paste this formula into 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 up 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:
- First, make a copy of our NPS spreadsheet template or simply download it as a .XLS file.
- Now, export your NPS survey responses into a .CSV or .XLS file.
- 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 will be displayed in seconds to get your final score.
Net Promoter, Net Promoter System, Net Promoter Score, NPS, and the NPS-related emoticons are registered trademarks of Bain & Company, Inc., Fred Reichheld, and Satmetrix Systems, Inc.