How to Hide Detail Rows and Show Only Subtotals in Excel PivotTables
Question details
The user wants to simplify an existing PivotTable by hiding or removing granular detail rows so that only the subtotals and grand totals are displayed.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Summarizing large datasets for high-level reporting where granular details clutter the view and only aggregated subtotals are required.
- Observed behavior
- The PivotTable currently displays all expanded detail fields, making it difficult to focus on the calculated subtotals and overall grand totals.
Determine whether you want to permanently remove the detail data from the PivotTable layout or simply collapse the view to hide the details while keeping the data structure intact.
Remove Unwanted Detail Fields from the PivotTable Layout
This method completely removes the granular data fields from the PivotTable, forcing Excel to calculate and display only the remaining grouping fields and their subtotals.
By removing the lowest-level fields from the Rows or Columns area, the PivotTable will automatically adjust to show only the higher-level categories and their summarized values.
Click any cell inside your existing PivotTable to activate the PivotTable Fields pane on the right side of your screen.
In the PivotTable Fields pane, look at the 'Rows' or 'Columns' areas at the bottom to identify the specific fields that are generating the unwanted detail rows.
Click and drag the unwanted detail fields outside of the pane to remove them, or simply uncheck the box next to the field name in the field list above.
Ensure you keep the primary grouping fields and the numerical fields in the 'Values' area so that Excel continues to calculate and display your required subtotals and grand totals.
Collapse Entire Fields to Hide Details
Use this method if you want to temporarily hide detail rows but keep the fields in the PivotTable layout for quick drill-down later.
Create and Manage PivotTables in WPS Spreadsheet
WPS Spreadsheet offers a robust, highly intuitive PivotTable feature that is fully compatible with Microsoft Excel. You can easily summarize complex data, automatically calculate subtotals, and quickly collapse detail rows to create clean, professional reports.
- 1. Insert a PivotTable: Open your dataset in WPS Spreadsheet, highlight the data range, and go to the 'Insert' tab to select 'PivotTable'.
- 2. Configure your fields: Drag your main categories into the 'Rows' area and your numerical data into the 'Values' area using the PivotTable pane.
- 3. Hide detail rows: Right-click any category header in your newly created PivotTable, select 'Expand/Collapse', and click 'Collapse Entire Field' to view only subtotals.
Frequently Asked Questions
Can I hide detail rows without removing the field from the PivotTable entirely?
Yes. Instead of dragging the field out of the 'Rows' area, you can simply right-click the category header in the PivotTable, select 'Expand/Collapse', and choose 'Collapse Entire Field'. This hides the details but keeps the underlying data structure intact.
Why did my subtotals disappear when I removed a field?
Subtotals summarize the data within a specific grouping. If you accidentally remove the primary grouping field instead of the lower-level detail field, the PivotTable loses the basis for that subtotal. Ensure you only remove the lowest-level fields from the Rows area.
How do I turn on subtotals if they aren't showing at all?
Click anywhere inside your PivotTable, navigate to the 'Design' tab on the ribbon, click the 'Subtotals' button, and select either 'Show all Subtotals at Bottom of Group' or 'Show all Subtotals at Top of Group'.
How can I format the subtotals to stand out more?
You can highlight the subtotal rows, go to the 'Home' tab, and apply formatting such as bold text or a different background color. Alternatively, use the 'PivotTable Styles' gallery under the 'Design' tab to apply a pre-made theme that automatically highlights subtotals.
