How to Organize Spreadsheet Data by Category and Calculate Totals in WPS Office
Question details
The user needs to group a large spreadsheet data set by category and display a calculated total for each category in a clear report.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- A report needs to summarize spreadsheet records by category instead of leaving them as ungrouped rows.
- Observed behavior
- The desired state is a grouped summary with a total for each category.
Make sure the source data has a distinct header row and that each column contains consistent data types, because WPS Spreadsheets uses the header names as PivotTable fields.
Create a PivotTable to Group Data by Category and Sum Totals in WPS Spreadsheets
Use this method when you want an interactive summary that groups records by category and automatically calculates totals without changing the original data.
A PivotTable is the fastest way to reorganize a large table into a compact category summary. WPS Spreadsheets keeps the source data intact and lets you refresh the totals when the data changes.
Open the workbook in WPS Office Spreadsheets and select the range that contains the header row and all rows you want to summarize.
Click the Insert tab on the ribbon and choose PivotTable. In the dialog, confirm the selected range and choose New Worksheet, then click OK.
In the PivotTable Fields pane, drag the category field, such as Product or Department, into the Rows area to group the records by that field.
Drag the numeric field you want to total into the Values area. WPS Spreadsheets normally applies Sum to numeric fields automatically.
If the Values area shows Count instead of Sum, click the field dropdown, select Value Field Settings, choose Sum, and click OK to display category totals.
Create a Category Total Report in WPS Office Spreadsheets
Use WPS Spreadsheets to turn raw spreadsheet data into a grouped category summary with calculated totals. It supports Excel PivotTable workflows, so you can build the same report without switching tools.
- 1. Open your data in WPS Spreadsheets: Launch WPS Office, open the .xlsx or .xls workbook, and click the sheet containing the raw data.
- 2. Select Insert > PivotTable: Highlight the data range with headers, go to the Insert tab, and click PivotTable.
- 3. Build the grouped summary: Drag the category field to Rows and the value field to Values, then set the value summary to Sum.
- 4. Refresh and save the report: If source data changes, right-click the PivotTable and choose Refresh, then save the workbook in WPS Spreadsheets.
Frequently Asked Questions
Can WPS Office create a PivotTable from existing Excel data?
Yes. Open the workbook in WPS Spreadsheets, select the range, click Insert > PivotTable, and use the same Rows and Values field areas as Excel.
Why does my PivotTable show count instead of sum?
This happens when the value field contains blanks or text, or the default aggregation changed. Click the field in the Values area, choose Value Field Settings, and set Sum.
Will a PivotTable change my original spreadsheet data?
No. A PivotTable creates a separate summary view; the original source range remains unchanged and can be refreshed when data is updated.
Can I group dates or custom categories in WPS Spreadsheets?
Yes. After placing a date or category field in Rows, right-click a row label, choose Group, and select the grouping options such as months or custom intervals.
