WPS Spreadsheet: Organize Data by City, Race, and Year with a PivotTable
Question details
The user needs to summarize and organize a large dataset by city, race, year, and related values, including combinations with missing categories or years.
- Product
- WPS Spreadsheet
- Device & OS
- not provided
- Scenario
- The user wants a practical way to group, sort, and compare records across multiple fields without manually rearranging the source data.
- Observed behavior
- The desired result is a summary table that groups City, Race, and Year and calculates the selected values, while leaving unavailable combinations without source records empty or absent.
Check that the source range has one header row with unique column names such as City, Race, Year, and Value. Keep each record on its own row and standardize blank entries so the PivotTable can group the data consistently.
Create a WPS Spreadsheet PivotTable for city, race, and year
Use this method when you need a flexible summary that can group, sort, filter, and calculate values across several data categories.
A PivotTable lets you arrange City, Race, and Year in row or column areas and place a numeric field in Values. Categories or years with no source records do not create misleading values; they remain blank or are omitted from the summary unless matching records exist.
Open the worksheet in WPS Spreadsheet and select the complete data range, including the header row and all records for City, Race, Year, and the value field.
On the ribbon, open the Insert tab, choose PivotTable, confirm the selected table or range, and select whether to place the PivotTable on a new worksheet or an existing worksheet.
In the PivotTable Fields panel, drag City and Race to the Rows area. Drag Year to Columns or place it below Race in Rows, depending on whether you want years displayed across the top or as nested groups.
Drag the numeric field to the Values area. Open its value settings if needed and choose Sum, Count, Average, or another available calculation.
Use the field drop-down arrows in the PivotTable to sort cities, races, or years and to filter specific categories. Refresh the PivotTable from the PivotTable tools when the source data changes.
Build a Flexible City, Race, and Year Summary in WPS Spreadsheet
WPS Spreadsheet can create a PivotTable that groups City, Race, and Year, summarizes numeric values, and lets you sort or filter the resulting report without changing the original dataset.
- 1. Open the dataset: Launch WPS Spreadsheet and open the workbook containing the City, Race, Year, and value columns.
- 2. Insert a PivotTable: Select the source range, then choose Insert > PivotTable and confirm the destination worksheet.
- 3. Arrange the fields: Drag City and Race into Rows, Year into Columns or Rows, and the desired numeric column into Values.
- 4. Customize the report: Use the PivotTable field menus to select Sum, Count, or Average and apply sorting or filters.
Frequently Asked Questions
How should City, Race, and Year be placed in a WPS Spreadsheet PivotTable?
For nested groups, drag City and Race to the Rows area and place Year below Race in Rows. To compare years side by side, drag Year to the Columns area instead.
Which field belongs in the Values area?
Drag the numeric column you want to analyze, such as population or count, into Values. Use the value settings to select Sum, Count, Average, or another available calculation.
What happens when a city or year has no data?
The PivotTable does not create a calculated value for a combination that has no matching source record. It may be blank or absent, depending on the field layout and available records.
How can I show an explicit zero for a missing category or year?
Add a source record for that combination and enter 0 in the numeric value column before refreshing the PivotTable. A missing source row is different from a source row whose value is zero.
Can I update the PivotTable after adding new records?
Yes. Extend the source range if necessary, then select the PivotTable and use the Refresh command in the PivotTable tools or right-click the report and choose Refresh.
