Fix Incorrect Row Labels and Blank Totals in an Excel PivotTable
Question details
The user needs an Excel PivotTable to display meaningful demographic labels and participant totals.

- Product
- Microsoft Excel PivotTable
- Device & OS
- not provided
- Scenario
- Summarizing participant demographics by race and gender in a PivotTable.
- Observed behavior
- Row and column labels appear as 1, while subtotals and totals appear as "(blank)."
Inspect the source worksheet to identify the race, gender, and participant ID fields. Check whether values such as 1 are category codes or indicators in separate columns before changing them.
Restructure the source data and rebuild the PivotTable
Use this approach when race and gender are stored in separate indicator columns or the PivotTable is using the wrong fields.
For a straightforward demographic count, arrange the source as a list with one participant per row and separate Race, Gender, and Participant ID columns. Convert indicator values to category names only when you know what the indicators represent.
On the source worksheet, create a single Race column with category names and a single Gender column with values such as Male and Female. Keep a nonblank Participant ID for each participant.
Give every source column a unique, nonblank header in the first row, such as Participant ID, Race, and Gender. Remove completely blank rows or columns within the source range.
Select a cell in the source data, choose Insert > Table, confirm the range, and select My table has headers.
Select a cell in the table, choose Insert > PivotTable, confirm the table as the source, and select where to place the PivotTable.
In the PivotTable Fields pane, drag Race to Rows, Gender to Columns, and Participant ID to Values. If necessary, open Value Field Settings for Participant ID and select Count.

Check the existing PivotTable fields and refresh the data
Use this if the source already has correctly structured Race, Gender, and Participant ID columns.
Summarize demographic data with WPS Spreadsheets
Use WPS Spreadsheets to organize participant data and create a PivotTable that counts IDs by race and gender.
- 1. Open the workbook: Open WPS Spreadsheets and choose Open to select the Excel workbook containing the participant data.
- 2. Prepare the source: On the source sheet, arrange the data with unique headers for Participant ID, Race, and Gender, and one participant per row.
- 3. Insert a PivotTable: Select the source range, choose Insert > PivotTable, and confirm the data range and destination.
- 4. Set up the summary: In the PivotTable field pane, place Race in Rows, Gender in Columns, and Participant ID in Values. Set the value calculation to Count if needed.

Frequently Asked Questions
Why does my PivotTable show 1 instead of race or gender names?
The selected source fields may contain numeric codes or indicators rather than category names. Check the source worksheet and field layout; use meaningful Race and Gender columns if you know how the codes map to categories.
Why do I see "(blank)" in my PivotTable?
A "(blank)" label can appear when a row or column field contains empty source cells. Check the Race and Gender columns for missing values, correct the source where appropriate, and select PivotTable Analyze > Refresh.
How do I count participants instead of summing their IDs?
Place Participant ID in Values, open its Value Field Settings, and choose Count. Make sure each ID is populated; repeated participant rows will be counted more than once.
Will refreshing update a PivotTable after I change the source data?
Yes, if the changes are within the PivotTable's source. Choose PivotTable Analyze > Refresh. If the source range has changed, check PivotTable Analyze > Change Data Source first.




