Conditional formatting based on another cell's value

Question

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?

Schumer

Until you move to the new Sheets, adapt one of the scripts from this answer using an onEdit trigger or this answer which doesn't. – Farthermost

Answer 1

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.

Messenger

Thank you Zig for letting me know. It's useful however can't just use it unless I copy my old spreadsheet version content to the new one. Thanks buddy – Schumer

For speed I recommend migrating to the new sheets, but wait a bit as its on early beta and some things aren't fully supported yet. – Messenger

I don't understand how to use the range directly in the custom formula field. If I want cells B5-B10 to have a color depending on cell C5, I cannot figure out how to express that. – Equator

I figured it out, you need to use an absolute reference. For example, "=B5:B10=C$5" in the forumla, and B5:B10 in the range. – Equator

yes, if you dont want the reference to move for the other cells. – Messenger

To reference a cell on another sheet: =B2>0.8*indirect("Sheet1!C2") (source) – Overanxious

You don't need the custom formula. You can just use the Greater Than option. In the value/formula box, enter the cell reference. Just bear in mind that the cell reference is relative without the $, so if you are trying to get multiple cells to change color based on one cell, you need to use an absolute reference: $C$5 – Healy

@Zig Mandel Works ok except for cells formatted as currencies, im just trying to hide (fore/background white) range of cells, some of which are currency. Any idea how? cheers – People

@Tom Thanks for letting me know it's relative. It really doesn't look like that when inputting it, but it behaves like relative. I was afraid that I had to add one for each 45 columns, but I'm glad I didn't have to. – Gymnosperm

It appears you can only do this for the "Single colour" tab, and not for the "Colour scale" tab, unfortunately. Unless someone knows of a way, it looks like you would have to to manually create a colour scale by putting in each colour individually (and you wouldn't get the smooth colour fade effect). – Ene

Answer 2

One more example:

If you have Column from A to D, and need to highlight the whole line (e.g. from A to D) if B is "Complete", then you can do it following:

"Custom formula is":  =$B:$B="Completed" 
Background Color:     red 
Range:                A:D

Of course, you can change Range to A:T if you have more columns.

If B contains "Complete", use search as following:

"Custom formula is":  =search("Completed",$B:$B) 
Background Color:     red 
Range:                A:D

Normandnormandy

This doesn't seem to work unless you provide $ in the "Custom Formula Is" field. i.e. do $B$2:$B and not B2:B – Irmine

Just to add to lucky1928's answer -- if you change Range from A:D to A:T you will be adding more columns, not lines/rows. If you only have a finite number of rows you want the conditional formatting to work on then it may help to specify a row, e.g. A2:D13 if you have a header row for example. Otherwise if you are matching to something being either blank or not equal to a specific value, you may be adding colour to all rows that exist in the sheet, which may not be what you want. – Beeswax

Note that the "=" at the beginning of the formula is VERY IMPORTANT – Torrence

Answer 3

I've used an interesting conditional formatting in a recent file of mine and thought it would be useful to others too. So this answer is meant for completeness to the previous ones.

It should demonstrate what this amazing feature is capable of, and especially how the $ thing works.

Example table

The color from D to G depend on the values in columns A, B and C. But the formula needs to check values that are fixed horizontally (user, start, end), and values that are fixed vertically (dates in row 1). That's where the dollar sign gets useful.

Solution

There are 2 users in the table, each with a defined color, respectively foo (blue) and bar (yellow).
We have to use the following conditional formatting rules, and apply both of them on the same range (D2:G3):

1. =AND($A2="foo", D$1>=$B2, D$1<=$C2)
2. =AND($A2="bar", D$1>=$B2, D$1<=$C2)

In English, the condition means:
User is name, and date of current cell is after start and before end

Notice how the only thing that changes between the 2 formulas, is the name of the user. This makes it really easy to reuse with many other users!

Explanations

Important: Variable rows and columns are relative to the start of the range. But fixed values are not affected.

It is easy to get confused with relative positions. In this example, if we had used the range D1:G3 instead of D2:G3, the color formatting would be shifted 1 row up.
To avoid that, remember that the value for variable rows and columns should correspond to the start of the containing range.

In this example, the range that contains colors is D2:G3, so the start is D2.

User, start, and end vary with rows
-> Fixed columns A B C, variable rows starting at 2: $A2, $B2, $C2

Dates vary with columns
-> Variable columns starting at D, fixed row 1: D$1

Domeniga

This probably needs a separate post, but is it possible to base the conditional styling itself off of another cell? e.g. if you have a separate lookup table for your guys "foo" and "bar", can Conditional Formatting be configured to apply the fg-color & bg-color of that cell in your rule? This is something I need often, a "status" indicator for a row, where the colors and status options are easily configured without manual reprogramming of the conditional formatting rules. – Polinski

You may be able to do that with scripting. I reckon I've never seen a dynamic mean to select specific colors or an existing format through normal usage. EDIT: to start scripting go to tools > script editor and start coding. But that's a whole new level in using sheets ;-) – Domeniga

Answer 4

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

Af

Answer 5

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.

Estancia

Answer 6

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.

Analogize

Answer 7

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.

Aylesbury