Conditional formatting based on another cell's value

user_3095729 0
Viewed 719334

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?

conditional-formatting google-sheets-formula google-sheets
7 Answers

Basically all you need to do is add $ as prefix at column letter and row number. Please see image below

enter image description here

user_16009260

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.

There is no mention that B5 contains any value so assuming 80% is .8 formatted as percentage without decimals and blank counts as "below":

Select B5, colour "amber/red" with standard fill then Format - Conditional formatting..., Custom formula is and: 

=C5>0.8

with green fill and Done.

CF rule example

user_15051200

I'm disappointed at how long it took to work this out.

I want to see which values in my range are outside standard deviation.

  1. Add the standard deviation calc to a cell somewhere =STDEV(L3:L32)*2
  2. Select the range to be highlighted, right click, conditional formatting
  3. Pick Format Cells if Greater than
  4. In the Value or Formula box type =$L$32 (whatever cell your stdev is in)

I couldn't work out how to put the STDEv inline. I tried many things with unexpected results.

user_16901930

I just want to explain it in a another way. In "custom formula" conditional formatting you have two important fields:

  • Custom formula
  • Apply to

Let's say, you have a simple sheet with test percentages of students, where you want to color Student Ids(Column B) where their score(Column C) > 80%:

Row B(Student ID) C(Score)
1 48189 98%
2 9823 6%
3 17570 40%
4 60968 23%
5 69936 7%
6 8276 59%
7 15682 96%
8 95977 31%

To design a custom formula, you only need to design a formula for the top left of the range, you want to color. In this case, that would be B1.

The formula should return

  • TRUE, if it should be colored and
  • FALSE, if it shouldn't be colored

For B1, the formula would then be:

=C1>80%

Now imagine that you put that formula in B1(Or just use a another range to test it). It would be like:

Row B C
1 TRUE
2
3
4
5
6
7
8

Now imagine dragging the formula(or autofill) up to B8 from B1. This is how it would look like

Row B C
1 TRUE
2 FALSE
3 FALSE
4 FALSE
5 FALSE
6 FALSE
7 TRUE
8 FALSE

This translates directly to color B1 and B7. Now the interesting thing is All of this is autocalculated using the given formula for B1 and the Apply to range. If you fill:

  • Custom formula: =C1>80% and
  • Apply to: B1:B8

you're saying

  • Fill the custom formula =C1>80%
  • in the top left cell of the provided range B1:B8,i.e., B1 and
  • drag/autofill the formula to the whole range B1:B8 and
  • Color the cells, where the formula outputs TRUE

If you want to color both student IDs and score, you would use

  • Custom formula:

    =$C1>80%

  • Apply to:

    B1:C8

The $ in the $C1 says not to change C, when autofilling the range. In the imaginary table(I suggest you to output the table somewhere). This would look like:

Row B C
1 TRUE TRUE
2 FALSE FALSE
3 FALSE FALSE
4 FALSE FALSE
5 FALSE FALSE
6 FALSE FALSE
7 TRUE TRUE
8 FALSE FALSE

In this way, you can color any cell anywhere based on any other cell.

user_84044530
Related