How to collate Net Promoter Scores in Excel
iStock_Customer satisfaction feedback_NiroDesign
How to collate Net Promoter Scores in Excel

Net promoters: How to manage feedback in Excel

4th Aug 2017
Marketing manager
Share this content

Satisfaction surveys are a great way to gather feedback. In response to a user query Valme Claro presents a basic tutorial about how to manage that data so you can start using it. Further articles in this series will move on to using the data to improve business decisions.

Do you ask for client feedback, asked AccountingWEB member RLK recently. While small practitioners may feel they’re close enough to their clients not to need to badger them with formal surveys, audit manager mike_uk_1983 wrote about where simple surveys can be effective: “You may think you are offering a great service but if it’s not the service the clients want, then you need to know so you can focus your energies in the right direction. You don't want to become complacent.”

Or as ICAEW IT Faculty technical manager Kirstin Gillon put it in her paper on AI and the future of accountancy, “Human judgement is often just a substitute for lack of data.”

One of the most common tools used to summarise satisfaction survey data across multiple industries is the Net Promoter Score (NPS) devised by Fred Reicheld. The strength of NPS is its simplicity. The formula is based on the assumption that the most reliable measure of customer satisfaction is how likely a customer is to recommend your business.

You may already be familiar with the concept from customer surveys that contain the key query, “How likely is it that you would recommend this company to a friend or colleague?”

In response to that question, participants answer on a scale ranging from 0 (very unlikely) to 10 (very likely). Then they are categorised as detractors, passives or promoters according to this structure:

  • 0-6: Detractor - the customer is not happy with the service provided and is likely to spread bad messages about the company
  • 7-8: Passive - the customer is satisfied but not particularly enthusiastic about the service
  • 9-10: Promoter - the customer is really happy about the service received and wants to spread the word to other colleagues.

The score is calculated by subtracting the percentage of detractors from the percentage of promoters. NPS can range from −100, which means that everybody is a detractor, to +100 if everybody is a promoter. For instance, if there were 100 respondents, 62 of which were promoters, 16 detractors and 22 passives, the NPS would be 46%. Anything above zero counts as a good NPS score those higher than +50 are considered to be excellent.

Multiple research projects – including AccountingWEB’s Practice Excellence Awards, have validated the underlying hypothesis that organisations that achieve high NPS scores are more likely to grow profitably.

How Excel can help

Having committed to the NPS route and collected responses to your survey, Excel can automate the NPS calculation and present the results.

The COUNTIF function can count the number of promoters, neutrals and detractors and give you summary totals that can be converted to percentages.

Step 1: Copy all the responses that you have received for your survey into one column:

Step 1: Collect NPS survey responses in an Excel column

If, for instance, you copied them into column M like on this example, the formulae would look like this:

  • Promoters: =COUNTIF(M:M,”>=9″).
  • Detractors: =COUNTIF(M:M,”<=6″)
  • Neutrals: =COUNTIF(M:M,”=7″) +COUNTIF(M:M,”=8″)

Step 2: Once the numbers of promoters, neutrals and detractors have been counted, we need to convert the numerical values into percentages in column D. We can do this by dividing the numbers of promoters by the total responses using the formula =(C5/C8) for the promoters and formatting the result in D5 as a percentage by left-clicking, choosing the Format Cell option, selecting Percentage from the menu of number formats and reducing the decimal place count to 0. Repeat the same process for the detractors and neutrals:

calculate percentage

Step 3: Once the three numbers have been converted into percentages, we can add the equation for NPS:

NPS  = (number of promoters – number of detractors) / total number of responses

The formula as shown in cell C10 of the spreadsheet below would be =(C5-C7)/C8

Then display the result as a percentage to get the NPS value.


Step 4: Now go to the Insert menu and select the Chart option (or press F11) to pick a chart. Here we have gone for a pie chart to illustrate the split of promoters, neutrals and detractors:

Display NPS results as a pie chart in Excel

NPS can also be calculated for more than one company, which can offer a company a useful overview of the market and how it compares to its competitors.

In order to compare the NPS result of a few companies in Excel, the first step would be to arrange the results of every company in a different column:

Comparing multiple NPS results in Excel

Now we can arrange the results from better to worse NPS and even add a more visual colour gradient to see the results more clearly. In order to do this, we can select all the data as in the previous image, then click on custom sort under sort and filter. Then, we can click on sort left to right:

Sort multiple NPS results

And sort by last row and order largest to smallest:

Adjust NPS sort order

The companies will now be ordered from best NPS (left) to worst NPS (right). In order to add a colour gradient we can use the conditional formatting tool applying a new rule:

Use conditional formatting to apply a colour gradient to NPS scores

Now we can change the type of value used for the rule description to percent (both under minimum and maximum), the value from 0 to 100, and assign any preferred colours to both extremes:

Conditional formatting colour scale

last step

As an additional step, the results for several companies can be presented in charts comparing the values. Explore the options with comparative pie charts, or bar charts that present a straight NPS comparison.

The next article in this series will look at how you can collect, analyse and present qualitative survey data data within Excel using text analysis.

Replies (0)

Please login or register to join the discussion.

There are currently no replies, be the first to post a reply.