To highlight filtered output in a table in Google Sheets, we can use Row and Regexmatch with the Filter Formula in question.

In my point of view, this an important tip because of the latest filter by color in the Google Sheets feature. Why?

We prefer the Filter function or the Query function to the Data menu > Filter as the former has the ability to extract the rows to a new range.

Further, in the Filter or Filter views menu command, in case of multiple conditions in different columns, one needs to apply the conditions in each column and it’s a time taking process.

Now let me tell you why the highlighting of a filter formula output is an important tip.

If we can highlight the rows in a table based on the filter formula output, then we can use the Data menu filter by color to those rows.

So no need to extract the rows to a new range. Didn’t get? Let me explain this concept first then we can move to the formula rule for highlighting.

Example to Highlight Filtered Output in a Table in Google Sheets:

Here are the conditions/criteria for filtering and highlighting.

A1:A="Product 1"
C1:C="Ind. Area I"

A filter formula based highlighting rule highlights the corresponding rows in the table. When I change the condition C1:C="Ind. Area I", the highlighting shifts rows accordingly.

Later if you want you can use the new Data menu Filter by a color option to filter these highlighted rows.

How to Highlight Filtered Output in a Table in Google Sheets

Please follow the step-by-step instructions under the sub-titles below.

Include Row Numbers Corresponding to Filtered Rows

Let’s consider the data in A1:D. To filter this table as per the earlier conditions we can use the below Filter formula.

Step 1 Formula (we will be modifying this formula in each step):

=filter(
     $A$2:$D,$A$2:$A=$F$2,$C$2:$C=$G$2
)

I am not explaining this formula. I hope you know how to use the function Filter.

If not, please check my tutorial – How to Use the Filter Function in Google Sheets [Basic and Advanced Use].

The above formula would return the below rows (values in H2:K4) from the table.

To get corresponding row numbers along with the filtered output, modify the filter formula as below.

Step 2 Formula:

=filter(
     {row($A$2:$A),$A$2:$D},$A$2:$A=$F$2,$C$2:$C=$G$2
)

Our table is in $A$2:$D. That is the filter ‘range’ in the first filter formula.

Syntax: FILTER(range, condition1, [condition2, …])

But in the just above step 2 formula, the ‘range’ is {row($A$2:$A),$A$2:$D}. So we will get a row number column as the front column in the filter formula output.

Extract Row Numbers Using Array_Constrain

To highlight filtered output in the table (range A1:D) in Google Sheets, as per my example, what we want to do is to highlight the rows 2, 4, and 6 (see column H), right?

So let’s extract the row number column from the filter formula output first.

Step 3 Formula:

=array_constrain(
     filter(
        {row($A$2:$A),$A$2:$D},$A$2:$A=$F$2,$C$2:$C=$G$2
     ),
     9^9,1
)

The above Array_constrain extracts an arbitrary number of rows (9^9) and 1 column.

Creating Conditional Format Rule to Highlight Filtered Output Rows in Google Sheets

We are almost set! Now to highlight the above Filter formula output in the Google Sheets table above, we need to format the numbers as a regex regular expression.

Yes! We are going to Regexmatch the row numbers in the range A1:D.

Syntax: REGEXMATCH(text, regular_expression)

Here is the regular expression.

Step 4 Formula:

="^"&
     textjoin(
        "$|^",true,
        array_constrain(
           filter(
              {row($A$2:$A),$A$2:$D},$A$2:$A=$F$2,$C$2:$C=$G$2
           ),
           9^9,1
        )
   )
&"$"

Result: ^2$|^4$|^6$

I have used the TextJoin to combine the row numbers returned by the step 3 formula.

Syntax: TEXTJOIN(delimiter, ignore_empty, text1, [text2, …])

The ^|$ signs inserted between the row numbers as the delimiter (separator).

The Pipe acts as the OR condition in regex. The Caret and Dollar are for exact match.

If 2|4|6 is the regular expression, then 2 in the formula would match the row numbers 2, 12, 21, 22, and so on. That is the case with 4 and 6.

You can hopefully understand the above step 4 formula by the below generic formula.

Generic Formula:

="^"&textjoin("$|^",true,step_3_formula)&"$"

Let’s move to the final step to write the formula to highlight filtered output in a table in Google Sheets.

Regexmatch Row Numbers in Conditional Formatting

In conditional formatting we just need to match the row numbers for the range A1:D.

Generic Formula:

=regexmatch(row($A1)&"",step_4_formula)

Why I have used row($A1)&"" instead of row($A1)?

I know you may pay your attention to such minute things and I should clarify it.

The first argument in Regexmatch must be text. So we must format the row number to text. By adding a null character we can do that and that is what I have done.

Here is the final formula as per the Generic Formula version above.

=regexmatch(
     row($A1)&"",
     "^"&
        textjoin(
           "$|^",true,
           array_constrain(
              filter(
                 {row($A$2:$A),$A$2:$D},$A$2:$A=$F$2,$C$2:$C=$G$2
              ),
              9^9,1
           )
        )
     &"$"
)

Can you help me to apply this formula in Conditional formatting?

Why not?

1. Select A1:D.
2. Go to the Format menu and click “Conditional formatting”.
3. Insert the formula in Format rules > Custom formula field.

You have learned how to highlight filtered (formula) output in a table in Google Sheets.

Other than highlighting matching rows in a table, it has one more benefit. See that below.

Multi-Column Conditions in a Single Column in Data Menu > Filter in Google Sheets

We can now filter the highlighted rows using Filter by fill color in the Data menu Filter in Google Sheets. Here are those steps.

1. Select A1:D
2. Data > Create a filter.
3. Click the drop-down in cell A1, B1, C1, or D1.
4. Click Filter by color > Fill color > select_the_color_used_for_highlighting.

Normally when you want to get the same filtered output without highlighting, you may need to filter column A for “Product 1” and then column C for “Ind. Area I”.

Highlighting and then applying a filter by fill color is equal to applying multi-column conditions in a single column in the Data menu Filter command.

Further, the filter formula lets us apply more complex conditions in highlighting. That’s all about highlight filtered output in a table in Google Sheets. Enjoy!