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 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.
Rob Hines commented
Yes. I found this looking to see if it were possible.
Excel is SO frustrating. Things like structured references almost take Excel out of the 1990s. And then, you find that sometimes they work...sometimes they don't (after like 3 hours).
Just as frustrating... I can't just break down and use VBA automation because everything I do has to run on Macs. And we all know that "Mac is different".
If Excel doesn't modernize...I'm going to find a replacement! And NO, going to Office 365 will not be acceptable.
i agree i have more 1000 lines of condicional formatting, its imposible to find a mistake correctly!
I like this idea. It would be very useful for tables that are refreshed via ODBC for quickly highlighting anomalies.
We desperately need this - I have a shared file so I can't adjust conditional formatting without unsharing it every morning - maintaining named ranges would make a world of difference
I agree... Excel needs to add this.
This issue makes conditional formatting very difficult to maintain over many spreadsheet edits! My number one frustration with Excel.
Rachel K commented
Yes, please. This would be so helpful.
yes, I recommend it.
I agree. It would make managing the presentation much easier.
Absolutely essential - couldn't agree more with all the comments in this thread. Conditional formatting should be "set and forget", not something you have to redo every time you insert a row or copy/paste some data.
yep, same here. I'ld love this feature
This is a common problem, assuming that you have set up the Conditional Format to be cell independent, I normally do this by having a Column/Row that has a code for the Format 1,2,4 etc the 1,2 etc can be the results of a complex formula.
=Colour=0, where Colour is a Named range, or could be a Cell.
Avoid having the complex formula in the Conditional Format.
Then a simple Macro can copy the format from a Master Range/Cell.
I use these two simple Macros:
' This appies the ColourPallet Named Range to the Report Named Range
' This applies the Colour Pallet Table to the required
obviously we would need this - that is one reason why we enter a named range reference in the first place
I definitely need this. Have the same problem
Certainly. The present situation is NOT NORMAL. Excel tables are wonderful, until it comes to conditional formatting.
This would be an incredibly useful upgrade. I have a workbook used for staff planning, with 12 conditional format for the main body of the main sheet (20 staff x 365 days) and when people start copying details it all gets very inefficient.
I spent some time figuring out that this is happening and it was so frustrating. Fixing this would certainly make the conditional formatting much more useful and reliable.
I could so use this functionality - with all you can do in excel, really surprised this is not already there
I completely agree! It makes complete sense that Excel should allow named ranges in every location where ranges can be referenced.
I have the same problem. Please fix!