Home » Calculating Net Promoter Score with Microsoft Excel

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:

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.)

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:

DaveNice, 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.

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!

Mike says:

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.

Ava Firth says:

Thanks David! Very helpful!! My company uses NPS and has a behind-the-scenes compiler, but Im trying to do som subgroup analysis and your formula has saved me hours of headaches!! Yay!!

Will says:

Thanks for this post. I realize it was posted several years ago now but I’m hoping you’re still around 🙂

I’m working on my company’s first NPS score (in Excel) can you tell me how to use the formula above and add the ability to do subtotals so that I can filter out particular data and see what the NPS score is within a subset?

swedige says:

Quick search for an NPS excel formula and look at what I come across. Thanks for the assist, mitz.

James Wilson says:

Great sheet David, is it also possible to calculate a rolling 6 month score using DATE formula? Thanks, James

Interesting question. Off the top of my head, one way you could accomplish that would be by converting =countif to =countifs and nesting some sort of date lookup. Here’s a really unsophisticated example that assumes that your survey date is in Column C.

Let me break that down so that you can see what I am doing just in case you want to use the same concept but make it a bit more elegant.

There are three sets that I am working with – promoters within the date range, detractors within the date range, and total responses within the date range.

To get my promoter set, I’m doing this:

(COUNTIFS(A:A,">8",C:C,">"&TODAY()-180)

I’m using the COUNTIFS function to count all responses that meet both of the following criteria: likelihood to recommend greater than 8, (which is the A:A,">8" part of the formula), and newer than 180 days old (which is the C:C,">"&TODAY()-180) part of the formula)

I’m doing the exact same thing for detractors with COUNTIFS(A:A,""&TODAY()-180). Finally, I’m taking the difference between my promoters and detractors, and dividing it by the total number of responses within that same date range with the /COUNTIF(C:C,">"&TODAY()-180) at the end.

Hope that makes sense. Like I said, I’m sure this can be optimized (particularly, the way I am calculating the date range is really unsophisticated), but that’s my top of head solution to getting a rolling 6 month NPS in Excel.

Jessica Winczner says:

Hi Dave!

This is so awesome. Saving me from pulling my hair out. 🙂 Question for you. How would I take the information from the “likelihood to refer” column from two separate sheets and combine on another sheet for reporting?

Denise says:

Awsome, thank you for this!

Jeroen Geertsen says:

Nice formula! Many thanks. I want to use this formula to calculate the NPS per week. But how do I add the week number to this formula? So that the formula first checks the week number and then calculates the nps of that week?

DaveNice, 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.

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!

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.

Thanks David! Very helpful!! My company uses NPS and has a behind-the-scenes compiler, but Im trying to do som subgroup analysis and your formula has saved me hours of headaches!! Yay!!

Thanks for this post. I realize it was posted several years ago now but I’m hoping you’re still around 🙂

I’m working on my company’s first NPS score (in Excel) can you tell me how to use the formula above and add the ability to do subtotals so that I can filter out particular data and see what the NPS score is within a subset?

Quick search for an NPS excel formula and look at what I come across. Thanks for the assist, mitz.

Great sheet David, is it also possible to calculate a rolling 6 month score using DATE formula? Thanks, James

@James Wilson:

Interesting question. Off the top of my head, one way you could accomplish that would be by converting

`=countif`

to`=countifs`

and nesting some sort of date lookup. Here’s a really unsophisticated example that assumes that your survey date is in Column C.`=(COUNTIFS(A:A,">8",C:C,">"&TODAY()-180)-COUNTIFS(A:A,""&TODAY()-180))/COUNTIF(C:C,">"&TODAY()-180)`

Let me break that down so that you can see what I am doing just in case you want to use the same concept but make it a bit more elegant.

There are three sets that I am working with – promoters within the date range, detractors within the date range, and total responses within the date range.

To get my promoter set, I’m doing this:

`(COUNTIFS(A:A,">8",C:C,">"&TODAY()-180)`

I’m using the

`COUNTIFS`

function to count all responses that meet both of the following criteria: likelihood to recommend greater than 8, (which is the`A:A,">8"`

part of the formula), and newer than 180 days old (which is the`C:C,">"&TODAY()-180)`

part of the formula)I’m doing the exact same thing for detractors with

`COUNTIFS(A:A,""&TODAY()-180)`

. Finally, I’m taking the difference between my promoters and detractors, and dividing it by the total number of responses within that same date range with the`/COUNTIF(C:C,">"&TODAY()-180)`

at the end.Hope that makes sense. Like I said, I’m sure this can be optimized (particularly, the way I am calculating the date range is really unsophisticated), but that’s my top of head solution to getting a rolling 6 month NPS in Excel.

Hi Dave!

This is so awesome. Saving me from pulling my hair out. 🙂 Question for you. How would I take the information from the “likelihood to refer” column from two separate sheets and combine on another sheet for reporting?

Awsome, thank you for this!

Nice formula! Many thanks. I want to use this formula to calculate the NPS per week. But how do I add the week number to this formula? So that the formula first checks the week number and then calculates the nps of that week?

Thanks!

Thank you. A simple and elegant formula