Customer Loyalty and Employee Engagement
Calculating Net Promoter Score with Microsoft Excel
If your Microsoft Excel kung-fu is a little rusty, calculating your Net Promoter Score can seem intimidating. But worry not … it’s a lot easier than you might think!
Let’s say you have an Excel spreadsheet containing all of your survey responses. Each row in your spreadsheet contains a unique response. To make things easy, let’s assume that the answer to the ‘likelihood to recommend’ question is contained in Column A. Your spreadsheet should look a little bit like this example:
Sample Spreadsheet
To calculate your Net Promoter Score, simply paste the following formula into any cell (one caveat: you cannot paste this formula into the same column that contains the answer to your ‘likelihood to recommend’ question or else you will get a circular reference error.)
=ROUNDUP((100*((COUNTIF(A:A,">8")-COUNTIF(A:A,"<7"))/COUNT(A:A))),0)
NPS formula is pasted into cell B&
If the answer to your ‘likelihood to recommend’ question appears in a different column, simply change all instances of A:A the formula above to reference the appropriate column name. So, for example, if the answer to your ‘likelihood to recommend’ question is in Column E, replace all three instances of A:A with E:E as illustrated below:
=ROUNDUP((100*((COUNTIF(E:E,">8")-COUNTIF(E:E,"<7"))/COUNT(E:E))),0)
About the author
David Mitzenmacher is passionate about helping companies create a competitive advantage through customer loyalty and employee engagement. He is currently Chief Customer Officer at Volusion where he helps over 30,000 entrepreneurs succeed online. He was previously the Director of Customer Experience at Rackspace, and held similar leadership roles at a variety of high-growth companies.
| Print article | This entry was posted by David Mitzenmacher on June 9, 2010 at 8:58 am, and is filed under Net Promoter. Follow any responses to this post through RSS 2.0. You can leave a response or trackback from your own site. |
No trackbacks yet.
7,960 Footsteps: A Measure of Loyalty
about 7 hours ago - No comments
Customer experience professionals look at loyalty using a broad assortment of metrics. We measure Net Promoter Score, Retention Rate, WOM Mentions, Share of Wallet, Customer Lifetime Value, and countless other indicators that help us to better understand the level of loyalty our customers have towards our companies. But what about counting footsteps? I recently attended More >
Net Promoter: The Right Tool for the Job
about 1 week ago - No comments
This past weekend I was lucky enough to have a few spare moments to spend in my garage workshop. I spend most of my time during the week working on strategic initiatives, so it’s a nice change of pace to run a hand plane across a piece of wood and see immediate, tangible results. As More >
The Net Promoter System on a Napkin
about 2 weeks ago - 3 comments
Therefore, since brevity is the soul of wit, And tediousness the limbs and outward flourishes, I will be brief – William Shakespeare, Hamlet If brevity is the soul of wit, then simplicity is the soul of inspiring others. It has been my experience that creating a customer-centric revolution within an organization requires the ability to More >
Net Promoter: Are You Playing Not to Lose?
about 3 weeks ago - No comments
Many Net Promoter programs “play not to lose” by only focusing on decreasing Detractors. You can amass and energize an army of Promoters by including Promoters in your closed loop, creating special benefits for Promoters, sparking referrals, and performing Promoter-focused analytic.
Elements of a Successful Net Promoter Survey Invitation
about 7 months ago - No comments
One of the keys to success for your Net Promoter Score program is generating a high response rate. Unlike many other customer feedback frameworks, Net Promoter is designed to emulate a census, with target response rates of 50% of more. The are many factors that contribute to your overall response rate, one of which is More >
3 Easy Steps to Calculating Net Promoter Score
about 9 months ago - 3 comments
A simple illustration that explains how to calculate your Net Promoter Score.
What is Net Promoter?
about 1 year ago - 4 comments
What is Net Promoter? Net Promoter is a methodology for measuring customer loyalty. It was developed jointly by Fred Reichheld, Bain Consulting and Satmetrix. Net Promoter was first introduced by Reichheld in an HBR article entitled “The One Number You Need to Grow“, and was later the topic of his book “The Ultimate Question“. At More >
A Tale of Bad Profits
about 1 year ago - 1 comment
In his book The Ultimate Question: Driving Good Profits and True Growth, Fred Reichheld introduced the concept of Bad Profits as such: Too many companies are addicted to bad profits—profits that come at customers’ expense and drain the value out of customer relationships. Whenever a customer feels misled, mistreated, ignored, or coerced, then profits from More >
How Marriott made me a rabid Promoter for the price of a bottle of water
about 1 year ago - 4 comments
I am, as they say in customer loyalty circles, a Promoter of the Marriott chain of hotels. Last year alone I spent over 60 nights in Marriott hotels, enough to earn my gold status for the next two years. I’ve referred countless friends and colleagues to various Marriott locations. I even set up a corporate More >
about 1 year ago
Dave
Nice, elegant solution, and well done for putting this on your blog – I have been meaning to put my Excel version on for a while, and you beat me to it! It's an excellent no-cost way of getting started with Net Promoter Score, and how we started our NPS journey. Trouble is when your spreadsheet gets bigger, and bigger, and you started to try and organise comments, then it becomes somewhat unwieldy. But by the time you are there, you are probably already convinced of the value of Net Promoter Score.
about 1 year ago
Adam,
Thanks for the kind words!
I completely agree with you – running a large Net Promoter Score program from an Excel spreadsheet eventually becomes impossible. In my experience, many companies follow a similar path regarding the systems that power their NPS program.
Phase 1: A suite of non-integrated, mostly free tools. Companies at this stage generally use a free tool like SurveyMonkey for their survey, Microsoft Excel for reporting and analysis, and their email client for Close-the-Loop triggers.
Phase 2: A purpose-built, standalone tool. Companies at this stage will use NPS software from companies like SatMetrix, CustomerSat, CustomerGauge, Vovici, MarketTools, etc.
Phase 3: A system that is integrated with the core of the business. Companies at this stage will use purpose-built NPS software like in stage two, but now those tools are integrated with the systems that the business already uses. Examples would be ETL integration with the enterprise datawarehouse for reporting, CRM integration for Close-the-Loop activity management, and more complex analytical tools like SPSS, Minitab, etc.
Regardless of what technology is used, the important thing is that companies start asking, understanding, acting upon and responding to feedback from their customers.
Hopefully this post helps companies who are new to NPS get started just a little bit faster!
about 11 months ago
This is fantastic, thank you.
My spreadsheet has filters but this formula only works for the whole data range. What do I need to do to the formula to work for the filtered results? Any ideas would be much appreciated.