How to Create an NPS Chart in Excel

A Net Promoter Score (NPS) gives you a powerful one-number summary of customer loyalty, but seeing that number displayed as a simple metric doesn't always tell the whole story. To truly understand the breakdown of your customer sentiment - and communicate it effectively to your team - you need a clear and informative visual. This guide will walk you through exactly how to calculate your NPS and create a compelling NPS chart directly in Excel.

Graphed

Your AI Data Analyst to Create Live Dashboards

Connect your data sources and let AI build beautiful, real-time dashboards for you in seconds.

Build Your First Dashboard

What is Net Promoter Score (NPS)?

Before building the chart, it's important to have a firm grasp of what NPS represents. The Net Promoter Score is a widely used market research metric based on a single, direct question:

"On a scale of 0 to 10, how likely are you to recommend our company/product/service to a friend or colleague?"

Based on their response, customers are grouped into three categories:

• Detractors (Score 0-6): These are unhappy customers. Not only are they at risk of churning, but they can also damage your brand through negative word-of-mouth.
• Passives (Score 7-8): These customers are satisfied but not necessarily loyal. They’re neutral and susceptible to competitive offers.
• Promoters (Score 9-10): These are your biggest fans. They're enthusiastic, loyal customers who fuel your growth by acting as brand ambassadors.

The final NPS score is calculated by subtracting the percentage of Detractors from the percentage of Promoters.

NPS = % Promoters – % Detractors

This formula results in a score ranging from -100 (if every customer is a Detractor) to +100 (if every customer is a Promoter). Passives are included in the total respondent count, which influences the percentages of the other two groups, but their percentage is not directly used in the final score calculation.

AI for Data Analysis

Crash Course

Graphed

Free PDF Guide

AI for Data Analysis Crash Course

Learn how to get AI to do data analysis for you — the best tools, prompts, and workflows to go from raw data to insights without writing a single line of code.

Download Free Guide

Step 1: Set Up and Categorize Your NPS Data in Excel

First, you need to organize your raw survey responses. A clean data setup makes the calculation and charting process much smoother. The best way to do this is by creating an Excel Table.

Create a simple table with at least two columns: one for a unique identifier for each response (e.g., "Response ID") and another for the numeric score ("Score").

To make this data useful, you need to add a third column, "Category," that automatically assigns each score to its correct group (Detractor, Passive, or Promoter). You can do this with a nested IF formula.

1. Add a new column to your table named "Category." In the first cell of this new column, enter the following formula:

=IF([@Score]<=6,"Detractor",IF([@Score]<=8,"Passive","Promoter"))

Because you're using an Excel Table, the formula will automatically fill down for all rows. This formula tells Excel:

• If the score is less than or equal to 6, label it a "Detractor."
• If not, check if the score is less than or equal to 8. If it is, label it a "Passive."
• If it's neither, it must be a 9 or 10, so label it a "Promoter."

Graphed

Your AI Data Analyst to Create Live Dashboards

Connect your data sources and let AI build beautiful, real-time dashboards for you in seconds.

Build Your First Dashboard

Step 2: Calculate Your Final NPS Score

Next, you need to create a small summary section to count the responses in each category and calculate the final score. You can do this on the same sheet or a new one.

Here’s how to build it:

1. Set up a summary table: Create labels for Promoters, Passives, Detractors, and Total Responses.
2. Count each category: Use the COUNTIF formula to count the occurrences of each category. Let's assume your Excel Table is named Table1 (you can check and change the name under the "Table Design" tab).

• For Promoters: =COUNTIF(Table1[Category],"Promoter")
• For Passives: =COUNTIF(Table1[Category],"Passive")
• For Detractors: =COUNTIF(Table1[Category],"Detractor")

1. Count total responses: Use the COUNTA formula to get the total number of survey responses.

=COUNTA(Table1[Score])

1. Calculate percentages: In a new set of columns, divide the count of each category by the total number of responses. Format these cells as a Percentage.

• % Promoters: =Promoter Count/Total Responses
• % Passives: =Passive Count/Total Responses
• % Detractors: =Detractor Count/Total Responses

1. Calculate your NPS Score: Finally, apply the NPS formula. Remember to subtract the percentage of Detractors from the percentage of Promoters. Format this final result as a General number, not a percentage.

