Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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 |       |
share|improve this question

2 Answers 2

up vote 5 down vote accepted

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

share|improve this answer
    
Great, this is exactly what I needed!!! Thanks. –  Nafas Apr 29 '12 at 8:58

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)
share|improve this answer
    
Why make it harder than it needs to be, when a correct answer has already been posted? –  Scott Jun 10 at 17:34
    
@Scott Just because one answer has been posted doesn't mean this one isn't any less valid. –  Chipperyman Jun 10 at 18:12
    
@Chipperyman: I know; but this answer is unnecessarily complicated, has no particular advantage (or at least none that is stated), and it doesn't provide the level of detail that the OP probably needs. –  Scott Jun 10 at 18:15
    
Then don't use this solution. It's always nice to have alternatives, even if they're bad. –  Chipperyman Jun 10 at 18:19
1  
@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 at 23:43

protected by Mokubai Jul 24 at 18:39

Thank you for your interest in this question. Because it has attracted low-quality answers, posting an answer now requires 10 reputation on this site.

Would you like to answer one of these unanswered questions instead?

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