Excel 2007 : Conditional Formatting drag

In Excel 2003, I used to have my conditional formatting based upon the value of another cell in the same column. I could drag the cell to the right, as if a "fill right", and the formula, format, and conditional format would drag to the right. When I do that in Excel 2007, the formula and format will drag to the right, but the conditional formatting remains pointed to the original column. For example, I have cells X48-X61 conditionally formatted to appear red if their values are greater than the value in cell X18. In the conditional formatting dialog box, I remove the "$" from in front of the "X$18". This should allow me to drag the cells in X48-X61 to the right, so that the formulas, formats, and conditional formats get filled into cells Y48-Y61, with the conditional formatting pointing to cell Y18. However, it doesn't work. The conditional formatting for cells Y48-Y61 still point to X18. It ignores the fact I removed the "$". Is this a bug or what?

Hello Scott,

Are you using a formula in conditional formatting? If you have X48:X61 formatted with

=X48>X$18

and then extend that to the range X48:Y61 you'll still see the same formula....but applied to the extended range. This should work as you want, it just displays differently to Excel 2003. The formula you see will be that applied to the top left cell of the range, but for each specific cell it should adjust as needed, e.g. for Y60 it's applying =Y60>Y$18. Test with different value in X18 and Y18 to check

I got it to work like I want it! I had previously tried using a formula, but couldn't figure out on my own exactly what to write. It helped that you showed the exact formula to use. It helped me to understand the new way of doing things. I'm happy I can drag the formatting to the right (or even to the left if I need to, in which case it automatically adjusts the upper left cell reference). Excel 2007's new features are being promoted as being "more intuitive", but in my opinion, it's not very intuitive at all in this case. Now that I see the trick, though, it will be easy. Thanks!

FYI I too was baffled by this until I realized that the conditional formatting works correctly even if the formula does not look right to you. For example I have a long column of data where each cell in the next column should be compared to the same master cell at the top and formatted if it is greater that it. So column G gets formatted based on what is in column H. The conditional format formula in cell G6 looks like this:

=H6>$H$5 and it applies to cell $G$6

To accomplish the formatting, I typed this formula in, then copied it and did did a paste special of formats to all the cells in column G. When I tested to see if the condition works, it does. Now here is the tricky part, when I scroll down and click on cell G20 for example, what do I expect to see in the conditional format fomula? Logically I'm thinking it will be

=H20>$H$5 and it applies to cell $G$20

Not so! The formula shown is only the whole of the copy/past you did and is not customized to the cell you are on, so you see

=H7>$H$5 and it applies to $G$7:$G$100

It shows the entire range you pasted to...so although at first glance it looks as though your conditional formula is wrong, it does seem to work. Excel 2007 is kind of misleading in this respect and mine seems to be functioning as it should.