[google-sheets] 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?

The answer is


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

Simple google sheets 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


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.


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

enter image description here


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


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

Examples related to google-sheets

Import data into Google Colaboratory Is there a Google Sheets formula to put the name of the sheet into a cell? How to label scatterplot points by name? Adding Buttons To Google Sheets and Set value to Cells on clicking How to automatically import data from uploaded CSV or XLS file into Google Sheets How to negate 'isblank' function How can I copy a conditional formatting from one document to another? Multiple IF statements between number ranges Apply formula to the entire column How to define global variable in Google Apps Script

Examples related to google-sheets-formula

Multiple IF statements between number ranges Apply formula to the entire column Count rows with not empty value Conditional formatting based on another cell's value Count cells that contain any text Counting number of occurrences in column? Get the last non-empty cell in a column in Google Sheets Selecting the last value of a column

Examples related to conditional-formatting

Vue.js: Conditional class style binding Conditionally formatting cells if their value equals any value of another column Format cell if cell contains date less than today Conditionally formatting if multiple cells are blank (no numerics throughout spreadsheet ) Change the color of cells in one column when they don't match cells in another column Conditional formatting based on another cell's value Conditional Formatting (IF not empty) Conditional Formatting using Excel VBA code Conditional formatting, entire row based Conditional formatting using AND() function