Fix an Excel Pivot Table That Shows Statuses but No Counts in WPS Spreadsheets
Question details
The user needs to count records by status in an Excel pivot table that currently shows green, yellow, and red status labels without numeric counts.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- The user is analyzing status data in a pivot table and expects a count of records for each status label.
- Observed behavior
- The pivot table displays green, yellow, and red status labels but does not show the number of records for each status.
Make sure the pivot table is based on a source range with a clear header row, and check that the Status column contains consistent text values without blank cells.
Add the Status Field to the Values Area as a Count
Use this when the status labels are already in the Rows area but no numeric count column appears.
A pivot table only displays labels when a text field is placed in the Rows or Columns area. To count how many records have each status, the same field must also be placed in the Values area and set to Count, not Sum.
Click any cell inside the pivot table so that the PivotTable Fields pane appears on the right side of the WPS Spreadsheets window.
Drag the Status field into the Rows area if it is not already there, then drag the same Status field into the Values area.
In the Values area, click the drop-down arrow next to Status and select Value Field Settings.
In the Value Field Settings dialog, select Count under Summarize Values By, then click OK. The pivot table will now show a count for each status.
If the counts still do not appear, go to the PivotTable Analyze tab and click Refresh, and confirm the source range includes all data rows.
Check Source Data and Refresh the Pivot Table
Use this troubleshooting solution when the Count setting is already applied but the pivot table still shows no counts.
Fix Pivot Table Counts in WPS Spreadsheets
Open your Excel file in WPS Spreadsheets and use the familiar PivotTable fields to set the Status field to Count, so each status label shows the number of records instantly.
- 1. Open your file in WPS Spreadsheets: Launch WPS Spreadsheets and open the Excel workbook that contains the pivot table.
- 2. Select the pivot table: Click any cell inside the pivot table to activate the PivotTable Fields pane.
- 3. Configure Status as a count: Drag Status to Rows and to Values, then set Value Field Settings to Count.
- 4. Refresh and save: Click Refresh on the PivotTable Analyze tab, then save the file as a .xlsx to keep Excel compatibility.
Frequently Asked Questions
Why does my pivot table show green, yellow, and red statuses but no counts?
Because the Status field is only in the Rows or Columns area. A text field must also be added to the Values area and set to Count before WPS Spreadsheets can display the number of records.
How do I change a pivot table value from Sum to Count in WPS Spreadsheets?
In the PivotTable Fields pane, click the drop-down arrow next to the field in the Values area, choose Value Field Settings, and select Count under Summarize Values By.
What should I check if the pivot table count still appears blank after selecting Count?
Check that the source data has a header in every column, the Status column has no blank cells, all status text is spelled consistently, and the pivot table source range includes all rows. Then click Refresh.
Can WPS Spreadsheets count text status values in a pivot table?
Yes. The Count summary works with text fields, while Sum is normally used for numeric fields. Choosing Count gives the number of records for each text status.
