How to Remove Subtotals in Excel Pivot Table: Expert Guide

If you’ve ever worked with pivot tables in Microsoft Excel, you may have noticed that by default, they include subtotals for each group of data. While subtotals can be useful in some cases, there are many situations where you may want to remove the subtotals to simplify your pivot table and make it easier to read. Subtotals can add unnecessary clutter and complexity, especially when working with large datasets or presenting data to others.

In this comprehensive guide, we’ll walk through the steps to quickly and easily remove subtotals from your Excel pivot tables, as well as explore some additional tips and best practices for working with pivot tables effectively.

How to Remove Subtotals in Excel Pivot Table?

Now that we have a better understanding of what subtotals are and when you might want to remove them, let’s look at the step-by-step process for doing so. Excel provides two main methods for removing subtotals: removing them for a specific field, or removing them for the entire pivot table.

Method 1: Removing Subtotals for a Specific Field

If you want to remove subtotals for a specific field or category in your pivot table, follow these steps:

1. Click on any cell inside the pivot table to activate it. This will display the PivotTable Tools tabs in the Excel ribbon.
2. In the PivotTable Fields pane on the right side of your screen, locate the field for which you want to remove subtotals. This pane shows all the fields that are currently included in your pivot table, organized by Filters, Columns, Rows, and Values.
3. Right-click on the field and select Field Settings from the context menu that appears. This will open the Field Settings dialog box for that particular field.
4. In the Field Settings dialog box, go to the Subtotals & Filters tab. This tab provides options for controlling the subtotals and filtering for the selected field.
5. Under the Subtotals section, select None from the dropdown menu. By default, this will usually be set to “Automatic” which displays subtotals. Choosing “None” will remove the subtotals for this field.
6. Click OK to apply the changes and close the Field Settings dialog box.

Excel will now remove the subtotal rows for the selected field in your pivot table, while keeping any other subtotals that may be present for other fields.

Method 2: Removing All Subtotals in the Pivot Table

If you want to completely remove all subtotals from your pivot table at once, regardless of which fields they are associated with, you can use this quick method:

1. Click on any cell inside the pivot table to activate it and display the PivotTable Tools tabs.
2. Go to the PivotTable Analyze or Options tab in the ribbon, depending on your version of Excel. In newer versions, it will be called “PivotTable Analyze”, while in older versions it may be labeled as “Options”.
3. In the Active Field group, locate and click on the Subtotals dropdown menu. This menu provides options for controlling subtotals at the table level.
4. From the dropdown menu, select Do Not Show Subtotals. This option will instantly remove all subtotal rows from your pivot table, leaving only the individual data rows and the grand total row.

That’s it! With just a few clicks, you can completely remove all subtotals from your pivot table, creating a cleaner and more streamlined view of your data.

Why Does Excel Include Subtotals by Default?

Excel includes subtotals by default in pivot tables because in many cases, they provide valuable summary information at a glance. Subtotals allow you to quickly see the totals for each group or category, without having to manually sum up the individual values. This can be especially helpful when working with large datasets that have many rows and multiple levels of grouping. Subtotals can help you spot trends, compare performance across categories, and get a high-level overview of your data.

However, there are also many scenarios where subtotals may not be necessary or desired. For example:

• When you want to create a cleaner, simpler view of your data without the added visual clutter of subtotal rows
• When you plan to do further analysis or calculations on the pivot table data, and the subtotals would interfere with those operations
• When you are presenting the pivot table to others and want to focus attention on the individual data points rather than the subtotals
• When you have a small dataset or a flat data structure where subtotals don’t add meaningful value

In these cases, removing the subtotals from your pivot table can be a helpful way to streamline your workflow and communicate your data more effectively.

Tips for Working with Subtotals in Pivot Tables

While removing subtotals is a common task when working with pivot tables, there are also several other subtotal-related tips and best practices to keep in mind:

Subtotals vs. Grand Totals

It’s important to understand the difference between subtotals and grand totals in a pivot table. Subtotals are the summary rows for each individual group or category, while the grand total is the final total row at the very bottom of the table that aggregates all the data across all groups. Removing subtotals will not affect the grand total row, which can still be useful for getting an overall picture of your data.

Customizing Subtotal Calculations

By default, Excel uses the SUM function to calculate subtotals for numeric fields. However, you can customize the subtotal calculation to use other functions like COUNT, AVERAGE, MIN, MAX, etc. depending on your analysis needs. To change the subtotal calculation, go to the Field Settings dialog box for the desired field and choose the appropriate function under the “Summarize Values By” tab.

Formatting Subtotals

If you decide to keep subtotals in your pivot table, you may want to format them differently to make them stand out from the regular data rows. You can do this by selecting any subtotal row, right-clicking, and choosing “Format Cells” from the context menu.

This will allow you to access options for changing the font style, size, color, background fill, borders, and more. Any formatting changes you apply to one subtotal row will automatically be applied to all other subtotal rows in the table, maintaining a consistent look.

Expanding and Collapsing Subtotals

When subtotals are displayed in a pivot table, you have the ability to expand or collapse the detail rows for each group by clicking on the small plus (+) or minus (-) symbols located next to the subtotal rows. Expanding a subtotal will show all the individual data rows that contribute to that subtotal, while collapsing it will hide those details and only display the subtotal row itself. This interactive feature allows you to quickly switch between a high-level summary view and a more detailed, granular view of your data.

Copying and Pasting Pivot Tables

If you need to share your pivot table with others or use it in another application, you may want to copy and paste it as values to remove the underlying pivot table functionality. However, be aware that copying a pivot table with subtotals can sometimes lead to unexpected results, such as blank rows where the subtotals used to be.

To avoid this, consider removing the subtotals before copying the pivot table, or use the “Paste Values” option to convert the pivot table to a regular range of data.

Final Thoughts

In conclusion, removing subtotals in an Excel pivot table is a straightforward process that can greatly improve the readability, usability, and clarity of your data. Whether you want to remove subtotals for a specific field or for the entire table, the steps outlined in this guide will help you accomplish that quickly and easily.

By understanding the purpose and behavior of subtotals, you can make informed decisions about when to include them and when to remove them based on your specific data analysis and communication needs. Additionally, the tips and best practices covered in this article will help you work with subtotals more effectively and efficiently, allowing you to get the most value out of your pivot tables.

FAQs

Vaishvi Desai

Vaishvi Desai is the founder of Excelsamurai and a passionate Excel enthusiast with years of experience in data analysis and spreadsheet management. With a mission to help others harness the power of Excel, Vaishvi shares her expertise through concise, easy-to-follow tutorials on shortcuts, formulas, Pivot Tables, and VBA.