Net Promoter Score is one of the most watched customer satisfaction metrics in SaaS and services businesses. Calculating it correctly in Google Sheets is straightforward, but building a tracker that shows trends, segment breakdowns, and response quality takes a bit more structure. Here's how to do it right.
The NPS Formula
NPS ranges from -100 to 100. It's calculated as: % Promoters - % Detractors.
Promoters: respondents who gave a score of 9 or 10. Passives: respondents who gave a score of 7 or 8. Detractors: respondents who gave a score of 0 to 6.
Passives don't count in the NPS calculation — they don't help or hurt.
Your Response Log
One row per survey response. Columns: respondent name or ID, customer segment or plan, response date, NPS score (0-10), and verbatim comment.
Calculating NPS
Total respondents:
=COUNTA(D2:D1000)
Promoters:
=COUNTIF(D2:D1000, ">=9")
Detractors:
=COUNTIF(D2:D1000, "<=6")
NPS:
=(Promoters-Detractors)/TotalRespondents
Format as a number (not percentage) — NPS is reported as a whole number between -100 and 100.
NPS by Segment
Use COUNTIFS to calculate NPS for each customer segment separately:
=COUNTIFS(C:C, "Enterprise", D:D, ">=9")
Do this for promoters and detractors per segment. Calculate segment NPS: (segment promoters - segment detractors) / segment total. This shows whether enterprise customers feel differently than SMB customers — a common pattern.
Trend Over Time
Group responses by month and calculate NPS for each month. Plot as a line chart. This is the view that shows whether your product improvements are moving the needle over time.
The Easy Way: Using SheetXAI in Google Sheets
Example 1: You have survey responses already in the spreadsheet.
"I have NPS survey responses on Sheet 1 with customer name, segment, date, and score. Calculate overall NPS, show the promoter/passive/detractor breakdown, calculate NPS by segment, and build a monthly NPS trend chart."
SheetXAI reads your data, calculates NPS at every level, and builds the trend chart.
Example 2: Your NPS data comes from a survey tool.
"Pull NPS responses from our Delighted survey account and build a tracker showing current NPS, segment breakdown, and 6-month trend."
SheetXAI connects to your survey tool and builds the full NPS dashboard.
Try SheetXAI free and see what it builds for you.
Published May 2026. See also: How to Build a Customer Health Dashboard in Google Sheets, How to Analyze Customer Churn in Google Sheets, and Google Sheets AI Guide.
