Why is Excel changing my Cond Formatting rule formula to reference the LAST row/column of the sheet?

[d0rian]

This happens with enough frequency that I want to get to the bottom of it. What happens is:

1. I create a new conditional formatting ("CF") rule, inputting a simple formula to determine when the formatting should apply.
2. I enable it, only to find that the formatting is not working as I expected it to
3. I re-open the CF Rule manager to investigate, and see that the reason it's not working is that the "RULE" value has been changed to the last column or row in the sheet (where obviously no data exists.)

For example, see the images below; I initially put in a simple formula of something like "=D1<0" (it may not actually have been D1 -- I forget what the exact cell reference was, but it was something near the top-left of my sheet). But when I open the Rule Manager, the rule has been changed to refer to cell "XEU1048494<0". That's the very BOTTOM-most row/column of the sheet and obviously no data exists there.

Why is Excel doing this? As best I can tell, it has something to do with the order in which I input (1) the rule formula itself, and (2) the "Applies to" range. I just want to understand just what the logic is that's causing this so I can avoid it going forward.

 

[Special-K99]

If you've copied the rule to other cells the D1 cell will adjust accordingly since you haven't anchored the cell.
To anchor the cell insert a $ either before the column reference or the row reference.

Depends what cells you have selected when you open that rule to look at it.
I don't think we can tell from those screen shots.

 

[mole999]

out of interest,when you push CTRL + END what cell does the sheet end on?