Microsoft Excel’s subtotal feature allows you to insert automatic subtotals and totals into a data set. This can help summarize and analyze large data sets. However, once you have added subtotals, you may later decide you want to remove them. Here are the steps to delete subtotals in Excel.
Table of Contents
When You Should Delete Subtotals
There are a few reasons you may want to delete subtotal rows:
- You no longer need the summary data and want the raw data only
- You made a mistake or subtotaled the wrong data range
- The subtotals are getting in the way of other data analysis
- You want to change or update the subtotal settings
Deleting subtotals removes the automatically inserted rows while keeping your original data intact.
How to Remove All Subtotals
If you want to delete all subtotals in a worksheet, follow these steps:
- Select any cell within the data set that contains subtotals.
- On the Data tab, in the Outline group, click Subtotal. This opens the Subtotal dialog box.
- Click Remove All next to each column that shows a check mark indicating subtotals are turned on.
- Click OK.
This will remove every subtotal row in the data while keeping the raw data.
How to Remove Specific Subtotal Rows
To delete only certain subtotal rows, use these steps:
- Select any cell in the data set with subtotals.
- On the Data tab, in the Outline group, click the Group option.
- In the rows with subtotals you want to remove, click the number 1 to ungroup that row. The number will disappear.
- Once you’ve ungrouped all subtotal rows you want deleted, click the Ungroup button on the ribbon.
The subtotal rows you ungrouped will be permanently removed from the worksheet.
How to Delete Grand Total Rows
If your data set shows grand totals and you only want to remove those bottom rows, do this:
- Click any cell within the subtotaled data.
- On the Data tab, click Subtotal.
- In the Subtotal dialog box, deselect the Grand Total checkbox.
- Click OK.
This will delete only the grand total rows while keeping any other subtotals.
Tips for Managing Subtotals
- Before deleting subtotals, consider copying them to another area first in case you need to refer back to the summary data later.
- To update subtotal settings or columns, remove the existing subtotals first before adding updated ones.
- Use the collapse outline symbols to temporarily hide subtotal rows if you don’t want to permanently delete them.
- Make sure you select the correct data range before adding or removing subtotals to avoid errors.