Conditional formatting - Apply to named ranges
Currently if you apply conditional formatting to a named range (i.e.. a range named via the Name Manager or a table style named-range), the value in the "Applies to" column of the Condidtional Formatting Rules Manger will revert the named range to its absolute cell reference.
eg. "Applies to: =Table1[Column3]" becomes "Applies to: =$C$2:$C$7".
Apart from being more difficult to read, it creates other complexities. e.g. if I happen to copy/paste a cell into this range, it creates a mess in the rules manager, creating a new rule for each cell pasted.
e.g. if my named range is 'Table1[Column3]' which corresponds to cells $C$2:$C$7, and I copy say cell C2 and paste it into C8, then in the rules manager another line is for some reason added with the same rule. So now I have one rule for range '$C$2:$C$7' and another one for '$C$9'. Why?!
Having one line for the named range would make things so much more simple.
Thanks for supporting this feature. We won’t be able to in the near future, but we understand that it would be a nice improvement. Even though it seems like a straightforward idea, it is quite complex since named ranges can be dynamic, meaning that the range is calculated when the workbook is calculated.
We’ll continue tracking votes for this suggestion.
Tim Wolfe commented
I Agree - named ranges should be supported THROUGHOUT Excel, especially so for Conditional Formatting and for Data Validation sources.
Named ranges are essential for building dynamic applications where range dimensions frequently change.
I would love this as well. I don't have too many problems with it reverting back as I tend to copy and paste specifically (i.e. if values paste as values, paste comments as comments, paste formulas as formulas). But every now and then excel surprises me. Conditional formatting is so wonderful but has yet to keep up with the rest of excel. Besides fixing the name range revert flaw, could you also make conditional formatting more user friendly. So problematic to have to put down so many conditions in such a small window.
Doug Clutter commented
Could not believe it reverted back to the absolute cell reference! And...to make matters worse...it did so without any feedback. So, I was calling up my Conditional Formatting and happily changing them to named ranges for a while before I happened to notice what Excel was doing. (sigh)
Chris Davis commented
I happened to experience the static nature of the "Applies to" selection when I put in the name of a dynamic range. I was disappointed because I had just discovered the use of the OFFSET function and was trying to use it to counter the file bloat I had caused by using conditional formatting and I had employed on a worksheet in order to accommodate a high number of dependencies and a potentially large data set. It looks like for now, I will have to use sufficiently large enough ranges in the "Applies to" section because it does not appear to grow or shrink after being engaged in the Conditional Formatting Rules Manager. I have high hopes for this to be developed.
Absolutelly, pleas do so
This is a must have at 2016, and is coming form Dec 2015
It's strange that it isn't implemented yet.
The work around of pasting formulas instead a complete paste is not enough.
Geez, please do this already!
Oh no is this still under review? I can't believe it has not been changed yet. Why is that even a question Steve?
Same need for cell validation from list..
change that please!!
[Deleted User] commented
This! 100x. Please implement this.
This is clearly gaining some traction... as well as being a continued annoyance.
Really very hard needed.
David Whitney commented
Oh, yes, please. When I refresh my data the row count changes. I do not want the conditional formatting in rows that are not populated, and of course I want it in every populated row. Right now my only option is to drop the formatting rules and rebuild based on the new row count. In an ideal world I would only have to modify the RefersTo of the named range.
Thomas Widmer commented
This would be really great because I could use it fix my biggest excel peeve: E.g. I have a format on Column =$A:$A, when I paste some Data I suddenly have =$A$1:$A$148;$A$150:$A$1048576 even though I want to keep =$A:$A.
i would also add the ability to search for, or the automatic highlighting of all external references in conditional formatting, as they are otherwise hard to find & manage, especially when after some editting, a very few conditional formatting rules quickly become huge list of the same rules applied to different ranges...
adding vote to this function.
Thadius Miller commented
Desperately needed. It's so frustrating that Excel duplicates conditional formatting rules when copying/pasting in data. I don't need 18 broken copies of each CF rule that applies to that cell, just one that applies to the whole sheet!