Google sheets - Relative conditional format based on cell above

user_7180766 0
Viewed 1246

I search in the deepest parts on the internet for a google sheets relative conditional formating based on the cell above value, which means, that whenever i add a new value in the cell below, it will automatically check the cell above value and format the cell according to it.

I've finally came to the solution explained below.

The result will be something like this:

enter image description here

I hope it works for you, weary traveller.

google-sheets-formula spreadsheet google-sheets
2 Answers
  • Select the range of cells that you want to format.
  • Go to >Format > Conditional Format > Add rule.
  • Under format rules select the option "Custom Formula is".
  • Paste the next formula if you want to check that your current cell is BIGGER THAN the cell above: =INDIRECT(ADDRESS(ROW();COLUMN())) > INDIRECT(ADDRESS(ROW()-1;COLUMN()))
  • OR the next formula if you want to check that your current cell is LOWER THAN the cell above: =INDIRECT(ADDRESS(ROW();COLUMN())) < INDIRECT(ADDRESS(ROW()-1;COLUMN())).
  • Select the color you like to apply, and click in DONE.
user_71807660

If the range of cells is A2:G, this rule will also work:

=AND(A2<>"",A2<A1)    [RED]

=AND(A2<>"",A2>A1)    [GREEN]
user_110751290
Related