Chart & Visualization Issues

Build an Excel NPS Dashboard with Tables and Slicers

Huma Ashraf ChHuma Ashraf Ch Nov 18, 2026 870 views

Question details

Create an interactive dashboard that filters survey responses and displays NPS, demographic results, and scores for other question categories.

Build an Excel NPS Dashboard with Tables and Slicers
Product
Microsoft Excel
Device & OS
not provided
Scenario
A survey dataset needs to be summarized in a dashboard with slicers and filter-aware metrics.
Observed behavior
Slicer selections should update the displayed NPS and related survey summaries.
Before you start

Arrange the survey data with one response per row and a header for each field, including the 0–10 NPS score and demographic categories. Check that scores are numeric and identify blank or invalid responses before calculating NPS.

Solution 1Recommended

Create a Slicer-Connected PivotTable Dashboard

Use this approach when you want shared slicers to update NPS and other PivotTable or PivotChart summaries.

1
Create a source table

Select the survey range, choose Insert > Table, and confirm that the table has headers. Add columns for a valid NPS response, promoter, and detractor: mark valid whole-number scores from 0 through 10 with 1, promoters with 1 for scores of 9–10, and detractors with 1 for scores of 0–6; use 0 otherwise.

2
Build the NPS PivotTable

Select a cell in the table and choose Insert > PivotTable. Add the valid-response field to Filters and select 1, then add the promoter and detractor fields to Values and set both to Average in Value Field Settings.

3
Calculate the NPS

In a dashboard cell, subtract the detractor average from the promoter average and multiply by 100. For example, if the two displayed averages are in B4 and C4, enter =(B4-C4)*100; update the references to match your PivotTable.

4
Add survey summaries

Create additional PivotTables from the same source table with Insert > PivotTable. Place demographic fields in Rows and response counts or other question scores in Values; use Insert > PivotChart where a chart helps present the results.

5
Connect the slicers

Select a PivotTable, choose PivotTable Analyze > Insert Slicer, and select fields such as region or age group. Select each slicer and open Slicer > Report Connections, or PivotTable Connections, to check the PivotTables it should control.

6
Test and refresh

Click different slicer buttons and confirm that the NPS cell and connected summaries change. After adding or changing source responses, choose Data > Refresh All.

Create a Slicer-Connected PivotTable Dashboard
Connect compatible PivotTables: Report Connections lists PivotTables that share a compatible PivotTable cache. Create the PivotTables from the same source, or copy an existing PivotTable and rearrange its fields.
Build a filter-aware survey dashboard

Calculate Filtered NPS in WPS Spreadsheets

Use WPS Spreadsheets to filter survey responses and calculate NPS from visible rows with helper columns and SUBTOTAL.

  1. 1. Open the survey: Open WPS Spreadsheets and use Open to select your Excel survey file. Confirm that each response occupies one row.
  2. 2. Add NPS helper columns: If scores are in column B, enter =IF(AND(ISNUMBER(B2),B2>=0,B2<=10,B2=INT(B2)),1,0) in C2, =IF(C2=1,--(B2>=9),0) in D2, and =IF(C2=1,--(B2<=6),0) in E2. Fill the formulas down.
  3. 3. Calculate the visible NPS: In a summary cell, enter =IFERROR(100*(SUBTOTAL(109,D2:D100)-SUBTOTAL(109,E2:E100))/SUBTOTAL(109,C2:C100),""). Change the ranges to cover your responses.
  4. 4. Filter the survey: Select the survey headers and choose Data > Filter. Use a filter arrow to select a demographic or category and check the updated NPS cell.
Calculate NPS from filtered survey responsesSummarize demographic and question-category resultsOpen and edit Microsoft Excel XLSX and XLS filesUse a familiar spreadsheet interface
microsoft office alternative - wps office

Frequently Asked Questions

How is NPS calculated?

Divide the number of 9–10 scores by the number of valid responses, subtract the proportion of 0–6 scores, and multiply by 100. Scores of 7–8 are passive responses and remain in the denominator.

Why isn't my slicer updating every PivotTable?

Select the slicer and open Slicer > Report Connections or PivotTable Connections. Check the PivotTables you want to control; only compatible PivotTables sharing a cache are available.

Can a PivotTable slicer update a SUBTOTAL formula over the source data?

Not by itself. A PivotTable slicer filters connected PivotTables rather than source worksheet rows. Use Data > Filter on the source rows for SUBTOTAL, or calculate NPS from the connected PivotTable.

How do I include new survey responses in the dashboard?

Add responses to the Excel source table, then choose Data > Refresh All. Check that any formula ranges outside the table also include the new rows.

Should blank or invalid scores count in NPS?

No. Exclude blank and invalid values from the NPS denominator. In the PivotTable approach, use the valid-response field in Filters and select 1.