Pivot Table is a super useful tool in Excel. It can give us some of the best representations of our data possible.
However, it can also be pretty troublesome for you to manipulate it if you do not it well. One of the things that you may need is to remove the totals from the tables.
We are going to show how to do it in the text below.
Remove Subtotals from Pivot Table
We will create a table with NBA players, their clubs, and statistic from several nights.
This table has 28 rows but only 15 are presented for simplification.
We will create a Pivot Table from our data in a sheet that we will simply call Pivot Table.
We have added players and teams in the Rows table and values’ sum of points and rebounds. We can see that we have two totals for every player (44 points for Anthony Davis appears twice), and we have a total of points and rebounds.
First, we will remove Subtotals. We are going to change the view of the Pivot Table. We do this by right-clicking on the table, then selecting PivotTable Options.
Next, we go to the Display tab and select the option Classic PivotTable layout (enables dragging of fields in the grid):
This will separate Players and Teams in different columns:
Next, we will remove the Subtotals. This is fairly simple. All we have to do is go in each column, right-click on it and deselect Subtotal:
Our Pivot Table now does not have Subtotals and points and rebounds are only found once in the table.
If you have a lot of columns, it would not be very convenient to go to each column and right-click on every column.
To work around this issue, and to remove Subtotals much easier, we will click anywhere on the table, go to PivotTable Tools >> Design >> Layout >> Subtotals.
On the dropdown menu that appears, we will see three options:
- Do Not Show Subtotals
- Show all Subtotals at Bottom of Group
- Show all Subtotals at Top of Group
As seen, you can easily manipulate Subtotals from this tab.
Remove Totals from Pivot Table
Although it is not very common, sometimes you would want to remove Grand Total as well. To do this, we use the same tab as we did above, and go to PivotTable Tools >> Design >> Layout >> Grand Totals. When we click on it, a dropdown menu will appear:
As seen, we can remove Grand Totals from rows and columns, we can activate it for both rows and columns, or activate it only for one option.
For our example, we will choose option number one. This way, we will remove totals from our Pivot Table and it will look like this: