How to Show Subcategories Under Main Categories in an Excel PivotTable
Question details
The user wants to nest subcategories underneath main categories in a PivotTable instead of having them appear as separate unlinked items.
- Product
- Excel / WPS Spreadsheet
- Device & OS
- not provided
- Scenario
- Organizing hierarchical data in a PivotTable report to present summarized categories.
- Observed behavior
- Subcategories appear as separate, independent items rather than being properly nested under their respective main categories.
Ensure your source data is organized in a tabular format with clear, separate columns for main categories and subcategories before creating or modifying your PivotTable.
Arrange Fields in the PivotTable Rows Area
The most direct way to create a hierarchy is by properly ordering the fields in the Rows box of the PivotTable Field List.
PivotTables use the order of fields in the 'Rows' area to determine the hierarchy of your data. The field placed at the top acts as the parent (main category), and subsequent fields below it act as children (subcategories).
Click anywhere inside your existing PivotTable. This action will reveal the 'PivotTable Field List' pane, usually located on the right side of the screen.
Look at the bottom half of the PivotTable Field List pane to find the 'Rows' area (sometimes called 'Row Labels').
Find your main category column in the field list above. Drag it into the 'Rows' area first so that it sits at the very top of the list.
Next, drag your subcategory field and drop it exactly below the main category field within the same 'Rows' area.
Click the '+' (expand) icons next to the main category items directly in your PivotTable to view the nested subcategories.
Change the Report Layout to Outline or Tabular Form
By default, PivotTables use a Compact layout which places main and subcategories in the same column. Changing the layout separates them into distinct columns for better readability.
Easily Manage PivotTable Data with WPS Spreadsheet
WPS Spreadsheet provides a highly intuitive PivotTable interface that fully supports complex data hierarchies, multi-level categories, and seamless compatibility with Microsoft Excel formats.
- 1. Insert a PivotTable: Open your data file in WPS Spreadsheet, go to the 'Insert' tab, and click 'PivotTable'.
- 2. Drag the main category: In the PivotTable Field List pane on the right, drag your main category field down into the 'Rows' section.
- 3. Nest the subcategory: Drag the subcategory field and place it directly beneath the main category in the 'Rows' section to establish the hierarchy.
- 4. Adjust layout if necessary: Go to the 'Design' tab, select 'Report Layout', and choose 'Show in Outline Form' to view categories in separate columns.
Frequently Asked Questions
Why are my subcategories showing up in the same column as my main categories?
This happens when your PivotTable is set to the default 'Compact Form'. To separate them into different columns, go to the Design tab, click 'Report Layout', and select either 'Show in Outline Form' or 'Show in Tabular Form'.
Why is the Group selection grayed out in my PivotTable?
The Group option may be disabled if your source data contains blank cells, text formatting mixed in a date/number field, or if your data was added to the Data Model (Power Pivot). Ensuring your source data is strictly uniform and clean usually resolves this.
How do I quickly collapse all subcategories at once?
Right-click any main category item in your PivotTable, hover your cursor over 'Expand/Collapse' in the context menu, and select 'Collapse Entire Field'. This will hide all subcategories and display only the main category summaries.
Can I add a third level of subcategories to the PivotTable?
Yes, you can add multiple levels of subcategories. Simply drag the third category field from the PivotTable Field List into the 'Rows' area and place it below your second subcategory field to create a multi-level hierarchy.
