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)
I hope this can be resolved as a matter of urgency; I was alarmed to discover this issue when developing clinical dashboards as it could lead to incorrect display of red/amber/green status for the indicators.
I also think this is an obvious improvement in conditional formatting. I'm currently using sheet macros to maintain conditional formatting. It's a complete pain.
Daniel Acevedo commented
has this changed in 2016?
Adding my vote. This is extremely frustrating
Connor Krammer commented
Adding another vote to this. The ranges that my conditional formatting rules are applied to become an utter mess every time I have to add or delete a row. It's become absolutely unmanageable, and forces me to manually redefine every single range each time it breaks.
Incredibly time-wasting, and greatly reduces the utility provided by conditional formatting. I would love to have this feature in Excel.
Tomasz Truszczyński commented
I totally agree. I would add to the named ranges whole columns as well, eg. $A:$C. I mainly define whole columns in c.f. window. It looks clean and easy to understand - until a while. Soon (after some operations) new rules excel creates, extracting small, mainly 1 cell ranges from these column ranges and creates very very complicated ranges. It drives me sooo crazy. If I defined whole column(s), I expect to keep it this way.
This is driving me absolutely crazy. I've wasted a lot of valuable time removing extraneous "applies to" entries in a sheet that has 7 rules applied to 4 columns. I tried the named range route thinking it would help but, of course, it doesn't.
I spend more time cleaning up conditional formatting that is has almost gotten to the point of not using anymore. I add/delete/sort rows constantly and this creates such a mess in the Conditional Formatting Manager that even my "duplicates" color on a whole column becomes inaccurate after a while. I don't understand why when a format is applied to a complete column that Excel divides and adds for row adjustments anyway. It should maintain the column regardless of rows added/deleted/sorted.
I totally agree!
Adding a new row or deleting a row is messing up the Conditional Formatting. It gets split up and it's hard work to get the rules cleaned up. Very frustrating!
I agree with Shannon Patrick 100%. Well stated.
I also have a complex sheet with ~15 conditional formats applied to a large range. After a few months of adding/deleting rows in this range, Excel broke that up into literally ~1,000 conditional formats in place of the original ~15. This creates a significant performance issue on the sheet. To resolve this I have created a workaround for this problem, but it is rather elaborate (it includes replacing the Row context-sensitive right-click "Insert" button with my own button that calls a macro that inserts the row in such a way that the formatting rules are automatically updated correctly). This should not be necessary!
Keith Howington commented
"... like Excel to maintain named range references and structured table references in Conditional Formatting rules ..."
Just to clarify, the "rules" side seems to be okay. It is specifically the "applies to" that needs to be allowed to be dynamic rather than static as it has been going back to at least 2007.
As a static system now; Excel evaluates the "applies to" reference at the time it is entered, and immediately stores the result as an absolute reference. This includes even "indirect" and other abstractions; all become absolute, hard-coded addresses based upon the value calculated at entry time.
The "applies to" will incur a bit of performance penalty by being evaluated at runtime, but this can be optimized to occur only when the applies-to line has a reference that needs it.
As others have said, this will remove the creation of hundreds or thousands of garbage rules from the conditional formatting list of mature spreadsheets.
===|==============/ D. Keith Howington
Shannon Patrick commented
If I could only show you the mess that is the "Applies to" section of my conditional formatting rules manager... (That window has fixed dimensions, so I can't get a good screen shot.) Essentially, relying on fixed references creates problems in three main ways:
1) As Graham stated, it makes for complicated ranges that often develop holes as a spreadsheet develops and is manipulated. This causes non-applied and mis-applied formatting that many end users do not know how to correct.
2) It increases the odds that a range will not be fully entered or will be entered incorrectly. Typing [name] is simply less error-prone than typing $a$2:$a:23556. This problem is accentuated by the limited options available for auditing conditional formatting/formulae.
3) It is faster to create conditional formats and their formulate with named ranges. Typing [name] is not just more accurate, but I don't need to know where it is on the spreadsheet. I frequently use conditional formats that rely on the values of other cells, and it's inefficient to have to shift out of the conditional formatting menu to scroll over and select the cell range being referenced.
Thanks for your consideration of this issue.
This is the same behaviour as when you try to make a chart series refer to a named range - again it defaults to absolute cells. This is fairly annoying if you expect your named range might change in size.
To clarify as per Scott Sobel's comment: Yes, ideally this would apply to both regular named ranges as well as the structured named ranges from tables.
Scott Sobel commented
I assume you also mean this to apply to regular named ranges? For example, have conditional formatting Apply To =Revenues, where Revenues is a named range.