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)
197 comments
-
Lawrence Dunn
commented
Message for those Users receiving updates on the 'feedback to improve Excel Conditional Formatting' thread - like many of you I guess, I periodically get notification when some adds a post to this thread.
After getting fed up with heavily fragmented rules for the umpteenth time on a document that I've been using & evolving for years, I have developed something of a work-around to the issue of fragmenting Conditional Format ('CF' from now on) rules, that I thought I'd share as it might help others here try or adopt an alternative approach to the subject.
Rather than write the rules into the CF tool pop-up box, I've written a macro which starts by clearing ALL CF on my worksheet, & then it creates my CF rules afresh.
While this does not prevent or avoid fragmented rules, what it does mean is that when you need to modify the CF rules, or add new ones, you can quickly, easily & painlessly get back to your original starting point prior to the fragmentation.
I've actually found it more convenient to add new CF rules via copy-pasting sections of code & modifying key pieces of info (like column or row reference, or the formula controlling what receives the CF), rather than creating a new rule in the CF tool pop up.
I've attached a text file containing a thrifted version of my VB module, just cut & paste into a module on your workbook, & modify the key pieces of data like column & row references & the condition formula & formating to suit your needs. I've commented the code.
I hope this helps some of you work around the CF fragmentation issues.
-
Bleddyn James
commented
@Excel Team [MSFT]
How many votes are required for this to be incorporated into the next release of Excel?Managing and maintaining conditional formatting without Named Ranges is an absolute nightmare. Especially if you have to update the formatting.
I currently have thousands of conditional format rules, that would consolidate into one rule to manage if Named Ranges were an option.
Screenshot attached....
-
Chris Newman
commented
This needs to happen!!! Would make life so much easier!
-
Roy
commented
I noticed, in the experimenting for the last post, that as rule never "exists" for the user with no "applies to" defined. It will at least default to the selected cell, set up the rule, and present it fresh and new with at least that selected cell in the "applies to" box.
One cannot get it to take anything not a cell address of some kind. If it does not resolve as a range, it is ignored, life moves on.
But if the value for it could be given, and be pretty unacceptable, BEFORE there is a "previous value" for it to restore... I wonder if they considered this possibility. After all, it's been around long enough to pre-date VBA and Excel might've figured there was no avenue to try to make it happen. Might not have closed off the VBA route.
So if one creates a CF rule with VBA but specifies a NR for the "applies to" data, I wonder if Excel would take it and keep it, presenting it at least one time via the CF tools in the Ribbon. ('Cause it would probably literalize it then, though maybe looking at it and closing without making any change of any kind...)
If so, and if it evaluated it and it really worked as a CF rule, that would do what we want. Creation and changing to a different NR would all have to be done with VBA, not the amazingly obtuse Ribbon tools, but it would be what we want. Not how we want it, but what we want. Especially if changes to the range in the NR were acted upon in the area of effect for the CF rule.
-
Roy
commented
I'll make an important point about this "they're formulas" thing, then a wee little counterexample, then I'm done with that because this is not a constructive, and therefore worthwhile, argument.
Important point:
However Excel looks at it, even if it regards what IT itself calls NR's as formulas instead, it makes NO difference. If it takes the entries at face value and a range is a range is a range, then it works using the range, if a range was defined. If, like the INDIRECT() function, there is a resolution process because it looks at all of them as formulas, then it would resolve some as ranges and go forth and resolve the rest as not-ranges and give an error. (Of course, either way supposes some resolution process, but either way, an actual range is acted upon, immediately, changing the CF value from the NR name to the range it resolved, and non-ranges are simply ignored with the "applies to" box's previous value left unchanged.)
So the whole thing just doesn't matter.
Sidenote: The "applies to" box uses the "=" too. Are we to suggest that it too has a formula in it? I tried a SUM() for the range it was using, but it just ditched that and restored the previous value. It is perfectly happy to function with a value of "=$A$1:$A$12,$A$1:$A$12" (that second string is an actual duplicate, and is not resolved by Excel as already covered by the first range string, then removed so clearly there is no effort to do more than list the cells).
So in this we see Excel using the "begins with an "=" character" yet Excel looks for a range, not a formula.
And now the wee counterexample to the main contention:
If the NR's are all formulas because they begin with the "=" character, then FORMULATEXT() ought to return those formulas as text. It should look at the value stored for the NR, see it is a formula, and return the text of the formula. It does not. Instead, it reads that value, resolves it to a range, then acts upon that reading the range's values creating the matrix {1;2;3} and applies FORMULATEXT() to that. So, not seeing the NR as being a formula.
The only way out of that is to suggest that it queries the Name Manager concerning the NR, and the Name Manager gives it the range that the NR has been defined as. But... then the "'Excel Name Manager 'black box'" is giving a range to CF, not a formula and therefore CF is never having a formula to operate on, only an true range. In the way of saying that last, it is being given "A1:F4", not "=A1:F4".
Either path above means CF always gets a true range to work on, if one exists for the NR, and could therefore let us use NR's. And if not, it simply ignores the entry and restores its previous value. So whether it is truly a formula or not, it truly does not matter because CF always has a true range, not formula, to work on.
Fin.
-
Anonymous
commented
@roy
A1:F4 is range...
=A1:F4 is a formula
you don't give name, like "sales" to a range such as B1:B4... you set the formula =B1:B4 to the name "sales"
In fact you could also set an entire matrix formula to a name, and therefore the name sales could get the formula =SUM(IF(A1:A4="North",B1:B4,"")).... could it be possible that such formula assigne to "sales" have a conditiional format? would you say this "sales" is a named RANGE?
-
Roy
commented
@Peter Collins:
Yes, exactly. Each avenue I mentioned seems to lead to complications. Except the simple one. No checkboxes, no locking, just a simple:
1. Excel sees there is a change.
2. It applies the change to anything expressed literally.
3. If the change was in a portion specified by a NR, it essentially ignores it because the NR itself did not change.Done deal. Specify a NR in the "applies to" box and unless an edit changed the NR itself, there is no effect on the CF.
And, of course, Excel always retains and presents the NR itself in the "applies to" box. It would NEVER change it to a literally specified range for ANY reason.
And that would be all that was needed. If one were coming at it from the other direction, one would simply not specify a NR. Or perhaps one could be entered, maybe in quotes (so they have to do the work, not us!) and Excel would know to convert it to a literal specification. That way they could enter things easily (and accurately: especially for complicated, multi-part ranges), letting Excel take it from there. And we could have real NR's used and retained.
Speaking of using NR's, they can be built to be relative (A1 vs. $A$1) which leads to lots of problems, usually, where CF is concerned (one of the two other problems I find with it) but might have good uses too. So it'd be nice for Excel to retain that when literalizing the NR's in its application of the rules. Maybe... 'cause the weirdness that leads to are still there. But maybe.
And if NR's could be used, they could be dynamic via formulas, for any interesting uses that might have. The dynamic ranges returned could be returned as absolutes ("Dynamic absolutes"... that sounds wrong! But it isn't really.) in the formulas and so be useful in part of the way the last paragraph mentions but without the problems caused by relative addressing.
-
Peter Collins
commented
@roy
What if, if you apply it to a named range... it applies to a named range?
-
Roy
commented
@Jaime Segura: that's just not correct.
There is a practical problem for Excel vis-a-vis NR's and CF.
Say you have a NR for cells A1:A10. You set up CF that applies to cells A3:A9. Over time, whatever way it happens (and there are plenty of ways), the CF comes to apply to cells A1:A10. This is sheer coincidence, there was never an intention to apply it to the NR, as such, it just, this moment, applies to a collection of cells that includes that range/NR.
If Excel noticed and "collected" things by changing the reference to the NR, havoc might ensue. Over time, not necessarily this second, but as more of the "over time" changes occur, that would have led to a different set of cells that the CF applies to.
If it uses the NR as long as it can, but then drops it when something conflicts (perhaps one drags away a cell from the A1:A10 area), then... what really was the point in ever showing it applying to the NR? Why do it?
Our desire is coming from the other end of things and it makes perfect sense from our take on things: we want a permanent, easy way to ALWAYS have the CF apply to some given area. Something is dragged away? It loses its CF. We only want the CF to apply to the NR. Period. Don't want Excel to apply it to that plus the odd cell here and there that comes to have it. Just the NR. So it makes perfect sense: this is why Excel would do it.
The two points of view are not really reconcilable, directly. Either you want the CF to apply to a given area, period, or you want to allow it to expand and contract and have isolated areas too. Obviously, there's a sliding scale here but at some point in between, the two approaches lack compatibility of a direct sort.
If, however, Excel allowed the use of a NR (along with any other way of specifying what to apply the CF to), AND provided a checkbox that let you "lock" the reference, that would work for both sides of the coin. The area of application could still change, if the NR changed its range, or perhaps a row is inserted, but it would be because the range, however specified, changed. Call that "organic growth" (or shrinkage). At this moment, I think most can see how this could start to shatter too... so keep it simpler, perhaps: perform the locking by only applying it to NR's used in the "applies to" information. Specify a range or cell directly and nothing related to it expands or contracts, and if it is grabbed hold of and dragged off, it is lost from the "applies to" area. Drag a cell out of a NR portion and it loses the CF, but the place it was dragged from keeps it. (Contrast with a directly specified range which loses the cell permanently.) No need for a checkbox.
So it would simply see a change made, then change any directly specified references. But it would make no change to any NR specified, so when done editing the "applies to" list, the NR would still be there, complete and unaffected while any other specified cells might have gained or lost, as appropriate.
At the moment, I almost never use CF because of this and two other obnoxious "qualities" it has. Fixing this would go a long way toward making it useful to me.
But it is NOT because NR's somehow don't exist.
-
Peter Collins
commented
Microsoft probably need to redesign how Tables work entirely. Or just make a new Tables program and we'll use that instead
-
Jaime Segura
commented
Maybe the problem is that NAMED RANGES don't exist..... everybody thinks ranges are being named, but actually names are asigned to formulas, where in the end a range is also a formula. Therefore I think for Microsoft to apply conditional formatting to a NAME (which is a formula not a named range) might be a bit more dificult.... the only place where I've seen that Microsoft managed to assign such formulas (determining that there implicited related to a range) is on List validation
Where I think could be more easy, could be on applying format condiitonal to ListObject (Tables) which already have structure...
-
Anonymous
commented
@4am : In fact, I think the first time I realized I couldn't use a named range in the "Applies to:" field, I was hoping setting a table column as a named range and applying a conditional formatting rule to that range would allow to paste/remove a row in the middle of the table without having the conditional formatting rules go wild!
:-( -
Anonymous
commented
This really should have been implemented years ago!
It feels like some great additions to Excel that should make it easier to work with were not completely implemented and now make things messy and hard to work with.
Named ranges, and even more so Tables and structured references, would be so great in making complex sheets easier to read and audit. Making them reliable...
BUT you can't really use them consistently because they don't work with some parts of Excel... it's not like we're talking about different programs here! It should be an evolution... but by being incomplete it keeps causing trouble and we can't move on!
I ended up here because I was looking for a work around for a bug with a "newer" part of Excel : Power Query
When refreshing a table created from a query... the conditional formatting gets all messy (like it would if you added/removed rows in the middle of a table)... I was hoping to use a named range (set to the table, like you have to do to use in a formula for a conditional formatting rule) before I remembered you can't use named ranges in the "applies to" field...
This is so frustrating!
Excel keeps adding "new" features... but they don't work with what was there before! There’s no compatibility INSIDE Excel itself!!! Allowing conditional formatting to be applied to named ranges would allow for many work around so we could use many parts of Excel together...
Please give new feedback!
This issue has been addressed in different suggestions splitting the votes, but it still has quite a number of votes! And please think of the usability of Excel as a whole, many many people who haven't found their way to this suggestion would find their use of Excel simplified and less frustrating with this ability.
New features might be nice marketing, but if you can't use them together with the other parts of the program, you're only failing to meet the expectations you set and eroding the trust in your next promised feature ("Great, something ELSE that won't quite work!") -
4am
commented
While some of the issues this would solve can be solved using structured references/tables (which automatically "format paint" new rows, including conditional formats), until structured references can be used in all the places that named ranges can, it would be nice to be able to use Named Ranges in the Applies To field. Especially dynamic named ranges!
-
John
commented
I am looking for exactly this functionality! Please make it so!
-
kevin
commented
About a year ago I also commented on the issues with copying data with conditional formatting into a column and the resulting "copying" and creation of small ranges with in the conditional formatting rules manager. Also the issue with not being able to apply conditional formatting to a dynamic range.
I have now found that with the data in a "table" that the conditional formatting is applied when ever I add new data (paste values only) into that table and the range shown in the rule manager has updated to include the new row, not adding another small range as previously. The "table" also acts as a dynamic range so if you create a format on the first row it is applied to every cell in that column.
I'm not sure if this solves everyone's issues but it has helped me
-
Christopher Tindall
commented
Futski... I fix proposed by Graham in the initial solution is clear. We need to be able to link conditional formatting ranges to previously defined 'named ranges'. These conditional formatting ranges would then be unaffected by modifications to cells, rows, and columns.
Implementing this functionality is not very difficult from a programming standpoint. This is why we are all stupefied that this has not yet been implemented.
-
Futski
commented
I'm not trying to make excuses for MS here, but maybe they don't know how we want excel to work.
For example, when we copy a cell that is formatted using named ranges to another cell in a different column, in the same table, how should the formatting be copied? Should it make a new rule? Or should it abandon the source formatting and assume the formatting of the new destinations column. I'm for the later.
What about if you have a format spanning 2 or more columns and I want to insert a column in the middle, should it be excluded from the format, or should it assume the formatting of the columns it was inserted between. And what about moving a column with unique formatting between the two spanned columns? Once again, how should that be handled?
Let's give MS some ideas how to fix this existing disaster instead of just complaining about their lack of action.
-
Phil Edwards
commented
I think if we get over 1000 votes, Microsoft might consider our requests. One can hope!
-
Anonymous
commented
Not sure why I bothered to vote, but what the ****. Has the Excel team been abducted by aliens?
