Pivot Table Issues

How to Show Excel PivotTable Items and Subtotals Without Extra Columns

Kushani Nimanthika Sep 28, 2026 869 views

Question details

The user wants to display item unit values alongside an item subtotal in an Excel PivotTable without generating unwanted extra columns in the table structure.

How to Show Excel PivotTable Items and Subtotals Without Extra Columns

Product: Microsoft Excel
Device & OS: not provided
Scenario: Arranging PivotTable fields to view item-level data and subtotals cleanly for accurate data analysis.
Observed behavior: Placing both the item and unit fields in the Columns area creates unwanted extra unit columns instead of a clean subtotal view.

Before you start

Ensure your raw data is organized in a proper tabular format with clear column headers and no blank rows before generating your PivotTable.

Solution 1Recommended

Optimize PivotTable Field Layout for Subtotals

Adjusting the fields between the Rows and Values areas prevents the creation of extra columns while still displaying individual unit values and subtotals.

By default, placing multiple fields into the Columns area of a PivotTable stretches the data horizontally, creating a new column for every unique data point. To maintain a compact view with subtotals, you should leverage the Rows and Values areas instead.

This layout approach forces Excel to calculate subtotals vertically per category rather than spreading the unit values across the top axis.

Open the PivotTable Fields Pane

Click anywhere inside your existing Excel PivotTable to activate the PivotTable Fields pane on the right side of your screen.

Move the Item Field to Rows

In the PivotTable Fields pane, click and drag your 'Item' field (or your main category) into the 'Rows' area instead of the 'Columns' area.

Move the Unit Field to Values

Drag the 'Unit' field (or your numeric data) into the 'Values' area to aggregate the data properly.

Enable and Format Subtotals

Navigate to the 'Design' tab on the Excel ribbon, click the 'Subtotals' button in the Layout group, and select 'Show all Subtotals at Bottom of Group' or 'Top of Group' based on your visual preference.

Optimize PivotTable Field Layout for Subtotals

Change Report Layout: If you want a cleaner separation of data, go to the Design tab, click 'Report Layout', and switch to 'Outline Form' or 'Tabular Form'.

Direct Solution in WPS Office

Create and Manage PivotTables Seamlessly in WPS Office

WPS Spreadsheet provides robust PivotTable features identical to Microsoft Excel. You can easily drag and drop fields, customize subtotals, and change report layouts to analyze your data effectively without layout issues.

1. Insert a PivotTable: Open your dataset in WPS Spreadsheet, go to the 'Insert' tab on the top ribbon, and click 'PivotTable'.
2. Assign Fields to Rows and Values: In the PivotTable Fields pane, drag your main category to the 'Rows' box and the metric you want to calculate to the 'Values' box.
3. Configure Subtotal Display: Go to the 'PivotTable Tools' or 'Design' tab, click 'Subtotals', and choose to display them at the top or bottom of the group without adding extra columns.

Fully compatible with Microsoft Excel (.xlsx) files and advanced PivotTable structures.Intuitive drag-and-drop PivotTable Field list for quick and clean data summarization.Free and lightweight, ensuring fast processing even with large spreadsheet datasets.

microsoft office alternative - wps office

Frequently Asked Questions

Why are my PivotTable subtotals not showing up?

Subtotals may be disabled in your PivotTable layout settings. To fix this, click inside the PivotTable, go to the Design tab, click 'Subtotals', and select either 'Show all Subtotals at Bottom of Group' or 'Show all Subtotals at Top of Group'.

How do I remove grand totals from my Excel PivotTable?

Navigate to the Design tab under PivotTable Tools, click on 'Grand Totals' in the Layout group, and select 'Off for Rows and Columns'.

Can I use Custom Field Settings to change how subtotals calculate?

Yes. Right-click the field header in the PivotTable, select 'Field Settings', and under the 'Subtotals & Filters' tab, choose 'Custom'. From there, you can select specific aggregation methods like Average, Count, or Max instead of the default Sum.

What is the difference between Tabular and Outline form in PivotTables?

Outline form displays subtotals at the top of each group and indents sub-items underneath, while Tabular form places subtotals at the bottom and shows parent items and sub-items in separate side-by-side columns.

Excel & Data Problems