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)
Lawrence Dunn commented
found this page while googling for a solution/work around to the proliferation & fragmentation of Conditional Formatting 'Applies to'...I tried using a named range & using that in the 'applies to' box but it instantly changes to the range that the name defines & does not keep/maintain the named range in the box.....hence I was googling for a work around.
It seems to me, that the main reason for folk suggesting Conditional Formating using (& keeping) a named range in the Applies to box is itself a work around to the way Conditional Formating works (with the way it fragments the applies too range).....maybe the better solution would be to fix that rather than address the work around?
Having said that, if you have multiple CF rules that all apply to the same range, it would be more elegant to use a named range & have all teh rules reference that named range, that way, you could just rnage the named range once & all rules would apply to the new range....or you could define the named range as a dynamic range which adds even more flexibility
I have run across this issue a lot - Conditional Formatting is a powerful way to build effective visuals within Excel. Limiting it to static ranges does not make sense with so many dynamic formulas and tools that would benefit from dynamic formatting.
I definitely would prefer to have this functionality!
J Welch commented
It's July 2018, can we please get this going? Three years and nearly 400 votes seems like enough to be worth MSFT's time, especially for the cost of office these days.
@futski - did you try something interesting? I won't call it radical, It seems to work - protecting the work-sheet - with "Insert Rows", "Insert Columns", ticked and "format Cells" deliberately "unticked" if by chance it is in "Ticked" condition.
Now even if I paste values, there doesn't seem to be break, and row and column insertions, permitted by me in the protection box, takes the contagious conditional format.
In that case, if you define the working zone, probably your colleagues, even if they don't paste value, won't be able to paste the format, unless you unlock it..
Celia Alves commented
Using Excel tables helps to work around this issue. However, if the user pastes in cells instead of using Paste as Values, the rules will easily get disrupted. I created a solution for this that is described in the video at https://youtu.be/pC98JKrfC3g
I would love to hear your comments on that solution and other strategies you might have. Thank you in advance for your advice.
@futski - you are right, in fact this whole trouble will be sorted, if ms takes care of original suggestion- and not parse the names into absolute value. Then it is left on us to edit the name description (range) or in some cases, excel itself extend the size of the named range. I don't think it should be that complicated- keep the name in the format and then link back the "current" description from the data-base. In fact I don't find much of a justification in this parsing.
Mine is the office auto-renewable subscription, whenever ms feels like, it updates it (as it does for the win10). That's all OK, but sometimes the system seemingly hangs (some critical update in office, say in explorer and it freezes for so long, that I get confused, and reboot). Thankfully system doesn't collapse, but it would be prudent from ms to flash some message once in a while on its behind curtain activities. Probably I should keep the auto-update off, but then I have to regularly check whether updates exist.
I agree with you 100%. I think the difference with my previuos note and what you are describing is that I was applying the conditional formatting to columns in Formatted Tables; ie "Applies To:=Table1[Column1]". I'd guess that you weren't using Formatted Tables.
To me, a Formatted Table is any table where "Format as Table" was applied.
For a formatted table, the normal way of adding a new row by simply starting to type in a new row at the end of the table. All call formats and formulas will automatically be applied to the new row. Conditional Formatting will also get applied to the newly added row. Looking at the Cond Format rules will show that the "Applies to" range will be extended to include the new row.
But as you said, "copying" a cell to a new row will still make a mess. But when it did, the new rule it added was unnecessary.
Table1 = is C2:D11
Headers for Table1 are in Row 1
Col C ($C$2:$C$11) formatted for cell = 1
Col D ($D$2:$D$11) formatted for cell = 2
Copying C5 to C12 creates a new new row in the table. So the Cond Format for Col C (and Col D) changed to to $C$2:$C$12 (and $D$2:$D$12) in order to accomodate the new table row. But it also added a separate rule just for $C$12. The rule for $C$12 is not needed because the table range was extended. This left 3 format rules; 1) $C$21 2)$C$2:$C$12 3) $D$2:$D$12 . #1 is not needed because it overlaps with #2
I hope this makes sense.
I think whats happening is that because you're doing a copy / paste (not paste special values), it insists on copying the Cond Format too even though the format is exactly the same as table column format.
Bottom line is that this still makes a mess. MS needs to allow us to put "Applies To:=Table1[Column1]" w/o out converting it to a fixed range. And if the Cond Format matches the table columns format, then drop the Cond Format.
By the way, I do agree with you about paste-special-values. Thats just about the only way I ever paste data. It keeps the formatting rules neat. Unfortunately, a lot of the sheets I create get shared with others that don't understand the intricacies of conditional formats and how copy / paste can mess them up.
And I too am on O365 V 1804 Monthly Channel
@futski- I didn't get your problem. Let me tell, what I did,
I tried out -
C2: C11 formatted for cell value = 1 and D2:D11 Formatted for cell value = 2
When I, for example Copy paste:
Single Cell: say C5 into C12 (even contagious row), the condition format when checked, remains for the range $C$2:$C$11, but if I check specifically C12, it has only that particular cell as range.
Multiple Cells: C3: C6 at C12 (onwards) :- C12 property shows the formatting range as $C$12:$C$15
Even two columns, C & D (with different conditions) - Copying C5: D6 at C12 behaves exactly as above, i.e. the original remains same but the C12 or any one in this range, say C12 shows $C$12:$C$15 and D12 shows $D$12:$D$15.
I would have liked it to move to $C$2: $C$15
I think you mean - copying one cell from within the range to another one "Within" range, e.g. C5 to C8 and muti columns C5:D6 to C9:D10
In this case rules are getting broken, and probably it is a shortcoming.
But it happens when the cell copied into is in the middle of the range, not the extremes (or crosses the end of the previous).
If I copy same C5:D8 into C10: D13 , the rules are still unbroken, and modifies to $2:$9 for both columns and in the new (10-13 range) $10:$13 for both columns.
The insertion in the middle breaks the rule, probably natural.
As i can understand, what is happening is, when I copy a number of cells - these group of cells are taking the properties, and self-defining a corresponding range. At the extremes, it doesn't matter, since the new range doesn't create an island. But when it is in the middle, some cells at the end remains attached to the old range, and those where I pasted creating the new range, gets divorced from it. Why that's not happening on single row copying, I don't know. It should happen there too, but, as you pointed, it isn't.
As far as I am concerned, I haven't faced this problem, since I would rather insert row, for new data, or copy-paste "Values" in paste-Special. Then obviously nothing gets affected.
Anyway, bug pointed out, probably Excel is listening and will do something about it. At least I am happy, that without informing us that they have done, they did do something :-)
I think when the Office does an auto update, they should provide a message what bugs they corrected, especially for people like me, who have put the auto-update on, and don't go to the site to check and update.
@Sbasu, you are correct. MS does seem to have fixed some of it. It appears to be adjusting the "Applies To: range when you add / delete / move whole rows and columns. Or even if you copy/paste within a single "Applies To: range.
But alas, the problem still exists when you copy/paste data that crosses more than one "Applies To" range.
Create a table with a conditional format in Column A and different conditional format in column B. If you copy a single cell from a row in column A and paste into a different row but also in column A, it will work as desired without breaking up your rules.
However, if you copy row data from columns A:B and paste that in a different row, then your rules will get broken, even though the data always stays in the same columns. Same thing would happen if column B did not have any conditional formatting.
You could MOVE the whole row (shift drag row) without breaking the rule. But sometimes you only want to copy a few cells from a row, not move the whole row.
So in summary, your rules will get broken/split-up when you copy/paste and the data you are copying spans more than one conditional rule or non-conditional formatted range.
Clear as mud, right?
PS, I haven't tried the =OFFSET trick from @JOUKE
@Jouke, I think you might've mistaken the issue (unless I have!). The issue is not with the conditional formula, but with the "applies to" range. I tried substituting =OFFSET(NamedRange,0,0) into the "applies to" field, and after applying, the same issue presents itself: NamedRange reverts to its absolute cell reference instead.
I don't know when Excel has added the patch, but it is now quite OK for me, in the version 1805 of office 365. Though still the named range is replaced by the absolute reference ($s), but now when I add a row or column within the range, the edges are automatically incremented in the absolute reference and I don't have any broken references.
It is now, as I wanted, a single unbroken range, no more cluttering. It wasn't too long ago, since my last addition did create clutters. To check, I added rows as well as columns, and it is alright now.
But while doing that I found something funny - I don't know it was there earlier or not - picking up property by contagion, even in conditional formats. I know it does for normal formats, but conditional too?
For example say I conditional format ($B$5: $E$10) to be Red when the value is 1. Now entering 1 in B5, C5,D5,E5 - all are red, as expected, but when I put 1 in F5, it too becomes Red, and now the conditional format is extended to "This Cell" - not whole row or column. Put 1 in G5, and that too gets included. Skip H5 and put 1 in I5, and the chain gets broken.
Micro seems to have done some correction, now this is another bug, which need to be corrected.
@Jouke, nice workaround! Thanks for the tip. Looking forward to trying it out since Microsoft is apparently not going to address this.
Jouke de Vries commented
As a conditional formula you could use e.g. =OFFSET(MondayMorning,0,0)="Green". NamedRange "MondayMorning" refers to only one cell. Apparently the OFFSET part encapsulates the NamedRange, so it is accepted. Still not a true solution, but it works for me
[Deleted User] commented
Yes please - I want this too
Is anyone paying attention to this thread? Please allow conditional formatting to use Named Ranges and not replace them absolute ranges.
It is ridiculous that this hasn't been resolved after YEARS of waiting.
PLEASE!! This is very annoying.