How to Summarize Expanding Excel Data with a Pivot Table in WPS Office
Question details
The user needs to organize expanding cleaning-work records by location, area, and date, then use a pivot table to calculate subtotals and grand totals for annual or historical reports without placing formulas inside the raw data.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- The user maintains cleaning-work records that grow over time and wants a stable, expandable structure for reporting by location, area, and date.
- Observed behavior
- Data should remain in one flat table, with a pivot table used to calculate subtotals and grand totals instead of manual formula rows.
Before creating the report, make sure all source records are in one flat WPS Spreadsheets table with separate columns for date, location, area, progress, and notes. Remove any subtotal formulas or blank rows from the raw data table.
Store All Cleaning-Work Records in One Flat WPS Spreadsheet Table
Use this first if the data is split across sheets or contains subtotal rows; a flat table gives the pivot table a clean source to group and total correctly.
Keeping raw data separate from reports makes it much easier to append new records and prevents totals from being counted twice.
Open WPS Spreadsheets and enter separate column headers for Date, Location, Area, Progress, and Notes in row 1.
Add each cleaning-work record as a new row, with one row per record and no blank rows inside the table.
Do not insert subtotal formulas inside the data rows; the pivot table will calculate totals in the report layer.
Create a Pivot Table to Summarize Data by Location, Area, and Date
Use this after the flat table is ready to calculate live subtotals and grand totals for any period or category.
Summarize Expanding Excel Data with WPS Office Spreadsheets
Use WPS Office Spreadsheets to keep all cleaning-work records in one flat table and insert a pivot table that automatically calculates subtotals and grand totals by location, area, and date.
- 1. Open the workbook in WPS Office: Launch WPS Office Spreadsheets and open the Excel file that contains the flat cleaning-work records.
- 2. Verify the flat data table: Make sure the data uses separate columns for Date, Location, Area, Progress, and Notes, with no subtotal formulas inside the table.
- 3. Insert the pivot table: Click any cell in the data range, go to the Insert tab, choose PivotTable, confirm the range, select New Worksheet, and click OK.
- 4. Configure the report fields: Drag Location, Area, and Date into Rows, then drag Progress into Values and set the calculation to Sum.
- 5. Refresh after appending data: When new cleaning-work records are added, right-click the pivot table and choose Refresh to update subtotals and grand totals.
Frequently Asked Questions
Can I add new cleaning-work records after the pivot table is created?
Yes. Add them to the bottom of the flat source table, then right-click the pivot table and choose Refresh. WPS Office updates all grouped subtotals and grand totals.
Why should I avoid subtotal formulas inside the raw data table?
Subtotal formulas create non-data rows inside the source, which can break pivot grouping and double-count values. Keep the raw table flat and let the pivot table calculate all totals.
How do I group pivot table dates by month, quarter, or year in WPS Office?
Drag the Date field into the Rows or Columns area, right-click a date in the pivot table, choose Group, and select Months, Quarters, or Years.
Where do I display grand totals for rows and columns?
Click the pivot table, open the PivotTable Design or Analyze tab, and choose the Grand Totals options to show totals for rows, columns, or both.
Can I keep an employee entry sheet separate from historical records?
Yes. Use an employee entry sheet for current input, append approved records to a protected master table, and build the pivot table from that master table.
