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.
Graham
shared this idea
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.
257 comments
-
Anonymous
commented
yes please
-
Joe
commented
> since named ranges can be dynamic, meaning that the range is calculated when the workbook is calculated.
Maybe start with reference to tables? I think it would be enough for majority of the users.
-
R Lien
commented
I want to flag for blank cells in a table to encourage users to complete the table... conditional formatting? Not if I add additional rows or columns to the table.
I'd hoped this was fixed for years... guess not. :( -
Lawrence Dunn
commented
@Steven Soeder - well said!
I'd also like to add, that for each one of use here, who are probably the more advanced Excel users in our respective organisations, there are probably 100+ others in our organisations that have been struggling with Conditional Formatting's range fragmentation habit for years.
The folk posting & voting here are merely the very tip of the Iceberg.
I'm a Senior Engineer at Ford Motor Company & am only around 18 months away from having 3 decades of experience of nigh on daily Excel use. Conditional Formatting is one of those tools thats relatively easy for many users to initially setup & it provides a convenient means to highlight certain cells/rows/columns/ranges etc, but the average user will return to amend, or add new rules after a few weeks & can find the simple CF rules they set an absolute mess & slow & hard to rectify - I suspect most probably just give up.
-
Futski
commented
@Steven Soeder - I think the problem is, as I've seen stated many times before, that MS doesn't actually have anybody that "uses" Excel. And certainly not anyone that could be considered a "power user" like the majority of people in this forum.
-
Steven Soeder
commented
@Excel Team [MSFT] - original response was to vote this up and "we'll prioritize accordingly." Well, we did vote it up, for 5 years, and apparently thats not enough. Just reading through the comments (which we've received continuous alerts on for 5 years now) it seems this is important to many users, so could you please explain why it has been de-priortized? I'm sure your software engineers and coders are pretty clever and could fix this if you asked them to. It's not a matter of it being too hard, it is a matter of it not being viewed as important. I'd like to know why it's not viewed as important, and what you need to see from us to convince you that it is.
-
Anonymous
commented
So between 2015 until 2020 nobody in this planet managed to solve this... Please hire the Russian who developed the Coronavirus's vaccine
-
YanisK
commented
The latest "update" is ridiculously disappointing. FIVE years in, and you're saying you basically can't homogenise functions within Excel!!?
-
Lawrence Dunn
commented
as there is a flurry of responses to this, now that we've woken the 'sleeping giant', it may be of value to some if I advise of the 'work around' I've been using to the CF range 'unplanned rapid disassembly' issue.
Recognising that Excel is going to fragment our 'apply to' ranges in CF, I decided my work around was to NOT create CF in the normal way, but to write the rules & addresses directly into VBA code, & have that code start by clearing all CF first then apply the CF rules/ranges written in the VBA code.
This does not stop the CF from fragmenting, but when it gets too fragmented to work with, I can just run the VBA code clearing & re applying my initial rules.
I find this quicker & easier than trying to concatenate fragmented CF rules manually.
I attach a blank Excel workbook that contains the VBA code (alt+pf11 to open the VBA editor to see the code). its a bit rough & ready, but works for my needs. you would have to modify the VBA code to suit the CF you require.
know I know MS are never going to fix this issue, I should probably try & find time to revisit this code & improve it.
-
Jan Kroone
commented
The arrogance of MSFT in casually responding "not this time" (after all this time), naming it a "nice improvement" not acknowledging the severity of the problem of us constantly having to repair CF and last bust nut least trying to let us "ignorant users" believe this problem is too hard to solve, is beyond comprehension.
Of course, there is no question the problem should be solved asap in a professional way, but if this really is too much asked "this time", until "that time" arrives I would gladly settle for
- option to let the user lock the specified defined range (as suggested by Lawrence Dunn)
- improved user interface including allowing for filtering defined rules, ranges and formats. -
Lawrence Dunn
commented
I apologise to all those, like myself, who get automated emails whenever a new post has been made to this item over the past ~5 years - my post here has just added another one to your inbox, but I really felt the need to respond to MS's 'computer says no' response.
MS - you might as well take this whole feedback website down if you are not going to listen to the feedback of your customers who are using Excel day in & day out & have been telling you for 5 years that the way Conditional Formatting is functioning over time is not good.
I find it really hard to believe that the Excel team can deliver loads of new tools like Power Query, Power Pivot & all the other new & relatively complex features, & yet doing something to address a known customer complaint - the fragmentation of ranges used in Conditional Formatting is 'too hard'.
If it really is 'too hard' for your current programmers to fix properly, then just give us an option within the feature to 'lock' the initially specified range to prevent Excel fragmenting it & then its a simple matter for the user to change to a new area. OR...
....when a user specifies a range, the Top Left & Bottom Right cell address of a range that the CF is to be applied to, then if they add or delete rows or columns inbetween those two addresses then just change the two addresses to adjust to whatever rows &/or columns have been added or deleted.
Anything just to stop the applied range fragmentation.!
-
Jesse Law
commented
I am a power user and evangelist of Excel in my current company, and every other company I’ve ever worked at. I’ve been teaching people index match for 15 years, and am a corporate beta tester and champion of the Excel workflow. That I still spend multiple hours per week fixing conditional formatting problems, and to get this response after all this time is crushing to me. I am going to rewrite my Flows and PowerApps in node.js and abandon the entire 365 ecosystem. This is just beyond the pale.
-
alex
commented
Or just that it understands the concept of a TABLE. I want the rule to look down the column(s) in the whole table. So that if you add a row....yes, the rule also applies to that new row. (I guess that tables are a type of dynamic range? I don't know.)
-
JP Davey
commented
How about restricting it to non-dynamic named ranges then? I'm sure you'd still satisfy 99% of the issue that way.
-
Myles
commented
Named ranges being dynamic is the whole point of this suggestion.
Adding a conditional format to a dynamic range, should allow for edits to the range without messing up the rule.If this sort of a change is not possible, please give some level of priority to updating the conditional rules manager. It feels like a trip back in time every time I open it up... and not in a good way.
-
Bleddyn James
commented
@MSFT you can not be serious.
This has been a product suggestion for almost 5 YEARS!
If "not at this time" when?It is one of the most voted items not only in its category but across the excel suggestions.
Responding by saying "it's in the too hard basket" is not a valid response.
Please provide an ETA/road map of when you actually intend to resolve this product suggestion!
-
Futski
commented
I guess this just proves how un-maintainable this 30+ year old app has become.
It's too difficult to add named ranges to conditional formatting. But wait, they can add all sorts of new features like the Dynamic Array Functions (#Spill function etc).Bottom line is there's no money in it when the fix a broke conditional formatting, but adding new features might attract new customers. Follow the money....
MS needs to focus on making the existing customers happy, not trying to capture new ones from an already saturated market.
It's time for a complete rewrite of excel. But that's not likely to happen, at least not before I die of old age.... :-(
-
Roy
commented
@Anonymous: Mighty good point!
I'd hate to risk they'd stop there, but it would be a super big thing even if they did.
-
Anonymous
commented
Even the most constrained implementation of conditional formatting through named ranges (for example, the named range must be fixed rather than dynamic, and consist of only one area, and have no relative references) would transform the feature from essentially unusable to entirely usable.
-
Roy
commented
Conditional formatting has no follow-on in calculation: Nothing depends upon it. It ought to be applied last since it does not feed into anything else. (It might itself depend upon plenty of cells, but that's a very different thing and not germane here.)
Being applied last, it would know what the value for any Named Range is, dynamic or not, since affecting nothing other than what we see on the screen it could not influence the value of anything.
There is NO function or menu item that looks to a CF and then acts to affect the traditional elements of a spreadsheet: it is a "dead end" in this respect. Since it should be generated last in a calculation, when all values are known and only display needs performed, it isn't actually "quite complex" in any way, shape, or form.
If we are to be told that it fits into the calculation parade in some other manner, well, that's just stupid. Ought to be fixed itself. But I am sure we are NOT to be told something like that because MS never bothers with actually explaining a "get bent" message to the users.
Stupidity isn't attractive. Or useful.