Exclude Hidden Rows in Excel GROUPBY

The GROUPBY function in Excel is a total game-changer. It lets you group and summarize data in seconds — no complex formulas or pivot tables needed. But here’s the catch — the GROUPBY function doesn’t automatically exclude hidden rows in Excel.

If you’ve filtered your data or manually hidden a few rows, you’ll notice they still show up in your results. Unfortunately, there’s no checkbox or built-in option to fix that.

You might have tried old tricks like SUBTOTAL or AGGREGATE as direct aggregation functions, but they don’t work the same way inside GROUPBY. That’s because GROUPBY uses a modern dynamic array engine, which handles calculations differently.

The good news is that you can still use SUBTOTAL — not as a direct aggregation, but inside a MAP/LAMBDA formula to create a helper array that marks visible rows. We’ll use this helper array in the filter_array part of the function. This is exactly what allows you to filter out hidden rows and get accurate summaries.

Table of Contents

GROUPBY Function Syntax

Before we dive into examples, here’s the basic syntax of the GROUPBY function (highlighting the arguments we’ll use in our examples):

GROUPBY(row_fields, values, function, [field_header], ..., ..., [filter_array], ...)

row_fields: column(s) to group by
values: column(s) to aggregate
function: aggregation functions like SUM, AVERAGE, etc.
field_header: optional headers
- Missing → Automatic (default)
- 0 → No
- 1 → Yes, but don’t show
- 2 → No, but generate
- 3 → Yes, and show
filter_array: optional Boolean array to include only visible rows (used to exclude hidden rows)

Sample Data for Grouping

Consider the following Material Shipping Records:

Sample material shipping records used to demonstrate Excel GROUPBY function — Sample dataset of material shipping records used in this tutorial to demonstrate the GROUPBY function in Excel.

We’ll use this dataset to demonstrate how to exclude hidden rows in Excel’s GROUPBY function.

Note: Make sure your table has at least one column without blank cells. If not, simply add a helper column with sequence numbers — then you’re ready to go.

How to Use GROUPBY with Visible Rows

With the sample dataset above, you can group data in multiple ways:

By Date to get date-wise summaries
By Truck # to get vehicle-wise totals
By Item to summarize materials
By combinations like Date + Truck # for date-wise vehicle summaries, or Truck + Item for vehicle-wise item summaries

Basically, you can use one or more columns in the row_fields argument depending on what you want to summarize.

In this tutorial, we’ll cover two examples of excluding hidden rows:

Single row field
Two row fields

Preparing for the Filter Array

The first step is to identify a column in your data with no empty cells. In our example, all columns have values, so we can use column A (Date) — range A1:A14.

If your data doesn’t have such a column, create a helper column (e.g., column E) and fill it with sequence numbers or any values. You can label the header “Helper”.

Next, use this formula in the filter_array (7th argument) of GROUPBY:

MAP(A1:A14, LAMBDA(row, SUBTOTAL(103,row)))=1

If you’re using a helper column, replace A1:A14 with your helper range (e.g., E1:E14).

This formula (except the final =1) creates a Boolean array where 1 = visible and 0 = hidden, which we can feed into GROUPBY.

Example 1 – GROUPBY with a Single Row Field (Regular + Hidden Rows Excluded)

A basic GROUPBY formula to sum quantities by truck number looks like this:

=GROUPBY(Sheet1!B1:B14, Sheet1!D1:D14, SUM, 3)

Sheet1!B1:B14 → row_fields (Truck #)
Sheet1!D1:D14 → values to aggregate (Quantity)
SUM → aggregation function
3 → field_header (data has headers)

Result:

Excel GROUPBY example with single row field showing total quantity by truck number — GROUPBY formula summarizing total quantity by truck number (before excluding hidden rows).

To exclude hidden rows in Excel GROUPBY, simply add the filter_array as the 7th argument:

=GROUPBY(Sheet1!B1:B14, Sheet1!D1:D14, SUM, 3,,, MAP(Sheet1!A1:A14, LAMBDA(row, SUBTOTAL(103, row)))=1)

Now the summary includes only visible rows.

Example 2 – GROUPBY with Two Row Fields (Visible Rows Only)

The 7th argument stays the same, regardless of the number of row fields. For instance, to group by Truck # and Item:

=GROUPBY(Sheet1!B1:C14, Sheet1!D1:D14, SUM, 3,,, MAP(Sheet1!A1:A14, LAMBDA(row, SUBTOTAL(103, row)))=1)

We’ve just replaced B1:B14 with B1:C14 to include two row fields. Everything else stays the same.

Animated Excel GROUPBY example with two row fields excluding hidden rows using the filter_array argument — Animated example showing how GROUPBY updates dynamically to exclude hidden rows when grouped by Truck and Item.

This gives a visible-only summary by truck and item.

FAQs

Q1: Will this work with filtered tables?
Yes! The MAP + SUBTOTAL(103) trick detects both manually hidden rows and filtered rows, ensuring your GROUPBY summaries only include visible data.

Q2: Can I sum multiple columns at once?
Absolutely. You can adjust the values argument to include multiple columns, and GROUPBY will aggregate them accordingly.

Q3: Can I perform different aggregations at once?
Yes. You can use multiple functions in the function argument, for example using HSTACK(SUM, AVERAGE) to calculate both sums and averages in one formula.

Conclusion

The GROUPBY function in Excel is powerful, but if you want to exclude hidden rows, use the filter_array argument with the MAP + SUBTOTAL(103) trick — it’s the cleanest way to create visible-only summaries.

Once you get the hang of it, you can create dynamic, visible-only summaries across multiple row fields — saving time and avoiding manual errors.