Conditional formatting based on another cell's value
I'm using Google Sheets for a daily dashboard. What I need is to change the background color of cell B5 based on the value of another cell - C5. If C5 is greater than 80% then the background color is green but if it's below, it will be amber/red.
Is this available with a Google Sheets function or do I need to insert a script?
Answer
Note: when it says "B5" in the explanation below, it actually means "B{current_row}", so for C5 it's B5, for C6 it's B6 and so on. Unless you specify $B$5 - then you refer to one specific cell.
This is supported in Google Sheets as of 2015: https://support.google.com/drive/answer/78413#formulas
In your case, you will need to set conditional formatting on B5.
- Use the "Custom formula is" option and set it to
=B5>0.8*C5. - set the "Range" option to
B5. - set the desired color
You can repeat this process to add more colors for the background or text or a color scale.
Even better, make a single rule apply to all rows by using ranges in "Range". Example assuming the first row is a header:
- On B2 conditional formatting, set the "Custom formula is" to
=B2>0.8*C2. - set the "Range" option to
B2:B. - set the desired color
Will be like the previous example but works on all rows, not just row 5.
Ranges can also be used in the "Custom formula is" so you can color an entire row based on their column values.