WPS Spreadsheet: Automatically Show Recent Case Numbers in a PivotTable Report
Question details
The user needs a PivotTable report to include case records created during the last seven days as new records are added.
- Product
- WPS Spreadsheet
- Device & OS
- not provided
- Scenario
- A case-tracking report uses a source range and PivotTable, and the user wants recent case numbers to appear without repeatedly changing the source range.
- Observed behavior
- New rows are not included in the report until the source is expanded and the PivotTable is refreshed.
Confirm that the source data has a case number column and a date column containing valid spreadsheet dates. Remove completely blank header cells and place the source records in one continuous range before creating the table.
Use a source table and a Last 7 Days PivotTable filter
Use this setup when new case rows should become part of the PivotTable source automatically while the report shows only recent records.
A table expands when records are added directly below it, so the PivotTable can continue using the table name instead of a fixed cell range. The PivotTable still needs to be refreshed before newly added records appear in the report.
Select any cell in the case data, open Insert, choose Table, confirm that My table has headers is selected, and click OK.
Select a cell in the table, open Insert, choose PivotTable, and select the table name as the data source. If the PivotTable already exists, open its source settings and replace the fixed range with the table name.
In the PivotTable field list, drag the Case Number field to Rows or Values, then drag the case date field to Filters.
Open the date field filter in the PivotTable, choose Date Filters, select Last 7 Days, and apply the filter.
After entering new rows in the table, open Data and choose Refresh or Refresh All to update the PivotTable.
Refresh the PivotTable when the workbook or sheet is opened
Use refresh settings when the report should update at a regular opening or sheet-activation point instead of relying only on manual refresh.
Build a self-updating recent-case PivotTable report in WPS Spreadsheet
WPS Spreadsheet lets you convert case records into a table, use that table as a PivotTable source, apply a Last 7 Days filter, and refresh the report when new records are added. Its familiar spreadsheet workflow also supports Microsoft Excel files for easier migration.
- 1. Create the source table: Open the workbook in WPS Spreadsheet, select the case-data range, choose Insert > Table, and confirm the header row.
- 2. Insert a PivotTable: Select a cell in the new table, choose Insert > PivotTable, and use the table as the PivotTable data source.
- 3. Configure the report: Place Case Number in Rows or Values and place the case date field in the Filters area.
- 4. Apply the recent-date filter: Open the date filter, choose Date Filters > Last 7 Days, and apply the filter.
- 5. Refresh the report: After adding cases, choose Data > Refresh or Data > Refresh All so the PivotTable includes the new records.
Frequently Asked Questions
Will a PivotTable include new rows automatically if the source is a table?
The table expands to include new rows entered directly below it, but the PivotTable must still be refreshed. Select the PivotTable and choose Data > Refresh or Refresh All.
How can I show only case numbers from the last seven days?
Add the case date field to the PivotTable Filters area, open its filter, choose Date Filters, select Last 7 Days, and apply the filter.
Why do new case records not appear after I add them?
The source may still be a fixed cell range, or the PivotTable may not have been refreshed. Confirm that the PivotTable uses the table name as its source, then choose Data > Refresh.
Can the report refresh when the workbook opens?
Click inside the PivotTable and open its Options, Properties, or PivotTable settings. If your WPS Spreadsheet version provides it, enable Refresh data when opening the file; otherwise refresh manually after opening.
