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)
PLEASE!! This is very annoying.
Still an issue in Excel2016. This should have been done LONG ago.
Austin Miller commented
I am making scheduling documents for work. Over 100 custom spreadsheets will have to be made. If a named range was not broken to an absolute cell reference as it is, I would have to do so much less work. Fix it.
No brainer...MS when will it be available?
Absolutely. Please prioritize this.
T Wilson commented
The only way I have found to apply conditional formatting to named ranges (where they are a single area) is to add the following into the conditional formatting formula
The named range is 'resources'. To test for the value of the cell is greater than 0 add to the formula
The applies to range could be =1:100 for columns 1 to 100.
Please fix - having to constantly go in and redefine the "master" applies to rule and delete all the extra rules is a pain in the butt
Come on MS! How long to resolve this issue? The mess of illegible conditional formatting rules created by this issue soon renders a worksheet very difficult to manage, and requires a clean up of rules irregularly which is complete waste of time.
does anyone have an example of the VBA that detects all conditional formatting rules that apply the same formatting, and then convert that into a single rule, that extentds the applied to range..? My skills dont go quite so far..
I stumbled across this when looking for help on how to reference table columns in the Applies to section, and couldn't believe it when there was no solution!
The only workaround I can think of is to use vba to completely erase and reset up the conditional formatting rules on startup, however I use the spreadsheet in Excel Online (so no vba) as several others may also be working on it so I would have to open in it Excel every now and again when no-one else is using it just to run the vba to fix the duplicate conditional formatting formulas. Doable but very painful...
I agree. If this is not being corrected, does anyone know of a way to prevent the "applies to" range from being broken and adding new "applies to" lines for each line addition to a table?
My spreadsheet quickly grow from about 50 rules to hundreds, and subsequent editing is a painstaking task.
Pls implement named ranges into conditional formatting. Currently conditional formatting only works for static worksheets which is hardly the point of excel.
I require this for a dynamic range of value that increase as I add rows of data. I have the ranges named but cant use this function. Is there a problem with being able to program this functionality?
This is a major pain in the back end. Come on MS, it's time to get this fixed.
I want this too! Thanks!
10 years an excel professional
Please pick this up again, would be useful for dynamic tables from another source (eg. SQL) where the row count changes on update.
Jerry Norbury commented
This one is still a problem - can it get some attention?
James Olsen commented
Not real hopeful this will be worked on since it's been "under review" for several years
Julian McIlroy commented