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 to Graham for starting this conversation. If you would also like Excel to maintain named range references and structured table references in the “applies to” field for Conditional Formatting rules, please add your comments and vote this one up. We will prioritize accordingly.
Steve (MS Excel)
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!
Rob Hines commented
Yes. I found this looking to see if it were possible.
Excel is SO frustrating. Things like structured references almost take Excel out of the 1990s. And then, you find that sometimes they work...sometimes they don't (after like 3 hours).
Just as frustrating... I can't just break down and use VBA automation because everything I do has to run on Macs. And we all know that "Mac is different".
If Excel doesn't modernize...I'm going to find a replacement! And NO, going to Office 365 will not be acceptable.
i agree i have more 1000 lines of condicional formatting, its imposible to find a mistake correctly!
I like this idea. It would be very useful for tables that are refreshed via ODBC for quickly highlighting anomalies.