How to highlight/conditional format multiple whole tables in Google Sheets?

I have several tables horizontally in a sheet. I just want to highlight/conditional format all those tables in which the first cell contains a particular value.

The tables are of the same size. I mean having the same number of rows and columns. Here is one example.

As you can see there are three tables horizontally. I want to highlight those tables having the value “fruits” in the very first cell.

Since tables 1 and 3 match the condition, the whole cells (range) in these two tables got highlighted.

You can include more than one condition. I mean you can highlight multiple tables having different values in the first cell.

For example highlight all the tables having the value either “fruits” or “vegetables” in the very first cell. I’ll include these additional tips at a later stage of this tutorial.

Let me explain how to highlight multiple whole tables as above in Google Sheets.

Formulas to Conditionally Highlight Multiple Tables in Google Sheets

There are two methods. How they are different?

In the first method, which is a simple one, you need to use the same formula multiple times in conditional formatting. It, the repetition of the formula, depends on the number of columns in any one of the tables, not based on the total number of tables.

Regarding the second method, you just need to use a single formula (a combo formula) to highlight multiple tables in Google Sheets. The formula is complex, but simple to use in conditional formatting.

Method 1 – Multiple Formulas for Highlighting Based on Number of Columns in the Table

As I have already mentioned, the formula that you will get below needs to be repeated in conditional formatting based on the number of columns in the tables.

Since the above tables contain only two columns each, you just need to duplicate the formula two times as two rules. I’ll explain it. Please read on.

Before writing the custom formula rule to highlit multiple whole tables, you may please take note of the following points related to the cell reference of the tables.

1. Range to highlight – B3:J10. You can extend this range to include more tables and rows in tables. Find the instructions after the formula below.
2. The cell reference of the very first cell in the range is B3.
3. The number of columns in tables is 2.
4. Condition to highlight – The first cell in the table must be “fruits”. If matches, highlight the corresponding whole table with light grey color (change the criteria as per your requirement).

Here is the conditional format rule to highlight multiple tables in Google Sheets.

=regexmatch(B$3,"fruits")

This rule will only highlight the first matching column in each table in the range B3:J10. To highlight the second column we can follow a simple trick. I’ll explain that later.

Further, you can extend the range from B3:J10 to B3:Z100 or more. For that, before applying the rule, select the range B3:Z100 instead of B3:J10.

First, let us learn how to apply the above formula to highlight multiple tables in Google Sheets.

Inserting Custom Formula (Rule) in Conditional Formatting (Formant Menu)

Select the range B3:J10 and go to Conditional formatting. It’s under the Format menu.

Inside the ‘Conditional format rules’ panel, you can see that the ‘Apply to range’ is set to B3:J10. If not, enter the range B3:J10 in the given field.

To enter the above formula, select ‘Custom formula is’. It’s under the title ‘Format rules’.

If you have any doubt to enter the rule (formula) and the range to highlight in ‘Apply to range’, please refer to the screenshot below.

Under the ‘Formatting style’, I have set ‘Light Grey’ as the cell background color and ‘Red Berry’ as the font color.

The above setting will only highlight the first column in the matching tables. To highlight the second column in each matching table, you can use the same rule again. How?

How to Include More Than One Column in Each Table in Highlighting?

Select the range C3:J10. That means instead of B3:J10 use C3:J10 as the ‘Apply to range’ in the ‘Conditional format rules’ panel. Then use the same formula in the custom formula filed.

Since I have selected the range C3:J10, the formatting shifts from column B to C and, of course, I to J.

If there are three columns in each table, again repeat the rule, but for the range D3:J10. I hope these make sense?

Follow the above method to conditional format multiple tables horizontally based on condition in Google Sheets.

How to Add More Conditions in Conditional Formatting Multiple Tables in Google Sheets

As you can see, the custom formula rule is actually a simple REGEXMATCH formula that can match multiple criteria in a range of cells.

To include more criteria, for example, highlight the tables “fruits” and “vegetables”, use the formula as below.

=regexmatch(B$3,"fruits|vegetables")

Follow this method to highlight/conditional format multi-column multiple tables of the same size in Google Sheets.

Method 2 – Single Formula to Highlight Multiple Tables of the Same Size in Google Sheets

This is the advanced form of the above method. I have already explained how to apply custom rules and other things. So I am directly taking you to the formula.

Select the range B3:J10 and enter the below rule to highlight all the tables contain “fruits” in the first cell.

=ArrayFormula(regexmatch(column(B$3:J$3)&"",TEXTJOIN("|",1,if(regexmatch($B$3:$J$3,"fruits")=TRUE,{column($B$3:$J$3);column($B$3:$J$3)+1},"-"))))

To highlight tables that contain different values in the first cell, for example, “fruits” and “vegetables” as above, use the below rule instead.

=ArrayFormula(regexmatch(column(B$3:J$3)&"",TEXTJOIN("|",1,if(regexmatch($B$3:$J$3,"fruits|vegetables")=TRUE,{column($B$3:$J$3);column($B$3:$J$3)+1},"-"))))

The only change is the use of multiple criteria in the REGEXMATCH.

To know how to include more rows in the tables and also more columns to include additional tables, please follow the below two points.

Assume, my tables are in the range B3:Z100. I’ll tell you how to change the above formula.

1. Replace all the occurrence of B$3:J$3 in the formula with B$3:Z$3.
2. The ‘Apply to range’ in the conditional format must be set to B3:Z100 instead of B3:J10.

Formula Explanation

The inner REGEXMATCH matches the criteria in the first row of the tables and returns TRUE for match and FALSE for the mismatch.

I have used the IF function to check the TRUE values and the COLUMN function to return the corresponding (TRUE value) column numbers.

The outer REGEXMATCH matches the column numbers of each column in the range with the above IF formula returned column numbers. The matching columns got highlighted.

The above formula is for two-column tables. Then, how to include more than two columns?

The below part of the formula controls the number of columns to highlight in each table.

{column($B$3:$J$3);column($B$3:$J$3)+1}

It’s for two columns. For three columns use it as;

{column($B$3:$J$3);column($B$3:$J$3)+1;column($B$3:$J$3)+2}

Quite simple right?

If you want to conditionally highlight/conditional format multiple tables having more than two columns, use this method 2, else go for method 1.

Sample Sheet

Conclusion

There are around 40+ tutorials pertaining to Conditional Formatting on this blog. Search this site using “highlight” or “Conditional format” to find most of them.

The search field is available both on the Footer and on the top navigation bar.

That’s all. Enjoy!