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)
in the meantime, use the INDIRECT formula to easily achieve this.
tap tap tap ...
It has been 3 years since this question was posted, are we close to getting this implemented?
I maintain a large, dynamic spreadsheet with 8 fairly complex formatting rules. Every time I copy and paste a row I have to redo the conditional formatting, which is very annoying and a waste of time. If I don't fix the formatting each time I end up with hundreds of conditional formatting rules and my spreadsheet starts to freeze up.
This would be awesome
1. Use of named ranges in Conditional Formatting (CF) is DEFINITELY NEEDED. Another example of a major problem I have is the mess that is made by sorting a range of rows/columns,
I have a large area that is constantly being resorted. I set up the CF (15 different formats based on a flag in column A) over this named range (say $A10:$J$500 is named Sort_Area). If I do a Home -> CF -> Manage Rules, it lists 15 formats for the whole range. But after sorting, the 15 formats are repeated for each moved row - and Manage Rules for the same area now lists the 15 formats separately for each row - after a few sorts, the list easily grows to many 100's of repetitions of those same 15 formats. I then have to rest the 15 CF ranges to the oridinal $A10:$J$500 range, and then spend hours deleteing all the repetitions of ranges. Using a Named Range would eliminate this problem.
2. Another problem is that these repeated formulas seems to cause a trmendous load on Excel processing. Use of CF is the #1 cause (in my experience) of casuing Excel to crash. And I learned quickly: after restarting excel - DO NOT to use the 'saved version' - for some reason Excel doesn't restore/changes the formats correctly. You have to go bak to your last save version and repeat any changes you made. This proplem has driven me to doing a SAVE after every change I make in Excel.
3. Tip: I have simplified this problem a bit: I now keep a copy of the CFs in a row outside of the Sort_Area, and every now and then I go and delete ALL CFs in the Sort_Area, then copy the saved area CFs, highlight the Sort_Area, then Paste Formula. Still cumbersome, but better that the hours I spent deleting the duplicated CFs one row at a time!
Tony Smith commented
This would be fantastic! It would also be nice to be able to... somehow set conditional format ranges to not change if just a few cells of the range change. For example, a user drags a cell out of a conditional format range, and now you have 3 ranges because it splits the big range into 2 and the lone cell retains the format. Would prefer for it to stay as one range, not put formatting on the new cell
Absolutely with the Named Ranges.
Two things to remove:
Example setup: Range is given as A1:E10 (which it ALWAYS changes to $A$1:$E$10 and THAT needs to go away too). Test cell is the D column. D1 controls the formatting for all five row 1 cells, and so on down the table. I copy a cell from the D column, one from INSIDE the table, not AX348, and paste it on D1 and now the range covered is modified to exclude cell D1. It is considered in the test, but not for the formatting. That is just... poorly conceived. And monstrously ruinous. In any case, a cell should be able to take any pasting and while its "real" formatting would take on the copied cell's "real" formatting, but all that has nothing to do with the conditional formatting engine's work which is applied afterwards, like a layer. It never "belongs" to the cell and so should be utterly unaffected and should make no changes in its behavior. Certainly it should not delete the cell from the range affected!
Second thing and some related guff associated is implicit intersection with a relative position touch in the operation of the formatting. If I say, with the selected cell being D1, cell D1 is to be tested (say... =D1<>""), no $ characters, and put material in the other four cells, they all take the formatting. If I then edit the rule to be the range I've been using, it instantly changes to be NOT "if cell D1 is such and such" but rather that the third cell from the starting cell is to be tested for the starting cell (D1 controls A1, E1 controls B1, F1 controls C1, etc. Change the condition to be "D$1" A1 and B1 take the formatting but not the rest (in row 1), and B1 is back to looking at E1, and so on. There's no question of what the active cell is after the rule is set up, pick one outside the range and it's the same, so it is looking at the EXPLICIT thing you typed and taking it relatively (this is more complicated and literal implicit intersection also takes place). You type it literally as something and it is still "interpreted." there doesn't seem to be much a priori reason to be this way. After people all get used of course, maybe... but it sure makes it obnoxious...
Steven Soeder commented
It would save a lot of time and effort if we could use named ranges and/or structured references in conditional formatting. The direct references are difficult to read, and copy/pasting makes a complete mess of the rules. This seems trivial, but in a complex workbook with lots of conditional formatting rules this becomes a nightmare to manage and troubleshoot.
Richard Frenette commented
Conditional formatting is super useful and powerful. I use it for many different things, among which simplified Gantt charts to communicate with non-technical people. Having the possibility to use range and table names would greatly simplify its use and make it more robust (cut the automatic generation of spurious "rules" from copy-paste).
Simon Millett commented
Please let us use table names in conditional formatting. Tables are so useful.
This is infuriating. Even if use =INDIRECT("tblName") as the conditional formatting target it still gets converted to an absolute cell reference!
As an ISO 9001 company we can't just make and use workbooks at random for things like monthly reports, we have to create and maintain empty template files for them. Conditional formatting, tables, and named ranges are insanely helpful for complex reports, but this BUG in Excel makes filling out such templates a nightmare!
I would like Excel to maintain named range references and structured table references in the "applies to" field for Conditional Formatting rule.
I make workbooks with complex analysis and pivots on data I need to refresh regularly. The data is in a named range "Data". All pivots have "Data" as source data. Updating the range for "Data" and refreshing pivots updates all analysis I do. Great, 2 minute job and I am done. Apart from the conditional formatting that I need to manually adapt each time
Mathew Fowler commented
Just finished making a deadly gantt chart just to find out that I cannot use dynamic range in conditional formatting :( I pushed for vba coding saying it was the way to go... looks like its back to square 1 :,(
Using named ranges in conditional formatting would automatically solve the condition-breaking problem introduced with latest version of Excel (2010 and newer). So we'd love to see it implemented!
I would love to be able to use this feature!
Yes, we need this feature in Excel.
Bányay Gergely commented
True, not to mention that the function picker is not available, you can only type formulas if you know the syntax by heart.
The fact that named ranges aren't usable any and all places that an absolute reference is, points to deeper problems. While conditional formatting is very useful, and I would love to have this, the entire conditional formatting dialog needs some serious rework. The fact that I can't resize it, and can't expand the rules text box, combined with Windows' AWEFUL text highlighting speed, and I can't even use arrow keys because they for some stupid reason are used for autocomplete, makes using this dialog one of the most frustrating parts of Excel once the rule is longer than some arbitrarily small text box length.
Luc Vander Putten commented
I don't undertand this even requires voting. It is sooo obvious that as a product manager I would put it in next release right away even if only one vote!