Have you ever wondered how to highlight or conditionally format multiple whole tables in Google Sheets? Well, in this article, I’m going to show you how to do just that!
Imagine you have several tables horizontally in a sheet, and you want to highlight or format only those tables where the first cell contains a particular value. It could be anything from “fruits” to “vegetables” or any other value you desire.
Let’s take a look at an example. In the image below, you can see three tables horizontally, and we want to highlight the tables that have the value “fruits” in the very first cell.
As you can see, tables 1 and 3 match the condition, so the cells in these two tables are highlighted.
Now, let’s dive into the methods you can use to conditionally highlight multiple tables in Google Sheets.
Method 1: Multiple Formulas for Highlighting Based on Number of Columns in the Table
The first method is simple but requires using the same formula multiple times in conditional formatting. The number of times you need to repeat the formula depends on the number of columns in any one of the tables, not the total number of tables.
To get started, select the range of cells you want to apply the conditional formatting to. In this case, it would be the range covering all the tables you want to highlight.
Before we proceed, here are a few points to note:
- The range to highlight is B3:J10, but you can extend this range to include more tables and rows.
- The cell reference of the very first cell in the range is B3.
- The number of columns in the tables is 2.
- The condition to highlight is when the first cell in the table contains the value “fruits”.
Now, let’s look at 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 provided range. To highlight the second column, we can follow a simple trick, which I’ll explain later.
To apply this formula, select the range B3:J10 and go to Conditional Formatting under the Format menu. In the Conditional format rules panel, ensure that the “Apply to range” is set to B3:J10. Then, select “Custom formula is” and enter the above formula.
You can also customize the formatting style to your liking, such as setting a light grey background color and a red berry font color.
To highlight more than one column in each table, select the range C3:J10 instead of B3:J10 in the “Apply to range” field and use the same formula in the custom formula field. Similarly, if you have three columns in each table, repeat the rule for the range D3:J10.
Now, what if you want to add more conditions to the conditional formatting? Let’s say you want to highlight tables that contain both “fruits” and “vegetables”. You can achieve this by modifying the formula as follows:
=regexmatch(B$3,"fruits|vegetables")
Feel free to experiment with different criteria and values to highlight the tables you desire.
Method 2: Single Formula to Highlight Multiple Tables of the Same Size
If you’re looking for a more advanced method, I have good news for you! There is a way to highlight multiple tables using a single formula. This method is slightly more complex, but it’s worth it.
Again, select the range B3:J10 (or the range covering all the tables), and enter the following formula to highlight all the tables that 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, such as “fruits” and “vegetables”, use the following formula 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},”-“)))`
In this formula, you can observe that the use of multiple criteria is achieved through the REGEXMATCH function.
To include more rows and columns in the tables or to add additional tables, make the following changes to the formula:
- Replace all occurrences of B$3:J$3 with B$3:Z$3 (or any desired range).
- Set the “Apply to range” in the conditional format to cover the expanded range, such as B3:Z100 (or any desired range).
The formula may seem complex, but once you understand its structure, it becomes quite simple. If you have any questions, refer to the formula explanation provided in the original article.
If you want to highlight tables with more than two columns, this method is the way to go. Otherwise, you can stick to method 1.
That’s it! You now have the knowledge to conditionally format multiple tables based on the first cell value in Google Sheets. Have fun experimenting with different criteria and values to make your tables stand out!
If you’re interested in more articles on Google Sheets and other exciting topics, be sure to visit Crawlan. We’ve got plenty of juicy secrets to share with you and your besties!
Enjoy exploring the power of Google Sheets and make your data shine!
