Is it possible apply Conditional formatting based on the value of a cell from the left?

What I need is to apply a red colour if the value is less then previous cell no colour if it unchanged and green if it is higher.

|  5   |   10  |  8  |   8   |
|      | green | red |       |

3 Answers 3


This is one of the more basic things with conditional formatting. Click Conditional Formatting on the Home tab, Highlight Cells Rules, Greater Than and either select a cell to compare it to or type the cell reference in. Repeat for less than.

It's a little more complicated to apply such a rule across a row/block.

I'll be using the first row as an example.

  1. Select a block. The 'active cell' is the white cell in the selection. This is important later.

    Screenshot of selected block
    Click for full size

  2. Click Conditional Formatting on the Home tab, Highlight Cells Rules, Greater Than

    Screenshot of menu
    Click for full size

  3. Select the cell you want to compare the active cell to. The other highlighted cells will automagically be compared to the cell shifted according to the relative position to the active cell. In this example, the selected cell is one column to the left of the active cell, so each cell in your selection will be compared to the cell one column to the left of the cell to be formatted. (I'm bad at explaining that, feel free to comment asking for clarification.)

    Select the desired colour.

    Make sure the formula has no dollar ($) signs. They mean it is an absolute reference, which means all cells in the selection will be compared to the specified cell, not the relative one "one column left". We don't want that, we want a relative reference instead, so remove the dollar signs.

    Screenshot of popup with options
    Click for full size

  4. Press OK.

  5. Go back to step 2, but use Less Than this time.

  6. There is no need to set a colour for "equal value", since default is blank. If you wish to set a different colour, there's an Equal To option too.

End result:

Screenshot of formatted cells
Click for full size

  • Great, this is exactly what I needed!!! Thanks.
    – Nafas
    Apr 29, 2012 at 8:58
  • this is not working in 2023 - all the cells in the block are comparing themselves to the same cell
    – bshirley
    Sep 19, 2023 at 21:57
  • @bshirley Did you make sure to remove any $ as described in the second half of step 3? I've just tested and this still works as originally described - it will default to an absolute reference which is why you need to edit the formula to remove the absolute anchors and make it relative. This is no different from the behaviour back in 2012.
    – Bob
    Sep 20, 2023 at 1:44

This will be a cell reference of the cell to the left. Highlight the table and do conditional formatting (greater than, less than).

  • 8
    @Scott Just because one answer has been posted doesn't mean this one isn't any less valid.
    – Jon
    Jun 10, 2014 at 18:12
  • 1
    Then don't use this solution. It's always nice to have alternatives, even if they're bad.
    – Jon
    Jun 10, 2014 at 18:19
  • 4
    I added this answer because it gives you a formula you can use outside of the conditional formatting. Useful for all sorts of things. Sorry I didn't take screen shots, really didn't think they were necessary.
    – dre
    Jun 11, 2014 at 22:09
  • 6
    @Scott +1. Here is a great use case for this - imagine having to compare multiple columns of the table, highlighting those that are bigger than their left neighbors. This can get pasted as a format and work out of the box, none of the other ones will.
    – gt6989b
    Sep 30, 2014 at 23:43
  • 3
    Should be the accepted solution. Jan 24, 2020 at 21:39

If you're doing this in Google Spreadsheets, this will work with the following config:

Conditional format rules

You must log in to answer this question.

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