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)
217 comments
-
Bleddyn James
commented
@Excel Team [MSFT]
Why bother have a feedback suggestions area if you aren't going to action the feedback.
From what I can see this is the 11th most voted item to date.Almost 5 years on there are still users requesting this functionality and yet still no action to introduce this functionality.
Extremely disappointing.
-
Peter Collins
commented
Can we just not copy conditional formating over to tables. When is it ever needed to copy conditional formating to another table? Conditional formatting should only ever apply to the table it's applied to and not whatever table data is being pasted to. Copying conditional formating should be done from the conditional formating pane itself and into the conditional formating pane, i.e. I can copy paste formating rules.
Stopping conditional formatting from being copied from cells would be a huge improvement in of itself.
-
Futski
commented
@David Ackroyd, I like your solution for cells that are copied outside the defined range. An error message would be perfectly appropriate as the rule would apply to a named range, not a specific cell. I'd opt for a warning with a "Don't show me this message again" option.
-
David Ackroyd
commented
I agree this is pretty essential. I've had issues where users sheets are running really slow because they have tens of thousands of conditional formats that have built up over time, just due to users innocently copying cells...dragging formatting down...moving rows...columns.
We use tables and named ranges quite a lot so if these could be referenced in the Conditional Format formulas this would resolve a lot of this.
Plus this greatly improves bug fixing of sheets
If I setup a rule that says:
=IF($A6 = $B$2,1,0) and the applies to is =$A$6:$L$18If any columns get added...moved, cells moved and the formatting stops working it's quite hard for anyone to try and figure out what was intended. However, if my rule says:
=IF([@SHORTCODE]=rngSelectedCP,1,0) applies to =tblCustomers
Then it's much more robust and far more stable.
On the various comments on how would this be resolved if cells are copied out of the table...if I explicitly use a named range in a conditional format and I copy it to a place that makes that rule invalid, then i'd expect an error or at least a warning stating the Conditional Formatting was not copied as could not be resolved.
-
Alek Vila
commented
I understand this is your interpretation. It is not what I intended.
By the way, I clarified my intention with "... email clients doing their thing," which came before your comment.
-
Roy
commented
@Alek
No, no confustion. You stated:
"The problem with using VBA or Macros is that the code is stripped out when the file is emailed."
and that was it. Beginning and end of comment. No modifers, no "by organizational policy." Nothing. Just that and only that. The statement, as stated, says it is done by the programs in question, not by someone's choice.
I'm not sh*tt*ng on you for it, just that's what you said and I pointed out it is NOT correct. Leave a thing like that uncorrected and not only will it keep coming up as a set fact (even though it is not), confusing this issue, but some people will act, or rather not act, thinking it is set fact.
No confusion at all.
-
Alek Vila
commented
You might be confusing me with someone else.
-
Roy
commented
@Alek
Sure, sure. Lots of folks cannot use macros by organizational choice. And by theory as well when boss types want to have some ability to delve the spreadsheet to assure themselves it is "correct" and can be trusted. Since few seem to have any VBA skills, they cannot evaluate a macro for this purpose and so the spreadsheet has to be accepted as holy writ.
(You presented it as a feature of Excel and I was addressing the fact that that wasn't so.)
-
Alek Vila
commented
That sound reasonable, but in our case with thousands of clients our department has been instructed not to use Macros or VBA because of the hassle of the various email clients doing their thing.
-
Roy
commented
@Alek
That would be an organizational thing that you might be able to overcome. Or possibly some option set in Outlook or Excel, but I've not heard of one.
It is certainly NOT something that HAS to happen. I can email macro-containing spreadsheets without them being stripped and I am doing nothing special. And that's with working for an organization that definitely makes some perverse "higher level" choices for everyone.
Seriously, a macro to reset the conditional formatting has to do so very, very little actual work that one might just set it to run automatically on every little action taken and one ought never really notice it, ever. Might want to modify that a wee bit to not risk interaction with other macros, but...
(Re-setting 250 rules might task it. But setting them on a range of 300,000 cells is the same work as a range of 4 cells. Though I suppose Excel would re-calculate to make sure it's displaying according to them so... that could be quite a burden. But one could test for the "noticeable point" before dialing it back to On Open/On Save/Button to let the user choose when/etc.)
-
Alek Vila
commented
The problem with using VBA or Macros is that the code is stripped out when the file is emailed.
-
Andrew Ashurst
commented
otimo solucao Celia. Parece pra me imediatement comprehensivel e elegante. Ha outra solucao de vba aqui de Laurence Dunn, e eu tem aquele tambem. Nao tentei usar o solusao dele, mais, so dei um olhado e o codigo parece bem mais complicado do seu. Para ser justo de Laurence tambem o solucao dele pode ser mais sofisticado tambem, mais seu parece suficiente bom por me.
Hope my Portuguese is intelligible :-)
-
Celia Alves
commented
I also use VBA to solve this issue. I usually make the macro run on Save so that the next user finds the file properly set up.
This video explains how I do it. If you watch it, please read the video description that includes important updates.
https://www.youtube.com/watch?v=pC98JKrfC3g -
EPSU
commented
Just wanted to take a moment to thank Lawrence Dunn for sharing his VBA "CF-tidying" code, complete with useful comments. Thank you! @|:~)
-
Christopher Tindall
commented
The only approach I've found to control Conditional Formatting is to prevent user ability to copy and paste rows or cells. This requires VBA buttons to add rows or columns (by inserting a row, copying the info from the row below, then clearing all of the cells for that row). I've used this approach on a 1000+ row spreadsheet with 12+ people simultaneously inserting and deleting rows... not one change to the 10+ conditional formatting formulas.
MS understands this issue is important to the users. This suggestion is ranked #11 in number of votes (an a few higher on the list appear to be solved by the latest update). This board is too valuable for direct user engagement for MS to ignore.
That said, Conditional Formatting is a volatile function without a decent workaround, which makes this difficult to correct without a big update to existing Excel coding. These kind of updates bring risk. Since this is essentially a visual issue, it has less priority than improvements which will increase user efficiency. This is evident in the latest updates which bring very powerful tools (AI, Ideas, New Data Types, Data from Pictures, and Dynamic Arrays). These are added features, not corrections to existing features, so there is less risk.
-
Lawrence Dunn
commented
please take a look at my post a few comments below & the attachment.
I found a 'work around' for me was to write a macro/VB code that contains the conditional format range, rules & formats required. & then run that.
Sure, overtime the Conditional Format gets fragmented & messy, but then you can just re-run the macro to clear all the prior CF & reset it all.
Once you have the VB code, I found it more convenient to just duplicate sections & adjust things in the VB code, than to write new rules in the CF popup window tool.
Take a look & give it a go, its working for me.
-
Futski
commented
@Jan, you are right, there is a lot of shouting at deaf ears. Unfortunately the selectively deaf ears don't want to hear what we're saying. And it's not just this topic.
Is anyone aware of any recent MS response to any topic??? My guess is that MS has completely abandoned this forum. But they leave this out here to make us feel like they are listening. So sad that a perfectly good tool is going to waste.
-
Jan-Rienk Hemminga
commented
@Tony Although you might have a point, I do not think this is a place to raise this issue. This is where we’re trying to get conditional formatting to work with named ranges. This is something that data validation already does. (On a side note, if table names don’t work for data validation, I think you can get it to work by using a name that refers to the table.).
@Microsoft: So what’s the status on this? This has been the number one suggestion in formatting for a couple of years now. And it feels a lot like we’re shouting to deaf ears.
-
Tony Smith
commented
Just want to say this would be super helpful. Not sure if anyone else has mentioned this, but updating the Data Validation dialog to have the same features as conditional formatting after this update would also be HUGE. Data validation is super important but often difficult to manage.
It might also be helpful to have a designation similar to $ such that the range does not split but instead re-applies to everything between the first and last cell. For instance, when inserting a copied row into row 3 that does not have conditional formatting, if the CF applies to box reads #A#1:#A#7, it would update to #A#1:#A#8 instead of A1:A2,A4:A8
-
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.
