WPS Spreadsheet Pivot Tables: Create Monthly Subtotals by Person and Price
Question details
The user needs to summarize price data by month and optionally by person with a PivotTable.
- Product
- WPS Spreadsheet
- Device & OS
- not provided
- Scenario
- A table contains dates, person names, and prices, and the user wants monthly subtotals that can be refreshed when new records are added.
- Observed behavior
- The desired result is a PivotTable showing summed prices by month, with person names arranged in rows or columns for additional comparison.
Check that the source has clear headers such as Date, Person, and Price, and that every date is stored as a valid date rather than plain text. Convert the source range to a table first so new records can be included more easily during refreshes.
Create a monthly price subtotal PivotTable in WPS Spreadsheet
Use this method to summarize prices by month and optionally break the totals down by person.
A PivotTable summarizes the source records without changing the original data. Place Date in Rows, Person in Rows or Columns, and Price in Values with the calculation set to Sum, then group the dates by months and years.
Select any cell in the date, person, and price range, then choose Insert > Table. Confirm that the table has headers and click OK.
Click inside the table and select Insert > PivotTable. Choose the table or selected range as the source, select a destination, and click OK.
In the PivotTable Fields pane, drag Date to Rows, Person to Columns or Rows, and Price to Values. Open the Price field settings and choose Sum if another calculation is selected.
Right-click any date in the PivotTable, choose Group, select Months and Years, and click OK. The row labels will display monthly periods instead of individual dates.
After adding records to the source table, click inside the PivotTable and choose Refresh from the PivotTable tools or the right-click context menu.
Build Refreshable Monthly Subtotals with WPS Spreadsheet
WPS Spreadsheet lets you create a PivotTable from date, person, and price data, group dates by month and year, and refresh the summary when the source table changes. Its familiar spreadsheet interface makes it easy to migrate an existing Excel workbook.
- 1. Open the workbook: Launch WPS Spreadsheet and open the workbook containing the Date, Person, and Price columns.
- 2. Insert a PivotTable: Select the source range or table, then choose Insert > PivotTable and confirm the source and destination.
- 3. Configure the fields: Drag Date to Rows, Person to Rows or Columns, and Price to Values; set Price to Sum.
- 4. Group and refresh the results: Right-click a date, select Group, choose Months and Years, and use Refresh after new source records are added.
Frequently Asked Questions
How do I show monthly totals instead of individual dates in a WPS Spreadsheet PivotTable?
Right-click any date in the PivotTable, choose Group, select Months and Years, and click OK. The PivotTable will combine records into monthly periods.
Where should I place the Person field for a person-by-month comparison?
Drag Person to Columns to show people across the top, or drag it to Rows beneath Date to list each person under each month. You can also place it in Filters when you only need to view one person at a time.
Why does the PivotTable show Count of Price instead of Sum of Price?
The Price column may contain text or blank values. Check that every price is numeric, then open the Price field settings in the Values area and select Sum.
How can I include new records in the monthly subtotal report?
Add the records directly below the source table, then click inside the PivotTable and choose Refresh. Using a formatted table as the source helps the PivotTable recognize the expanded data range.
