If group labels are not repeated in your table, it would cause problems while filtering rows in that table. In this cause, you can use an extra (helper) column to repeat the group labels for filtering in Sheets.

New to group labels in Google Sheets?

Group labels are the group headers in the first column or subsequent columns (subgroup) in a table. If such labels are not repeated/filled/duplicated it can cause issues in filtering and other calculations based on groups.

In the below table, column A contains the group labels and column B contains the subgroup labels. Both are not repeated.

In this type of table, you may not be able to use the Filter command correctly. Suppose I want to filter all the rows for the group “Product 1”. All the rows will be filtered out except the row # 2!

Want to see that?

1. First select the columns A, B, and C.
2. Go to Data > Create a filter.
3. Then click the drop-down on cell A1 and set it as follows and see the filtered output.

If the above data is as below, I mean, item labels aka group labels are repeated, then you would be able to filter the data correctly.

In this type of data, you can use functions like Sumif, Query, etc. effectively.

You can manually copy and paste the group labels/headers but it is not an easy task. So the solution is to repeat the group labels in a helper column for filtering in Sheets. I am going to explain that in detail below.

Steps to Repeat Group Labels/Headers in Google Sheets

First I am using the helper column E to repeat the group labels in the left-most column A.

In cell E1, enter the following array formula to fill the group item names.

=ArrayFormula({"Helper"&column(A1);if(row($A$2:$A)<=MATCH(2,1/($C:$C<>""),1),lookup(row($A$2:$A),row($A$2:$A)/if(A2:A<>"",TRUE,FALSE),A2:A),)})

Before explaining this formula you should know what it does. It duplicates the labels as shown in the image below.

Copy the formula in cell E1 and paste it in cell F1.

=ArrayFormula({"Helper"&column(B1);if(row($A$2:$A)<=MATCH(2,1/($C:$C<>""),1),lookup(row($A$2:$A),row($A$2:$A)/if(B2:B<>"",TRUE,FALSE),B2:B),)})

It will duplicate the subgroup labels in column B.

Can you explain this formula for me?

Sure. Here you go – Array Formula to Fill Blank Cells With the Values Above in Google Sheets.

Filtering Groups and Subgroups in Sheets

We have repeated the group labels for filtering purpose. Now when you filter, select the columns A, B, C, D, E, and F instead of selecting the columns A, B, and C.

Do use the filter in column E and F instead of A and B. This way you can correctly filter a table that contains rows with group labels unfilled.

Here in this filter, I have filtered Product 1 and its Gr. II. So I can see the quantity of this product and its subgroup in column C. It won’t be possible without the helper columns.

My group labels for filtering are in a different column. How to modify the formula then?

Hope you have followed my formula explanation tutorial above (see the link). If not, please follow that. Here for your quick use, let me explain what changes required in the formula to make it adaptable to a different range.

How to Modify the Formula That Repeats Group Labels in Google Sheets

Take a look at the formula in cell F1. Here are the explanations to the cell references in that.

Formula 1:

row($A$2:$A)

In my sample data, the first row is the header row. That’s why I have used $A$2:$A instead of $A$1:$A in the Row formula.

Formula 2: Match to Find the Last Used Row in Google Sheets.

MATCH(2,1/($C:$C<>"")

Note: I have explained this formula in my Excel tutorial titled – Address of the Last Non-Empty Cell Ignoring Blanks in a Column in Excel. Please do note that it works similarly in Google Sheets.

When we repeat group labels for filtering, the last value (here “Gr. II” in cell B10) may get copied several rows down. We want to limit the formula expanding and limit to row # 11. The above formula returns that row number.

In our data, the 11th row contains the last value. That we can only determine from column C as there is no group in that column.

Formula 3:

B2:B

This is the column that contains the group/item labels which to be duplicated.

Formula 4:

column(B1)

It just returns the helper column header “Helper2”. It has no other role in the formula.

You can use the above formula to repeat group labels for filtering in Google Sheets.