= % Promoters - % Detractors

Step 3: Create a Visual NPS Chart in Excel

Now for the fun part: visualizing the breakdown. The most common and effective way to display an NPS breakdown is with a 100% stacked bar chart. This chart creates a single, colored bar that immediately shows the proportion of Detractors, Passives, and Promoters.

Building the 100% Stacked Bar Chart

1. Select your percentage data: Highlight just the cells containing the percentage values for Detractors, Passives, and Promoters (in that order).
2. Insert the chart: Go to the Insert tab on the Ribbon, select Column or Bar Chart, and then choose the 100% Stacked Bar chart type under the "2-D Bar" section.

You’ll get a basic chart that needs a bit of formatting to become a proper NPS visual.

Formatting Your NPS Chart for Clarity

Your default chart isn't very intuitive. Let’s make a few crucial adjustments to clean it up and align it with NPS standards.

1. Reorder the sections: Standard NPS charts flow from negative to positive, meaning Detractors on the left, Passives in the middle, and Promoters on the right. Currently, our order is likely reversed.

• Right-click on the bar chart and select Format Data Series.
• In the Format Data Series pane, make sure the series for "Detractors," "Passives," and "Promoters" are in that order. If not, go to Select Data and use the up/down arrows to reorder them. If your data was selected with Detractors first, they should already be on the left.

1. Change the colors: Color is key for at-a-glance understanding. Apply the standard NPS colors:

• Red for Detractors: Click on the Detractor section of the bar, go to the Format tab, select Shape Fill, and choose a red color.
• Gray or Yellow for Passives: Click the middle section representing Passives and fill it with a neutral gray or light yellow.
• Green for Promoters: Click the Promoter section and fill it with a positive green.

1. Clean up the chart: Remove visual clutter for a cleaner report.

• Delete the Y-axis label (the "1" on the left) as it’s not adding value. Just click it and press Delete.
• Delete the X-axis (the 0% to 100% scale at the bottom).
• Delete the Legend, as the colors within the bar are self-explanatory once you add labels.
• Click on the chart, click the (+) icon that appears on the right, and check the box for Data Labels to show the percentages directly on the chart. You can then right-click the labels and select "Format Data Labels" to change the font color to white for better readability.

After these clean-up steps, your NPS chart is complete and ready to be added to any dashboard or presentation!

AI for Data Analysis

Crash Course

Graphed

Free PDF Guide

AI for Data Analysis Crash Course

Learn how to get AI to do data analysis for you — the best tools, prompts, and workflows to go from raw data to insights without writing a single line of code.

Download Free Guide

Best Practices for Presenting Your NPS Chart

Once you’ve built your NPS chart, here are a few extra tips for making it as insightful as possible:

• Include the final score and respondent count: Place your calculated NPS score (e.g., "-5") and the total number of responses (e.g., "N = 85") either in the chart title or as a text box underneath. This provides crucial context. The score tells you the result, while the respondent count tells you how statistically significant it is.
• Track your NPS over time: A single NPS score is a snapshot. Its true power comes from tracking it over time. Consider creating a trend line chart showing your NPS score month-over-month or quarter-over-quarter to spot trends in customer satisfaction.
• Segment your data: Don’t stop at an overall score. If you have the data, create separate NPS charts for different customer segments, such as new vs. longtime customers, different geographic regions, or users of different product lines. This can reveal which parts of your business are creating loyal fans and which need improvement.

Final Thoughts

Turning a list of numbers into a clear, visual story is essential for making data-driven decisions, and Excel gives you all the tools you need to build an effective NPS chart from scratch. By carefully structuring your data, using a few simple formulas, and applying some thoughtful formatting, you can create a professional-grade visual that keeps your team focused on improving customer loyalty.

Of course, manually downloading CSVs, cleaning data in Excel, and rebuilding reports every week can be time-consuming, especially as your data volume grows. For teams looking to automate this process, we built Graphed to do the heavy lifting for you. We connect to your data sources, whether it's a Google Sheet with survey results or a CRM like HubSpot, and let you build real-time dashboards just by describing what you want in plain English. This way, you spend less time crunching numbers and more time acting on the insights.