10

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 |       |
17

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

1
  • Great, this is exactly what I needed!!! Thanks.
    – Nafas
    Apr 29 '12 at 8:58
36

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

=OFFSET(INDIRECT(ADDRESS(ROW(), COLUMN())),0,-1)
10
  • 8
    @Scott Just because one answer has been posted doesn't mean this one isn't any less valid.
    – Jon
    Jun 10 '14 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 '14 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 '14 at 22:09
  • 5
    @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 '14 at 23:43
  • 2
    Should be the accepted solution. Jan 24 '20 at 21:39
0

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

Conditional format rules

Not the answer you're looking for? Browse other questions tagged or ask your own question.