How to Remove Subtotals in Excel: Quick Data Cleanup Method

I’ve seen spreadsheets slowed to a crawl by unnecessary subtotals. One click can clean up your data and restore performance without losing the raw numbers.

This approach works whether you added subtotals manually or inherited a file with them already embedded. The method removes the summary rows while preserving every detail in your dataset.

You’ll regain faster calculations, cleaner formatting, and the ability to sort or filter without subtotal interference. No macros, no add-ins—just built-in Excel functionality.

Applies to Excel 2013 through 365, including the web version. The same steps work for both single and multiple subtotal levels.

What Youll Need

Instructions

Here’s the most efficient way I know to strip out subtotals and restore raw data.

First, click any cell inside your dataset. Press Ctrl + A twice to select the entire range, including hidden subtotal rows. If your data has a header row, Excel will stop at the first blank row—double-check the selection outline to ensure it captures everything.

Look for the outline symbols (minus signs) on the left margin. These indicate collapsed subtotal groups. If you see them, your data has structured subtotals inserted via the Subtotal tool or manual grouping.

Go to the Data tab and click Subtotal in the Outline group. In the dialog, select Remove All. This instantly deletes all automatically inserted subtotal rows while preserving your raw data.

Verify the outline symbols disappear from the left margin. If they remain, the subtotals were likely created manually or via formulas. In that case, proceed to the next step.

If subtotals were added manually, sort your data by the column used for grouping (e.g., Category or Region). Subtotal rows will cluster at group boundaries, often with labels like “Total” or “Subtotal.”

Select these rows by clicking the row numbers on the left, then press Ctrl + – (minus key) to delete them. Confirm the dialog shows the correct number of rows to remove before clicking OK.

For subtotals created with formulas (e.g., =SUMIF()), use Find & Select (Ctrl + F) to search for “Total” or “Subtotal.” Delete or replace these formulas with the underlying data.

Check for hidden rows by pressing Ctrl + Shift + 9 to unhide all rows, then repeat the search. Some subtotals hide below visible data, especially in large datasets.

Sort the dataset by your primary column (e.g., ID or Date) to ensure no orphaned subtotal rows remain. Look for duplicate entries or rows with summary labels that might have been missed.

Finally, save a copy of the cleaned dataset. I always use Save As with a name like “OriginalData_Clean” to avoid overwriting the source file accidentally.

Tips and Common Mistakes

Here are practical ways to avoid errors and streamline the process when cleaning up your data.

The Quick Identification Method: In Step 1, look beyond the obvious “Subtotal” label. Some users miss subtotal rows because they only contain formulas without text. Scan for rows with SUM, AVERAGE, or COUNT functions—these are giveaways. I learned this the hard way after overlooking several formula-only rows in a large dataset.

Automation Shortcut: For Step 2, if you see outline symbols but aren’t sure they’re from the Subtotal tool, try this: click any outline symbol. If it collapses/expands groups of rows, it’s definitely from the Subtotal feature. This saves you from wasting time manually deleting what could be removed instantly with Remove All.

Data Integrity Check: In Step 5, the row count verification is crucial. After removing subtotals, always compare your before-and-after counts. If you started with 500 rows and removed 10 subtotals but only have 485 rows left, you’ve accidentally deleted data rows. Sort your data to spot-check for missing entries.

Error Prevention: Before recalculating in Step 5, check for any cells that might reference deleted subtotal rows. Look for formulas containing row numbers that match your removed subtotals. Updating these references beforehand prevents those dreaded #REF! errors that can cascade through your worksheet.

Frequently Asked Questions

Removing subtotals in Excel is a breeze once you know the steps! 💡 Whether you used the Subtotal tool or manual formulas, you can quickly clean up your data for a polished look. Now that your spreadsheet is subtotal-free, try exploring PivotTables for dynamic summaries—your next data adventure awaits!