Excel Conditional Formatting Offset Greater than

I need to create a conditional formatting rule that will apply some color if the cell to the left has a value greater than 500, however it is not accepting my formula:

=OFFSET(0,-1) > 500

Can anyone tell me what am I doing wrong?

3 Answers

Edit:I think you would not be able to use conditional formatting this way with icon sets. I got the following error when I tried:

You cannot use relative references in conditional formatting criteria for color scales, data bars, and icon sets.

I was, however, able to achieve the same by applying this formula to right column and then applying conditional formatting rule on this column as show in the screen shot.

=IF(OFFSET(E10,0,-1)>500,1,IF(OFFSET(E10,0,-1)=500,0,-1))

The formula should be:

=OFFSET(E10,0,-1)>500

In Excel, the Offset function returns a reference to a range that is offset a number of rows and columns from another range or cell.

The syntax for the Offset function is:

Offset( range, rows, columns, height, width )

• range is the starting range from which the offset will be applied.
• rows is the number of rows to apply as the offset to the range. This can be a positive or negative number.
• columns is the number of columns to apply as the offset to the range. This can be a positive or negative number.
• height is the number of rows that you want the returned range to be.
• width is the number of columns that you want the returned range to be.

There is a way of achieving the equivalent of Offset in Conditional formatting with relative references.

In the cell you want conditionally coloured, select the conditional formatting option Use a formula to determine which cells to format. In the formula bar use the following syntax:

=INDIRECT(ADDRESS(ROW()+X,COLUMN()-Y))>Z

Here, X is the vertical offset, Y is the horizontal offset and Z is the value against which you are testing.

In the OP's example, it would be:

=INDIRECT(ADDRESS(ROW(),COLUMN()-1))>500

You then just need to define the formatting; you can't have the traffic light symbol but you can set the cell's background or font to be a specific colour.

In your case (fixed offset), there is a much simpler way to achieve your goal: when moving/extending conditional formatting, absolute/relative reference are handled sensibly (even if this is undocumented afaik).
For example edit conditional formatting on cell B2, and write in the condition "=A2>500". Then copy format (or extend) on the others cells you want formatted that way.
That's it.
If you need something more flexible, e.g. getting the actual offset from a second cell, you have to revert to the INDIRECT() solution.