Calculation and an interactive chart for Net Promoter Score® survey results in Microsoft Excel

The Net Promoter Score® is a popular and easy to understand market research metric based on evaluating responses to a single survey question of how likely the respondent would be to recommend the company/brand/product/employer etc. to someone else, e.g. relative, colleague, or friend.
This question can also be accompanied by an open-ended “why?” or follow-up questions to determine the drivers of the response. The concept of the Net Promoter Score was published by Fred Reichheld in his article “The One Number You Need to Grow” in the Harvard Business Review from December 2003. He argued that a single survey question can serve as a predictor of growth and that question is not about customer satisfaction or loyalty, but rather about the willingness to recommend the product to someone else. The survey responses range on a scale from 0 to 10. The respondents are grouped according to their responses in one of three groups. The respondents with ratings of 9 or 10 are the “Promoters” and they can help the surveyed company/brand/product by positive word-of-mouth. Respondents with ratings of 7 or 8 are “Passives”. They neither hurt, nor help. Respondents with ratings of 6 and below are the “Detractors” and they can cause harm by negative word-of-mouth.
The Net Promoter Score is calculated by subtracting the percentage of Detractors from the percentage of Promoters.
Net Promoter Score = %Promoters - %Detractors
The Net Promoter Score is usually shown as an integer, although it is a difference of percentages and it ranges per definition from -100 to 100.

In this video you will see how to calculate the Net Promoter Score in Microsoft Excel using a pivot table with calculated fields and how to make an interactive visualization in the form of a butterfly chart with a special effect made using Visual Basic for Applications.

00:00 Introduction
00:15 Calculation of Net Promoter Score
01:22 Visualizing the NPS with an interactive butterfly chart
02:18 Making the pivot table
03:00 Adding a calculated field for percentage of Promoters
03:38 Adding a calculated field for percentage of Detractors
04:38 Adding a calculated field for the Net Promoter Score
04:57 Adding a dummy column for centering the data labels
05:09 Formatting the pivot table
06:16 Making the butterfly chart for visualizing the NPS
13:23 Adding a cell with the target value to the sheet
13:38 Macro for dynamically changing colors of the data labels
16:38 Testing the macro
17:34 Adapting the code of the macro to your own file

For more contents like this, please subscribe to my channel.
#MsExcel #ExcelTips #VBA

Net Promoter®, NPS®, NPS Prism®, and the NPS-related emoticons are registered trademarks of Bain & Company, Inc., NICE Systems, Inc., and Fred Reichheld. Net Promoter ScoreSM and Net Promoter SystemSM are service marks of Bain & Company, Inc., NICE Systems, Inc., and Fred Reichheld.

The chart itself, used as the visualization of the Net Promoter Score® in this video, is not subject to the above copyright, because it was developed by me.

Screenshots used with permission from Microsoft.

Some images on the thumbnail of this video are AI-generated using Canva.com.

Link to the Excel-file used in the video:
https://drive.google.com/file/d/1AzxE...