Excel – Conditional formatting based on cell above

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.

While in A2, please try a Conditional Formatting formula rule of:

=AND(A1=A2,A2<>"")  

with Applies to =$2:$20 or as far down as you wish or are able to go, with yellow fill and blue font.