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 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.
Thanks,
Steve (MS Excel)
170 comments
-
Hảo Lưu Tuệ
commented
Completely agree with @Roy, if it's too hard to maintain a named range/table, at the very least please keep the normal range instead of turning it into that abomination.
-
Anonymous
commented
Ballmer is turning around the Clippers faster than this much-needed change is taking.
-
Ice Apitsada
commented
OH PLEASE Microsoft!
This is one of the most common function!It's been 4 years already, how long will you need to prioritize this?!
-
Alex Lush
commented
@Roy bonus points for using the phrase Balkanizing. I had to look that one up!!
-
Roy
commented
"Bolted" is a good word. Bolted in place though, not really on.
Here's some wonderful behavior that needs to go away. Name a Range, say I1:N10, place a condition on row 1, once in place, edit the "Applies to" to the name of the Range. It atakes it, but permanently converts the Named Range to its absolute coordinates.
Not good. But... given that if I drag a row out to somewhere else, it's going to Balkanize the applied to range (drag out row 7's cells and drop them on the right cells in row 17 and it now applies to $I$1:$N$7,$I$8:$N$10,$I$17:$N$17... forgive me if I drop "$" here and there), I guess this is better than Balkanizing my separately Named Range. But it shouldn't happen. It should stay applied to the Named Range and, AT MOST, add the new location of the dragged cells.
Sure, you MIGHT want those dragged cells to carry the format with them, though dragging has to be a fairly primitive thing nowadays, it's not 1992 anymore, BUT you cannot possibly want to lose the format in their original location unless you dragged the entire Applied to range. Sure, some unusual situations could exist, but... "unusual"...
So why Balkanize the original Applied to range at all, and therefore, since you won't be doing that, why not allow the Named Range to stay instead of converting it? Because, clearly, they convert because they intend to Balkanize, and accept that in their very souls. (I know, but whatever passes for souls in them.)
Worse, I drag the cells back where they came from. Does it evaluate and restore the simple range? No, it replaces the Balkanized range portion with a new Balkanized range portion. Sigh...
OK, standard complaint stuff, I suppose.
But say I Copy and Paste that set of cells. It does NOT carry the conditional format with it. But further BIZARRE behavior: if I Copy and Paste|Special and tell it to carry the format, it will. OK, that's not the bizarre part. The bizarre part is that now simple Copy and Paste DOES carry the format with it. Can't turn it off.
I'll grant that last (can't turn it off) might not be intrinsic to Conditional Formatting. You get a brother behavior with copying outside material and pasting it into Excel depending on how you last had your delimiters set for Data Import. And similarly, that can't be undone except by closing and re-opening.
But why does it turn on at all? I don't mind "sticky" behaviors when they are documented AND can be turned off when done with them. Avoids needing dedicated shortcut key combos or macros for simple but short-lived tasks. Still... bizarre.
And really, why?
I wonder how some of this plays out with deleting of rows and columns with attendant re-insertions, or unrelated insertions. I bet the order gets to be important.
But the whole function is so cr*ppy that I literally never really make use of it. It's just too d*mn cr*ppy in particular and on the whole.
So I feel a sense of loss and empathy, but mostly sympathy for those suffering trying to use it.
Ugh... and that whole "mostly we do this" relative thing that it really doesn't apply according to strict rules, but rather slightly flexible ones...
"Bolted on" also applies in that it's not truly formatting and have fun trying to read it and use it with VBA. It's laid on as if it's something your monitor is doing, not Excel. Which might be an interesting business idea: a program that intercepts Excel's (or any other's) feed to the monitor and allows you to interact with it when it's still data, before it's pixel hash. And that can save its work somewhere in the file, or with a brother file. I bet someone else would be happy to toss in an app that does real conditional formatting (and other things) with Excel material (now reduced to a "fluffer" role while the apps downstream of the Interceptor do your real work).
Once the Interceptor existed that is. Very different programming one would think, but once it exists and is in place, the app community that uses its services could become very varied and quite rich.
MS is clearly pushing us out of Excel except as a rough data store (for now) and into PowerApps (lots and lots of subscription money and over-purchasing of licenses along that pathway). They are slowly, for now, removing functionality that is not "on point" for this.
So now? Maybe they will do something. After all, the Admin comment that literally promised nothing is only 3½ years old...
Good luck guys. I voted and I'm sure ol' Steve there is waiting with baited breath to count that vote and get "We" to prioritize accordingly.
I still won't be tainting my ten foot pole touching it.
-
Mark
commented
I've been struggling with the problem of fracturing ranges in conditional formats for years. I finally got around to thinking about using named ranges and find that this will not solve anything. The whole conditional formatting functionality is far too important and useful to be treated like a bolted on afterthought the way it is.
-
Roxy
commented
I want to have this feature too!! It is driving me nuts!
-
Peter Tholstrup
commented
Same goes for spilled ranges in new dynamic array aware excel. As the point of spilled ranges is that their extent downwards is dynamic, not being able to apply Conditional Formatting rules to eg A1# seems counterintuitive. Especially since it works fine in data validation settings.
-
JC
commented
Echoing Michael Marcer, it took a long time searching to find a discussion on this topic, only to confirm that sad news that the feature being discussed does not exist. I created a couple formulas that determine the edges of a pivot table (which is, of course, dynamic based on filters applied). The formulas identify the corners and relative size of the table, and the conditional formats I created would have then been applied to the dynamic range via Address formulas in Applies To.
-
Michael Marcer commented
I should also mention that this took me a **** of a lot of digging to find, so there are probably a massive number of people who just gave up long before getting here.
-
I'm assuming they feel that 563 people isn't enough to put the work into adding this feature, which is unfortunate because it's a waste of system resources to check a vast number of empty cells, not to mention looks like ****. I can't just use an IF statement to skip the empty cells, because I need them to be formatted in certain areas.
Work gives me a potato for a laptop...
-
Jaime Segura
commented
I think that it should go beyond names. Microsoft makes all new tools rely on table (ListObject), therefore Conditional Format should accept these "structured ranges"
An improvement on the user interface is also needed... but I don't know how much they'll take to apply it. For instance, I'm a native spanish, and use Excel in spanish... Many years ago, I wrote a comment regarding huge errors on translation... and,,, wait, wait... till Excel v2016 where miracoulsy included the corrections I made, although they were actually facts, and shouldn't take any discussion to apply
-
Anonymous
commented
Same here - I use a macro but believe the real solution lies in MS modifying conditional formatting to allow names ranges
-
Lawrence Dunn
commented
For all those who have posted on this subject, I got so fed up on one of my sheets with the conditional formatting becoming increasingly fractured as I & other users worked with the sheet, that I recently developed a work around.
I've written all the conditional formatting rules into a macro that 'clears' all conditional formatting & then re-applies the rules.
Just thought that the approach may help many others here
-
Philip Carpenter
commented
I agree, this would be handy. As it is conditional formatting can really bog a spreadsheet down, but using conditional formatting in combination with a dynamic named range (defined for example with offset), creates an easier way to manage conditional formatting.
-
Anonymous
commented
true Daniel.
my bad, got confused about the real purpose of this change request: it has been opened for so long, that its true purpose kind of faded away in my memory.yet, the trick I remind people below (the INDIRECT formula) might still help some people, as it provides a workaround in "another place" (your point) where the Conditional formatting kind of fails to handle named ranges simply as one would expect...
zifnab.
-
Daniel
commented
To anonymous below, the method described in that article only applies to formula used in the Rule to calculate when to apply the formatting. This thread is about how to use named ranges in the "applies to" field defining the range which should be formatted, and the INDIRECT method does not work here.
-
Anonymous
commented
in the meantime, use the INDIRECT formula to easily achieve this.
as shown here:
https://www.get-digital-help.com/2012/10/15/how-to-use-a-table-name-in-data-validation-lists-and-conditional-formatting-formulas/zifnab.
-
Eddy
commented
tap tap tap ...
-
Dark
commented
It has been 3 years since this question was posted, are we close to getting this implemented?
I maintain a large, dynamic spreadsheet with 8 fairly complex formatting rules. Every time I copy and paste a row I have to redo the conditional formatting, which is very annoying and a waste of time. If I don't fix the formatting each time I end up with hundreds of conditional formatting rules and my spreadsheet starts to freeze up.