How to Delete Subtotal Rows in Excel Without Losing Data

To remove subtotals created with Excel’s Subtotal command, click a cell in the list, choose Data > Subtotal, then select Remove All. Excel removes the generated subtotal rows and outline levels while keeping the original detail records. If the rows were typed or added manually, identify them first and delete only those worksheet rows.

Before you begin, save a copy of the workbook or make sure you can undo the change. The right method depends on how the subtotal rows were created: Excel-generated subtotals have a built-in removal command, while manually added rows need to be deleted like other rows.

What You’ll Need

The workbook open in the Excel desktop app, preferably Excel for Microsoft 365 on Windows.
Permission to edit the worksheet. If it is protected, you may need the password or the workbook owner’s help.
A saved backup or a recent copy of the file, especially if you are unsure whether the totals are generated or manual.
For manual subtotal rows, a consistent label or another clear way to distinguish them from detail records.

Step-by-Step

Save a working copy. Use File > Save As to create a copy if the data is important or shared. Removing generated subtotals is generally reversible with Undo, but once you make additional edits or save and close the file, a backup is the safer fallback. Avoid deleting anything until you have confirmed which rows contain details and which contain totals.
Check whether Excel created the subtotals. Click a cell in the list and look at the Data tab. If the Subtotal command is available, the list may have been subtotaled using Excel’s built-in feature. The worksheet may also show outline level buttons, commonly numbered 1, 2, and 3, along the left side above the row numbers. Those buttons let you expand or collapse detail and summary levels; they are a useful clue, but not proof on their own. Manually added rows can also be grouped or formatted to look similar.
Remove Excel-generated subtotals. Select any cell inside the subtotaled list, then choose Data > Subtotal. In the Subtotal dialog box, click Remove All. Excel removes the subtotal formulas and the outline structure it created. The original data rows remain in place. You do not need to select the subtotal rows individually, and selecting only the visible summary rows is not a substitute for this command.
Check the result and the remaining data. Scroll through the list and confirm that subtotal lines have disappeared while the original records are still present. If you had collapsed the outline, the detail rows might have been hidden before removal; after the outline is removed, review the full list. Check the first and last records and compare the row count or a known record against your saved copy if accuracy matters. Also inspect any grand total you added separately, since a manually entered total is not necessarily part of Excel’s Subtotal feature.
If the rows are manual, identify them before deleting. Look for a reliable marker such as the word Total in a label column, a distinctive description, or a formula used only in those rows. Do not rely on bold text, shading, blank rows, or a particular number format alone: ordinary records may use the same formatting. If you are uncertain, inspect the formula bar for a suspected total row and compare it with a nearby detail row. A subtotal row might contain a SUM formula or a SUBTOTAL formula, but either function can also appear in legitimate calculations elsewhere.
Delete a small number of manual rows individually. Use Ctrl+F on Windows to find a distinctive label, check that the selected result is actually a subtotal row, and close the Find box. Click that row’s number at the left edge to select the entire worksheet row. Choose Home > Delete > Delete Sheet Rows. Repeat for each confirmed subtotal row. On Mac, use Command+F to search; ribbon labels and dialog placement can differ slightly, but select the row header and use the worksheet row-delete command. Deleting the entire row is important when the total spans several columns, because clearing only one cell can leave stray values behind.
For many manual rows, filter using a dependable label. If every subtotal row has a unique label in one column, turn on filters with Data > Filter, open that column’s filter menu, and show only the subtotal label. Verify that the filtered results contain no detail records. Select the visible row headers for the confirmed subtotal rows, then choose Home > Delete > Delete Sheet Rows. Clear the filter afterward and inspect the surrounding records. If there is no unique marker, do not filter on formatting or guess from blank lines; add a temporary review column or remove rows individually so that detail data is not caught in the selection.
Check formulas, totals, and saved changes. After deleting rows, look for formulas elsewhere that refer to the removed rows or expect a particular range. Recalculate if needed by pressing F9 on Windows; on Mac, the recalculation shortcut can depend on keyboard settings. Review any charts, summaries, or reports based on the list. Save the workbook only after the remaining records and results look right.

Common Mistakes

Deleting detail records along with totals. A subtotal line often sits between groups of valid records, so selecting a broad block of rows can remove both. Confirm each target row by its label or formula before deleting it.
Using Remove All on manually typed totals. The command removes subtotals created by Excel’s Subtotal feature. It will not reliably identify and remove rows someone inserted and filled in by hand. If nothing changes, use the manual-row method instead.
Removing a grand total that should stay. A worksheet may contain an overall total outside the generated subtotal structure. Check whether that row is part of the original data, a separate report calculation, or a manually entered summary before removing it.
Clearing cells instead of deleting rows. Pressing Delete or choosing Clear Contents removes cell contents but leaves the row in the worksheet. That can leave blank gaps or partial totals. For a confirmed manual subtotal, delete the whole sheet row.
Assuming formatting proves a row is a subtotal. Bold text, borders, or colored fills are easy to copy onto ordinary rows. Use the row’s label, position, and formula together rather than styling alone.
Deleting filtered results without checking the filter. A filter can hide records that do not match the displayed value. Confirm the visible labels before deleting, then clear the filter and review the full dataset.

