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 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.
Steve (MS Excel)
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!
Adrienne Perry commented
I also have spreadsheets with continually changing data so rows are added / deleted regularly. I am always shocked to see the massive number of rules by the end of the year when I only had 5 to start with. Please fix!