I have a column with several numbers one below the other, I would like to color the cells based on the cells above, example if cell is larger than cell above then the cell color is green, if it is smaller the color is red. I need to do this in an entire column using only one formula, as this formula would be, I believe it is using = indirect with = address but I can't formulate it

  • 1
    you can apply the formula and populate an adjacent column to be marked with color to be filled, later with conditional formatting update the cell colors. ex : =IF(F5>=F4, "GREEN", "RED")
    – DarkKnight
    May 15, 2021 at 16:34

2 Answers 2


You want to create a new conditional formatting (CF) rule using relative references. Lets say A1 is your header and A2 to A100 is data. Colour all your data red with normal formatting. Now select A3 to A100 and create a new CF rule using a formula (last option in the "New Rule..." CF type list). In the formula text box, enter:


And set the CF colour to green. Et voilà!

  • @MVO if this worked for you kindly checkmark the answer. May 15, 2021 at 22:26

Select the range you want to format and Go to Home- Conditional Formatting- Manage Rules...- select Use a formula to determine with cells to format and enter these formulas and formatting:

enter image description here

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .