The Pivot Table Bloat
When you create a Pivot Table in Microsoft Excel to summarize a massive dataset, you often drag multiple fields into the “Rows” area to create a hierarchy (e.g., Year > Quarter > Month). By default, the moment you add a second level to that hierarchy, Excel automatically generates a bold “Subtotal” row at the bottom (or top) of every single group. If you have hundreds of categories, these automatically generated subtotals instantly double the length of your Pivot Table, making the data incredibly bloated, difficult to read, and frustrating to copy-paste into other systems.
How to Disable Subtotals for a Specific Pivot Table
You can instantly strip all automatically generated subtotals out of your current Pivot Table, leaving only the clean, raw grouped data.
1. Click anywhere inside your Pivot Table to make sure it is selected.
2. Look at the ribbon at the top of the screen. Two new tabs will have appeared: “PivotTable Analyze” and “Design”.
3. Click on the Design tab.
4. On the far left side of the Design ribbon, click the button labelled Subtotals.
5. From the drop-down menu, select Do Not Show Subtotals.
Every subtotal row in the entire Pivot Table will instantly vanish, dramatically shrinking the size of your report.
How to Stop Excel from Ever Adding Subtotals Again
Disabling them per-table is fine, but if you never want Excel to automatically generate subtotals when you create a new Pivot Table in the future, you can change the global default layout settings.
1. Click on the File tab in the top-left corner of Excel.
2. Click Options at the bottom of the left-hand sidebar.
3. In the Excel Options window, click on Data in the left menu.
4. In the main pane, look for the section titled “Data options” and click the button labelled Edit Default Layout…
5. A new “Edit Default Layout” dialog box will appear.
6. Click the Subtotals dropdown menu in this box.
7. Select Do Not Show Subtotals.
8. Click OK, then click OK again.
From now on, every new Pivot Table you generate will be clean and free of subtotal bloat by default.