Remove Subtotals in Google Sheets Filters and Views

Google Sheets filters do not add subtotal rows, so the right fix depends on where those rows come from. For a pivot table, open its editor and clear Show totals for the relevant row or column field; for subtotal rows already in a regular data range, filter them out using a label or helper column. These approaches hide or exclude rows from view without necessarily deleting the underlying data.

First identify what is creating the subtotal

A row labelled “Subtotal” or “Grand Total” can look like a filter result, but filtering and subtotals are separate features. A standard filter hides rows that do not meet your conditions; it does not insert grouped totals. The subtotal may instead be part of a pivot table, a formula someone entered, or a row that was typed into the source data.

Check the sheet before changing anything:

Pivot table: Click a cell in the summary. If the Pivot table editor opens, its settings control the displayed totals.
Formula row: Select the total cell and look at the formula bar. A formula such as =SUBTOTAL(9,B2:B20) identifies a subtotal formula.
Ordinary data row: If the row contains a label and values but no subtotal formula, it is probably a manually entered row in the source range.

This distinction matters. Removing a pivot-table total is different from removing a source-data row, and deleting the wrong row can affect later calculations or reports.

Turn off totals in a pivot table

Pivot tables can display totals for their row and column groupings. To change them, click inside the pivot table. The Pivot table editor appears at the right of the browser window; if it does not, double-click a pivot-table cell. Under Rows or Columns, find the field whose totals you want to hide and clear its Show totals checkbox.

There may be more than one grouping field. For example, a sales pivot could have Region and Salesperson under Rows. If the unwanted total is associated with one of those groupings, adjust that field’s setting rather than removing the field itself. Turning off totals changes the pivot table’s presentation; it does not remove matching records from the source data.

After changing the setting, check the result at each grouping level. A pivot table can contain detail rows, group-level totals, and an overall total, so the row that looks like a subtotal may not be controlled by the field you first expect. If you still see a total, inspect the other row and column fields and confirm that the pivot table’s source range does not include a manually inserted total row.

Filter out subtotal rows in a regular range

If subtotal rows are mixed into a normal table, use a column that reliably identifies them. Select a cell in the data range and choose Data > Create a filter. Open the filter menu in the identifying column, then use the values list to deselect the subtotal label. This hides those rows while leaving the records in the sheet.

For example, suppose a report has “East,” “West,” and “Subtotal” in a Region column. In that column’s filter menu, deselect “Subtotal” and apply the filter. If the total row is labelled “Grand Total,” deselect that value too. This is quick when labels are consistent and each summary row has an unambiguous marker.

Be cautious about filtering by a broad text condition such as “text does not contain Total.” It could also hide a legitimate category or customer whose name includes that word. Check the filtered results before using them in a report or copying them elsewhere.

Use a helper column when labels are inconsistent

If labels vary or subtotal rows are recognizable by more than one exact value, add a temporary helper column. With row labels in A2:A and the helper formula entered in B2, use:

=OR(A2="Subtotal",A2="Grand Total")

Fill the formula down the helper column. It returns TRUE when the label in column A is exactly “Subtotal” or “Grand Total,” and FALSE for other labels. Then create or use a filter and show only FALSE in column B. Adjust the two text labels in the formula to match the wording in your sheet.

This exact-match method avoids catching unrelated labels that merely contain the word “total.” If the summary rows are identified by a blank ID, a particular code, or a consistent formatting convention, a helper column can check that condition instead. Keep the helper column until you have checked the output; remove it afterward only if you no longer need it for refreshes or future filtering.

Understand what SUBTOTAL does

A formula using SUBTOTAL calculates a result over a range; it does not create a filter or a separate subtotal row by itself. A user places the formula in a cell, and the cell displays the result. For example, a subtotal formula above a list may update when a filter hides some of the list’s rows. That can make it seem as if the filter created the total, when the formula was already there.

Google Sheets’ SUBTOTAL function is designed to work with filtered data: rows excluded by a filter are not included in its calculation. Its first argument selects the operation, and its remaining argument or arguments specify the range. If you want the result cell gone, hiding rows is not the same as removing the formula. You must edit or clear the formula cell, or change the report design, while checking whether other calculations depend on it.

Also distinguish a live subtotal from a fixed number. A typed value will not recalculate when a filter changes. If the result stays unchanged after filtering, it may be a fixed value, a formula that does not use the visible-row behavior you expect, or a total calculated in another part of the sheet.

Choose between a filter and a filter view

A standard filter changes what is visible to people using that sheet. If several people need different views of the same data, a filter view is often less disruptive: choose Data > Filter views > Create new filter view, then set the criteria there. Filter views let users apply their own filtering without continually replacing the regular filter settings used by others.

Use either kind of filter when you need to inspect or temporarily exclude subtotal rows, not when you need to permanently clean the dataset. Filters can make hidden records easy to overlook, especially if someone later copies a range or assumes the visible rows are the complete source. Give the view a descriptive name, such as “Detail rows only,” if the filter view will be reused.

Common problems and safe checks

The subtotal label is not in the filter list: Check that the filter range includes the full table and that the label is in the column you opened. Remove and recreate the filter if its range was set incorrectly.
Some summary rows remain: The sheet may use multiple labels, such as “Subtotal – East” and “Grand Total.” Add each label to the exclusion criteria or use a helper column that recognizes all the intended cases.
Rows disappear unexpectedly: Reopen the filter menu and inspect the selected values or conditions. A broad condition may exclude valid data as well as subtotals.
The total changes after filtering: Check whether the result is a SUBTOTAL formula, a pivot-table total, or a fixed value. Each behaves differently.
The source data appears to be missing: Clear the filter to show all rows. Filtering normally hides rows; it does not delete them.

Before deleting any subtotal rows, make a copy of the sheet or confirm that the rows are not used by formulas, charts, or another report. If the totals are part of a pivot table, change its settings rather than deleting individual displayed cells, because pivot results are generated from the source data.

When to remove rows instead of filtering

Sometimes subtotal rows are accidental clutter in a dataset that should contain only individual records. In that case, filtering them out is a useful first step for review, but it does not clean the underlying range. Confirm that each identified row is truly redundant, then remove the rows themselves if that is the intended permanent change. Avoid deleting rows solely because they contain the word “Total”; check their values and formulas first.

For recurring reports, a better structure is usually to keep raw records in one table and generate summaries separately with a pivot table or formulas. This avoids mixing detail records and summary rows in the same source range. Filters then operate consistently on the raw records, and totals can be displayed or hidden in the summary without changing the dataset.

Frequently Asked Questions

Does Google Sheets add subtotals when I turn on a filter?

No. Data > Create a filter lets you hide rows based on criteria; it does not insert subtotal rows. Look for a pivot table, a formula, or a manually added row if totals appear in the sheet.

How do I remove the Grand Total row from a pivot table?

Click inside the pivot table to open the Pivot table editor. Under the relevant Rows or Columns field, clear Show totals. Check the other fields too if the displayed total is controlled by a different grouping.

Will filtering out a subtotal delete it?

No. A filter hides rows that do not match its criteria. Clear the filter to see them again. To remove a row permanently, delete it from the source sheet only after confirming it is not needed.

Why does my subtotal change when I apply a filter?

The result may come from a SUBTOTAL formula, which is intended to calculate over filtered data. A fixed typed value will not update in the same way, and a pivot-table total is controlled by pivot settings.

Can I hide subtotal rows without changing other people’s filter?

Use a filter view when you want a separate view of shared data. Create one through Data > Filter views > Create new filter view, then exclude the subtotal labels within that view.

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.