To highlight filtered output in a table in Google Sheets, there is a powerful and efficient method that involves using the Row and Regexmatch functions in conjunction with the Filter formula. This technique allows you to easily highlight specific rows in a table based on certain criteria.
Why is this important?
Highlighting the rows in a table based on the filter formula output is an essential tip for several reasons. Firstly, it eliminates the need to extract the rows to a new range, unlike the Data menu filter by color feature. This saves time and effort. Secondly, it allows you to use the Data menu filter by color option directly on the highlighted rows, simplifying the filtering process even further.
Here’s an example to help illustrate this concept:
In this example, we have a table with certain conditions or criteria for filtering and highlighting. By using a filter formula based highlighting rule, we can highlight the corresponding rows in the table. When the conditions change, the highlighting shifts accordingly.
How to Highlight Filtered Output in a Table in Google Sheets
To achieve this highlighting effect, follow these step-by-step instructions:
Include Row Numbers Corresponding to Filtered Rows
Assuming our data is located in the range A1:D, we can use the following filter formula:
=filter($A$2:$D, $A$2:$A=$F$2, $C$2:$C=$G$2)This formula returns the filtered rows from the table. To include the corresponding row numbers along with the filtered output, modify the formula as follows:
=filter({row($A$2:$A), $A$2:$D}, $A$2:$A=$F$2, $C$2:$C=$G$2)By using {row($A$2:$A), $A$2:$D} as the range, we obtain a column of row numbers as the front column in the filter formula output.
Extract Row Numbers Using Array_Constrain
To highlight the filtered output rows in the table (range A1:D) in Google Sheets, we need to extract the row number column from the filter formula output. Use the following formula:
=array_constrain(filter({row($A$2:$A), $A$2:$D}, $A$2:$A=$F$2, $C$2:$C=$G$2), 9^9, 1)This formula extracts an arbitrary number of rows and one column.
Creating Conditional Format Rule to Highlight Filtered Output Rows
To format the numbers as a regex regular expression and highlight the filtered output, use the following 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)) & "$"The above formula combines the row numbers returned by the previous step into a single string, with “^|$” signs inserted as the delimiter.
Regexmatch Row Numbers in Conditional Formatting
Finally, we need to match the row numbers for the range A1:D in conditional formatting. Use the following formula:
=regexmatch(row($A1) & "", step_4_formula)Make sure to replace step_4_formula with the formula obtained from the previous step.
Apply this formula in conditional formatting by selecting the range A1:D, going to the Format menu, clicking “Conditional formatting,” and inserting the formula in the Custom formula field.
By following these steps, you now know how to highlight filtered (formula) output in a table in Google Sheets. This technique not only allows for easy highlighting but also enables the application of multi-column conditions in a single column using the Data menu filter by color feature.
To summarize, highlighting filtered output rows in Google Sheets tables can simplify data analysis and visualization. By using the powerful combination of the Filter, Row, and Regexmatch functions, you can efficiently manipulate and highlight specific rows in your tables. Enjoy exploring the possibilities and making your data more visually appealing and informative!
For more tips and tricks on Google Sheets and data analysis, visit Crawlan.com.
