How to Remove Subtotals from a Google Sheets QUERY

To remove subtotal rows from a Google Sheets QUERY result, filter them out in the query’s where clause—for example, exclude rows whose label is “Subtotal.” If the subtotal rows are not in the source data, QUERY did not create them: grouping produces one result per group, not an extra subtotal row.

Why subtotal rows appear in a QUERY result

A Google Sheets query operates on the data in its selected range. It can select, filter, group, sort, and aggregate those records, but it does not automatically insert Excel-style outline subtotals between groups. A group by query returns a summary row for each distinct group; those rows are the grouped results, not extra subtotals placed alongside the original detail.

When a result seems to contain unwanted subtotals, the cause is usually one of these:

The source range already includes total lines. A person, pivot table, report export, or another formula may have added rows labeled “Subtotal,” “Total,” or “Grand Total.”
The query is returning detail and summary data together. For example, the selected source range may include both transaction records and a second section of summary rows.
The grouped output is being mistaken for detail. A query that uses group by replaces multiple detail rows with a summary for each group. It does not retain the detail lines unless the query asks for them in a way compatible with grouping.

Check the source range first. If a total line is already present there, the query will treat it as a record unless its conditions exclude it. If the source contains only detail but the output has one row per category, that is likely the intended result of grouping rather than an inserted subtotal.

Filter labeled subtotal rows out of the query

The most direct fix is to add a condition for the column that identifies subtotal records. Suppose columns A:C contain Category, Item, and Amount, with headers in row 1. Regular records have an item name in column B, while subtotal records use the exact label “Subtotal.” With that layout, use:

=QUERY(A1:C,"select A, B, C where B <> 'Subtotal' and B is not null",1)

The query keeps records whose item is not labeled “Subtotal” and excludes rows with a blank item. The final argument, 1, tells Sheets that the first row of the range contains one header row. The column letters inside the query refer to columns in the sheet range; the quoted query text uses single quotes around the text value.

If the marker is in a different column, change the condition to match that column. For example, if column A contains the word “Subtotal” on total rows, use A <> 'Subtotal' instead of checking column B. The condition must refer to a field that reliably distinguishes total rows from ordinary records.

If the data has more than one kind of total line, exclude each exact label. With the same A:C layout and subtotal labels in column B, this version removes both “Subtotal” and “Grand Total” rows:

=QUERY(A1:C,"select A, B, C where B <> 'Subtotal' and B <> 'Grand Total' and B is not null",1)

Use the spelling and capitalization actually found in the data. Exact labels are safer than filtering on a broad word such as “Total,” which might also occur in a legitimate item name.

Keep totals out of grouped summaries

If the goal is one total per category, filter out source subtotal lines before grouping. For example, assume A:C still contain Category, Item, and Amount; detail rows have an item name in B, and subtotal rows have “Subtotal” in B. This query sums the detail amounts by category:

=QUERY(A1:C,"select A, sum(C) where B <> 'Subtotal' and B is not null group by A label sum(C) 'Amount'",1)

The output has one row per category and a summed Amount column. The label clause changes the displayed heading for the aggregate; it does not add or remove rows. The filter is important: if a source subtotal is included in the sum, the category total can be overstated because the query adds both the underlying detail and the already-calculated subtotal.

Do not remove the group by clause just because the grouped output looks like a subtotal report. Removing it changes the question the query answers. A grouped query summarizes records; a detail query lists records. Decide which output is needed, then filter the source appropriately.

Choose a dependable way to identify totals

A text label is convenient, but it is not always a robust marker. If a total row is identified only because its description happens to say “Subtotal,” a renamed label or a spelling variation can let it slip through. When you control the source structure, a dedicated type column is clearer. For example, column D could contain “Detail” on transaction rows and “Subtotal” on total rows. With headers in row 1 and records below, query only rows marked Detail:

=QUERY(A1:D,"select A, B, C where D = 'Detail'",1)

This method avoids guessing from an item description. It is especially useful when descriptions can vary or when the source includes several kinds of non-detail rows, such as grand totals, notes, and section headings. A stable ID or record-type field is often a better filter than a blank cell or a specific amount.

Other possible indicators include:

