Home > Mobile >  Google Sheets Overlapping Conditional Formatting
Google Sheets Overlapping Conditional Formatting

Time:07-18

I have a google sheet I am working on where I have a conditional formatting rule that bolds certain rows in a range, I also have one that highlights certain rows in the same range. These both work independently unless the second formatting rule wants to act on a row the first has already formatted, in which case it overwrites the bold to do the highlight. Is there any way around this such that the rules could both work on the same cells?

CodePudding user response:

Unfortunately, there must be an order of precedence for conditional formatting rules so that Sheets knows which to apply when there is a conflict. However, a problem naturally arises when conflicts are desired and expected, and multiple rules should apply to the same cell. The workaround for this is to have a third conditional formatting rule that checks if a cell meets both conditions, and if so, applies both formatting rules.

For example, in order of precedence from highest to lowest:

  1. If the cell meets condition A and condition B, highlight the cell and bold its text.
  2. If the cell meets condition A, bold the cell's text.
  3. If the cell meets condition B, highlight the cell.

This means that Sheets will first check for cells that meet condition B and highlight them. Then it will check for cells which meet condition A and make them bold, potentially overwriting any highlighted cells from previously. However, this overwriting is fixed by the top-precedence rule, which both bolds and highlights cells which meet both conditions.

The downside of this system comes if you wish to have three or more conditional formatting rules applying to the same range. The number of additional rules to account for each potential overlap grows very fast as the number of base rules increases.

  • Related