How to subtotal, unsummarize and total in PivotTable data

Key points of this article

Subtotals and totals are two independent settings. Changing the layout will not automatically change the summary rows. To cancel the summary, you need to operate it separately.
The subtotal row is at the bottom of each group by default. If you want to place it at the top, you need to change the position to the top of the group in the summary settings of this field.
When canceling the total, the rows and columns must be set separately. Only one direction is turned off, and the total in the other direction will still be retained.
After changing the summary settings, it is recommended to check whether the total is consistent with the source data. After the source data changes, refresh first and then check.

When making a pivot table, many people get stuck on the summary row for the first time: they want to add a category subtotal, but only the total appears; they want to put the summary above each group, but can't find a place to change it; they want to remove the redundant total row, but are afraid of messing up the structure of the entire table. This article is written for people who are using Excel to make data summary reports. It explains clearly how to add, move and cancel subtotals and totals, and tells you why sometimes "the summary rows are still there after changing the layout."

After reading, you can do three things: add or remove subtotals for row fields, adjust summary rows above or below the group, and control the display of row totals and column totals independently. The steps in this article take the common desktop version of Excel as an example. The locations of other versions may be slightly different, but the setting logic is basically the same.

How to add, move, and delete subtotals and totals in a pivot table? One sentence conclusion

First select any cell in the pivot table, then set subtotals for specific row fields, and set totals for the entire table. The two have no influence on each other. The table below provides methods based on your situation.

your situation	Recommended practices	reason
I want each group to have a subtotal	Enable subtotaling for this row field and select functions such as sum or count.	The subtotal is bound to the field and only affects this level of grouping.
Want to put the subtotal above each group	In the summary settings of this field, change the summary position to the top of the group	The default is at the bottom, and the top is a separate setting item
I just want details, not subtotals	Turn off summary display in subtotal settings	Canceling aggregation does not delete fields, the data remains
Just want to remove the total row at the bottom	In total settings, turn off row-wise totals and keep column-wise totals (or vice versa)	Row totals and column totals are controlled separately
The report layout has been changed, but the summary rows have not changed.	Check whether subtotals and totals have been set separately	Layout and summary are two sets of settings, layout does not reset summary

Before you start: Confirm these points first

First make sure that the PivotTable itself is selected. If the cursor is placed on a normal cell, summary-related options are usually gray. Click on any value or label cell inside the pivot table, and then look for related settings. This step is easy to miss.

First identify which layer you want to change. Pivot tables may have multiple row fields, such as "Region" and "Product". Classification and summary are set according to fields. Changing the summary of "Region" will not affect "Product". First determine which field you want to work on to avoid correcting the wrong level.

First confirm whether the summary function meets the requirements. The default for subtotals is usually sum, but text fields and scenarios that require counts or averages should be changed to the corresponding function in the settings. Wrong function selection makes the subtotal numbers look normal, but their actual meaning is wrong.

First confirm whether the source data has been updated. Pivot tables are snapshots generated from source data. If the source table later changes numbers or adds new rows, it needs to be refreshed before the summary results are updated. This point will be mentioned later in the verification.

If the data is in a PDF table, convert it to Excel first. Some reports are only available in PDF version and cannot be directly pivoted. The PDF editor of the OmniDoc desktop client can export PDF to Word, Excel or PowerPoint, and then create a pivot table after exporting. The exported results should be checked against the original file, especially for merged cells and spreadsheet tables. See download.omnidoc.top for the download address.

Steps to add, move and cancel subtotals in a PivotTable report

Select the PivotTable. Click any cell in the table to make table-related tabs appear in the ribbon.
Find the classification and summary settings. Find the "Subtotal" option group in the pivot table related tabs, which controls the subtotal row of the row field.
Add classification summary. If only details are currently displayed, set the summary position to the bottom of the group, and a subtotal row will appear below each group.
Move the summary to the top. When it needs to be placed at the top of each group, set the summary position to the top of the group. This is often referred to as "show summary above".
Cancel classification and summary. When the summary display is turned off in the subtotal settings, the subtotal rows will disappear, but the detail and grouping structures will remain.
Set total. Set row totals and column totals separately in the total options. When both directions are turned on, they will be displayed. When only one direction is turned on, only that direction will be displayed. If both directions are turned off, both will be removed.
Check results. Scroll through each grouping and the end of the table to confirm that the position and quantity of the subtotal rows and total rows are as expected, and then proceed to the following verification process.

Check each item after completion

Check items	How to see	What to do if there is a problem
Subtotal position	Check whether the subtotal row for each group is above or below your request	Go back to the category summary settings, reselect the top or bottom of the group, and do not drag manually
Subtotal figures	Randomly select a group and manually add the numbers from the source data	If inconsistent, check whether the field has been refreshed and if it has been mistakenly placed in the value area
Summary function	Subtotal uses sum, count, or average	Change the required function in the field settings and check again
In total, the ranks and ranks are balanced	Are the totals at the end and right of the table exactly what you want?	Adjust the row totals and column totals separately; don't just close one and think both are closed
Source data update	After the source meter changes, is the pivot meter number synchronized?	Right-click or refresh in the data options, then check if the subtotal and total have changed

📊 Core Comparison

Summary by category, total, and report layout are often mixed together. The comparison below can help you decide which item to change.

Set the item	Control what	Common uses	Will changing it affect other settings?
Classification and summary	Each row of fields is grouped into subtotal rows and their positions	View subtotals by region and category	It does not affect the total or change the layout format
In total	The total of rows at the end of the table and the total columns on the right side of the table	Look at the overall total	Rows and columns can be controlled separately without affecting grouped subtotals
Report layout	Fields are arranged in compressed, outline, or table formats	Adjust reading methods and print quality	When the layout changes, the subtotal and total settings usually remain unchanged

❓ Frequently Asked Questions (FAQ)

1. Why is the summary of categories in the pivot table missing?

Most of the time, the report layout has been changed, or the summary display by category has been mistakenly disabled. Reselect the pivot table and change its position back to the bottom or top of the group in the summary settings.

2. Why do you only want to remove the total, but the total on the right side of the table still remains?

Row totals and column totals are controlled separately. The end of the table is the row total, and the right side is the column total. You need to close them separately or close both directions directly.

3. Can pivot tables delete summaries and keep only details?

Yes, you can. Turn off the category summary display, then turn off both rows and columns for the total, leaving only the detailed data for each field. Source data will not be deleted as a result.

4. Will the summary settings reset when refreshed?

Generally, it doesn't; the main update is the number of updates. However, if you add or remove row fields, the original category summary settings may need to be reset on the new field, so you need to check after refreshing.

📝 Summary

The PivotTable's Category Summary and Total are two independent settings. First, select the pivot table, then adjust the fields and column directions separately to add, move to top, and cancel. After revision, manually verify with a set of numbers and refresh the source data first after changes to ensure the summary results are reliable.

Open OmniDoc and process your table file