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?
Super User is a question and answer site for computer enthusiasts and power users. It only takes a minute to sign up.Sign up to join this community
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.
The formula should be:
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 )
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:
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:
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.
If you need something more flexible, e.g. getting the actual offset from a second cell, you have to revert to the INDIRECT() solution.