Pivot Table Issues

Fix Incorrect Row Labels and Blank Totals in an Excel PivotTable

Natalie TaylorNatalie Taylor Nov 14, 2026 868 views

Question details

The user needs an Excel PivotTable to display meaningful demographic labels and participant totals.

Fix Incorrect Row Labels and Blank Totals in an Excel PivotTable
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)."
Before you start

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.

Solution 1Recommended

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.

1
Organize the source fields

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.

2
Check the headers

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.

3
Format the data as a table

Select a cell in the source data, choose Insert > Table, confirm the range, and select My table has headers.

4
Create a new PivotTable

Select a cell in the table, choose Insert > PivotTable, confirm the table as the source, and select where to place the PivotTable.

5
Arrange the fields

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.

Restructure the source data and rebuild the PivotTable
Check the count: Count ignores blank participant IDs. If a participant can appear in multiple source rows, the result counts those rows rather than necessarily counting unique people.
Work with PivotTables in WPS

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. 1. Open the workbook: Open WPS Spreadsheets and choose Open to select the Excel workbook containing the participant data.
  2. 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. 3. Insert a PivotTable: Select the source range, choose Insert > PivotTable, and confirm the data range and destination.
  4. 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.
Summarize participant counts by demographic categoryOpen and edit Microsoft Excel XLSX filesUse a familiar spreadsheet interfaceGet started with WPS Office for free
microsoft office alternative - wps office

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.