A known text value in a dedicated Type or Status column.
A blank identifier on all total rows, provided ordinary records always have an identifier.
A formula or export flag that explicitly marks summary records.
A separate detail-only source range that does not include report totals.

Filter on a characteristic that describes the row’s role, not merely a value that might also occur in genuine data. For instance, filtering out every row where Amount is blank can remove useful records if blank amounts are valid in the source.

Common problems and how to fix them

The subtotal remains in the output. Confirm that the condition references the column containing the marker, and compare the criterion with the cell’s actual contents. A label may contain extra spaces, different punctuation, or a longer phrase such as “Subtotal – East.” An exact inequality only excludes the exact value specified; it will not automatically match every variation.

Ordinary rows disappear too. Check whether the chosen column is blank in valid records. In the first example, and B is not null deliberately excludes blank item cells. Remove that part if blank items should remain, or use a more reliable record marker. Also check that you did not accidentally test the wrong column.

The output is empty or shows an error. Check the range, header count, quotation marks, and query syntax. The example formulas use a header row in row 1 and therefore end in ,1. If your selected range has no header row, use ,0; if the number of header rows is not known, the third argument can be omitted, though specifying it makes the intended layout clearer. Google Sheets uses commas between formula arguments in the examples shown here.

The totals are too large after filtering. Make sure all source subtotal and grand-total rows are excluded before applying sum. Otherwise, the query can add a precomputed total to the detail values that produced it. Also verify that the source range does not include a second copy of the same records.

The result has one row per group, not transaction-level rows. That is expected when the query uses group by. Use a detail-level select query without aggregation if you need to display each transaction. Filtering out subtotals and choosing between detail and grouped output are separate decisions.

Remove totals without changing the source data

A query is useful when you want a clean report while leaving the original table intact. Put the formula in an empty area or on a separate sheet, and use the filtered result for charts, summaries, or downstream calculations. The query output updates when the referenced source data changes, subject to the range included in the formula.

You can also remove rows from the source manually, but that changes the underlying data and can affect other reports. If the total rows are useful for a printed report, keep them in the source and make a separate detail-only query. If the rows are accidental duplicates, correct the source or the process that creates it rather than hiding them in just one report.

For a one-time inspection, Google Sheets’ filter controls can temporarily hide matching rows. In Sheets in a web browser, select the data and use Data > Create a filter, then use the filter on the marker column to hide subtotal labels. A filter changes what is displayed, not the underlying records, and it does not rewrite a separate query formula.

How Google Sheets QUERY differs from Excel

QUERY is a Google Sheets function, not an Excel worksheet function. The formula examples here are for Google Sheets in a web browser and use the Google Visualization query language inside the formula’s text argument. Excel users may use filters, formulas, PivotTables, or Power Query to achieve a similar result, but the syntax and menu steps are not interchangeable with Sheets’ QUERY.

That distinction matters when copying formulas between programs. Excel will not understand a Sheets QUERY formula simply because both applications work with tables. Conversely, a Google Sheets query condition is not an Excel filter criterion. If a workbook is shared across both applications, choose a method supported by the intended destination or keep separate versions of the reporting formula.

Frequently Asked Questions

Does Google Sheets QUERY create subtotal rows automatically?
No. QUERY does not insert subtotal rows between groups. A grouped query returns aggregate results for its groups; apparent extra totals usually come from records already in the selected range or from another part of the sheet.

How do I exclude a row labeled Grand Total?
Add a where condition that excludes the column containing that exact label. For example, if column B holds the label, use B <> 'Grand Total' inside the query text. If there are several total labels, exclude each one or use a dedicated row-type field.

Can I hide subtotal rows without deleting them?
Yes. A query can return a filtered view in another area while preserving the source rows. You can also use a sheet filter to hide matching rows temporarily; hiding rows does not remove their values from the source.

Why does my query include a subtotal in the sum?
The subtotal is probably part of the source range and has not been filtered out before aggregation. Exclude summary records in the where clause before using sum, or point the query at a detail-only range.

What if subtotal rows have no consistent label?
Add or use a dependable marker, such as a Type column that distinguishes Detail from Subtotal. If you cannot change the source, identify a field that reliably differs on total rows and check that the rule does not exclude valid detail records.

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.