In this article, we will discuss two easiest methods to remove subtotals in Excel. Basically, we use the subtotal option in excel to organize and group data. Later, while working with various spreadsheets, we also have to delete these subtotals.
Download the Practice Workbook
You can download the practice workbook that we have used to prepare this article.
2 Most Common Ways to Remove Subtotals in Excel
1. Delete Subtotals from a List of Data in Excel
In this method, we will work on a simple list of data that is not the output of any other process. Interestingly, the process of deletion of the subtotals is very similar to the one related to the formation. So, let us go through the process:
- Initially, assume that we have the following dataset; containing subtotals of the data. Now, select a cell from this dataset.
- Then, go to Data > Outline group.
- From the Outline group, choose Subtotal.
- Then, the Subtotal window will show up. Now, click Remove All.
- Finally, you will get the dataset free of subtotals.
Sometimes, people show subtotals manually; such as by inserting rows one by one. Unfortunately, in such cases, the regular subtotal removal process will not work out. Luckily, you can use Excel’s Filter option there. So, the steps involved are:
- Firstly, select the heading of the dataset.
- Secondly, go to Data > Filter.
- Thirdly, type ‘total’ or whatever common name is given in the subtotal rows and click OK.
- As a consequence, you will get the subtotal rows only.
- Then, delete those rows with subtotals.
- Lastly, Clear the Filter, you will get the below result.
2. Remove Subtotals from Pivot Tables in Excel
In some cases, we have subtotals present in Pivot Tables. So, now, we will discuss how to delete those subtotals. In our example, we have prepared a Pivot Table from a given dataset. Removing subtotals from Pivot Table is pretty much simple. Let’s a look at the procedures:
- At first, select a cell in the Pivot Table to show table options.
- Then, go to PivotTable Analyze > Field Settings.
- The Field Settings window will pop up. Now, choose None and click OK.
- In the end, here is the table without the subtotals.
You can delete subtotals from the Pivot Table Design option too. Steps Involved are:
- After selecting the table cell, go to Design > Subtotal.
- Then select the Subtotals menu and select, Do Not Show Subtotals.
- Finally, you will get the following result.
In the above discussion, I have shown very simple ways to remove subtotals. Hopefully, these methods will help you to solve problems regarding the deletion of subtotals. However, if you have any questions or comments related to the methods described here, please let me know.