Tips

Expand the outline before reviewing. If numbered outline controls appear at the left, select the highest level number to show the detail rows before checking what remains. The controls change what is visible, not what is deleted.
Keep a copy of the original range. You can duplicate the worksheet by right-clicking its tab and choosing Move or Copy, then selecting Create a copy. This gives you a reference if a manual deletion removes the wrong row.
Sort consistently before creating subtotals in the future. Excel’s Subtotal feature groups records based on changes in a selected field. Sorting by that field first makes the groups predictable. This is preparation for adding subtotals, not a requirement for removing them.
Use Undo immediately if the result is wrong. Press Ctrl+Z on Windows or Command+Z on Mac as soon as you notice an unwanted deletion. Undo is most dependable before you perform unrelated edits; a separate saved copy is better protection for later.
Remember that removing subtotals is not the same as removing duplicates. If repeated detail records remain after the subtotal rows are gone, they are still data rows. Review them separately rather than deleting them as part of this cleanup.

Troubleshooting

Remove All is unavailable or dimmed. First click a cell inside the list and check that you are on the correct worksheet. The command may be unavailable when the selected data is formatted as an Excel table. If appropriate, select a cell in the table, open Table Design > Convert to Range, and confirm the change; then try Data > Subtotal again. Converting a table to a range removes table features, so keep a copy and consider whether filters, structured references, or table formatting are needed before converting.

The command runs, but the rows remain. The totals may have been typed manually, pasted from another source, or created with formulas without using Excel’s Subtotal command. Check a suspected row’s label and formula, then use the manual deletion method. Also make sure the worksheet is not protected and that you are working in the sheet containing the subtotaled list.

Only some totals disappear. Some rows may be generated subtotals while others were added separately. Remove the generated subtotals with Remove All, then inspect any remaining summary or grand-total lines and decide whether they are manual. Do not repeatedly run delete operations without checking the data between them.

Rows seem missing after removal. Check whether a filter is active or whether rows are hidden. Use Data > Clear to clear worksheet filters, if appropriate, and inspect the row numbers for hidden gaps. The Subtotal command’s outline can hide detail rows when collapsed; removing the outline should restore them, but a separate filter or manual hide setting can still conceal records.

You deleted the wrong rows. If the deletion just happened, use Undo immediately. Otherwise, reopen the backup or use version history if the workbook is stored in a location that supports it. Avoid trying to reconstruct lost records from neighboring rows unless you can verify them against the original source.

You are using Excel for the web. The web version may not provide the same Subtotal dialog and outline controls as the Windows desktop app. Open the workbook in the desktop app to use Data > Subtotal > Remove All. If you must work in the browser, use a cautious manual method only when you can clearly identify the rows, and confirm that row deletion is available in your version.

Frequently Asked Questions

Does Remove All delete the original data?
It removes the subtotal calculations and outline levels created by Excel’s Subtotal feature, not the underlying detail records. Still, review the list afterward, especially if the sheet was filtered or collapsed.

Can I remove only one of several generated subtotal groups?
The built-in Remove All command removes the generated subtotal structure as a whole rather than offering a simple one-group removal option. If you need to keep selected summary rows, save a copy and decide whether to recreate the summaries you want after removing the existing structure.

Will Remove All delete a manually added grand total?
Not necessarily. It removes the subtotals created through Excel’s Subtotal command. A grand total typed or calculated in a separate row may remain, so inspect it and remove it manually only if it is no longer needed.

Why can’t I find the Subtotal command?
Click inside the relevant range and check the Data tab. The command may be unavailable because the range is an Excel table, the sheet is protected, or you are using Excel for the web. Convert a table to a range only if losing table features is acceptable, or open the workbook in desktop Excel.

Can I delete subtotal rows without affecting formulas?
Deleting rows can change formulas that refer to them or include them in a range. Excel adjusts many direct references automatically, but do not assume every report or external link will remain correct. Check dependent formulas, charts, and summaries after the deletion.

Is hiding subtotal rows the same as deleting them?
No. Hiding a row only changes whether it is visible; its values and formulas remain in the worksheet. Use Remove All for Excel-generated subtotals or delete confirmed manual rows when you want them removed.

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.