How to Identify Source Values Behind an Excel PivotTable Total in WPS Office Spreadsheets
Question details
The user wants to identify which underlying source records make up a total shown in an Excel PivotTable.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- The user is reviewing an Excel PivotTable in WPS Office and needs to trace a displayed total back to its source data for verification.
- Observed behavior
- The PivotTable displays an aggregated total, but the individual source rows behind that total are not immediately visible.
Make sure the PivotTable uses a regular worksheet or table as its source, and open the workbook in WPS Office Spreadsheets so the drill-down sheet can be created.
Drill Down from a PivotTable Total to Its Source Records
Use this method when you need to audit or verify an Excel PivotTable total quickly; WPS Spreadsheets creates a new worksheet with the exact underlying records.
WPS Office Spreadsheets supports Excel-compatible PivotTable drill-down. Double-clicking a displayed total extracts the source rows that form that value and places them on a new worksheet, so the original PivotTable remains unchanged.
In WPS Office Spreadsheets, open the workbook that contains the PivotTable and click any cell inside the PivotTable to activate it.
Double-click the PivotTable total you want to inspect, such as a sales amount of 10. WPS Spreadsheets creates a new worksheet with the source records for that total.
Go to the new worksheet to review the extracted records. If you want to keep the details, save the workbook; otherwise close or delete the sheet.
Find the Source Records Behind a PivotTable Total in WPS Office Spreadsheets
WPS Office Spreadsheets lets you drill into any PivotTable total directly. Double-click a total to generate a separate worksheet containing the exact source values behind that number, keeping your original PivotTable intact.
- 1. Open the Excel workbook: Launch WPS Office Spreadsheets and open the .xlsx workbook that contains the PivotTable you want to audit.
- 2. Select the PivotTable total: Click the PivotTable, then double-click the exact total value for which you need the source records.
- 3. Review the new source records sheet: WPS Spreadsheets creates a new worksheet with the underlying rows. Review the detail sheet to confirm which records contribute to the total.
- 4. Save the workbook: Save the workbook if you need to keep the drill-down worksheet for documentation or further analysis.
Frequently Asked Questions
Does double-clicking a PivotTable total work for every PivotTable in WPS Spreadsheets?
It works for PivotTables based on a regular worksheet or table source. If the data comes from an external connection or data model, drill-down may need to be allowed by the data source settings.
What happens when I double-click a grand total instead of a row total?
WPS Spreadsheets creates a new worksheet with all source records that contribute to that grand total intersection. The original PivotTable is not changed.
Can I remove the drill-down worksheet after it is created?
Yes. You can delete the newly created worksheet or use Undo immediately. The original PivotTable remains unchanged.
Does the new worksheet include all columns from the source data?
The extracted detail sheet normally contains the fields available in the PivotTable data source, including record-level columns. The exact columns depend on the source structure.
Will this drill-down method work with .xlsx files created in Microsoft Excel?
Yes. WPS Office Spreadsheets opens .xlsx workbooks and supports Excel-compatible PivotTables, including double-click drill-down into totals.
