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?



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.


alt text

alt text

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 )
  • 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.
  • Tried that and it's giving me a "Please enter a valid formula" error. – nmuntz Aug 5 '10 at 14:34
  • @nmuntz I have updated the answer. – Sharjeel Aziz Aug 5 '10 at 15:41
  • That worked out great ! Thank you very much! I would have never guessed that you cant use formulas with icon sets – nmuntz Aug 5 '10 at 16:30

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:


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:


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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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