How to add the pivot table grand total to a dynamic chart using as example a chart for NPS®
Have you ever encountered a situation when you needed to include the pivot table grand total in a Microsoft Excel chart, but pivot charts don't display it?
In this video you will see a workaround, how to display the pivot table grand total in a normal dynamic chart and still utilize the flexibility of a pivot table. As an example I will use a chart showing Net Promoter Score® for several brands of an example company.
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.
The solution is based on using dynamic named ranges in the chart. The logic behind it is, that formulas can be used to get dynamic ranges (also values in cells of a pivot table), but they cannot be used directly as chart references.
As a bonus, I also included a special effect of data labels changing colors using Visual Basic for Applications depending on meeting a target specified by the user.
00:00 Introduction
00:30 What Net Promoter Score® is
01:02 Reason to show grand total in a chart
02:34 Making the pivot table
06:46 Creating dynamic named ranges for each column of the pivot table
10:00 Adding the named ranges as series to the chart
12:12 Formatting the chart
13:45 Using a named range for dynamic data labels
14:37 Making a dynamic chart title
15:25 Custom formatting the value axis
17:14 Adding a slicer to the pivot table and sorting
18:10 Special effect (bonus content)
18:21 Adding a cell with target value
18:33 Macro for changing colors of the data labels
21:40 Adapting the code 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.
The link to the file used in the video:
https://drive.google.com/file/d/114ka...