Description: Issue was born here [https://ask.libreoffice.org/t/conditional-formatting-multiple-range/98131] originally. Long story short: conditional rules cannot have multiple ranges. Easiest way to overcome this problem is to simply copy rules, then change cell range manually, case-by-case; Unfortunately when you copy the conditional formatting*, LO does not create a new rule but "increases" the cell range** in existing rule. *by copying the cell with the conditional formatting, Edit > Paste Special, uncheck “Paste all” and uncheck all boxes in the Selection area except Formats; **maybe this is a desired behaviour since condition is the same... With a simple "Copy" button in the conditional formatting, we can fix this issue. --- a simple mockup is attached below. Steps to Reproduce: try to copy a conditional formatted rule Actual Results: you cannot Expected Results: you can Reproducible: Always User Profile Reset: Yes Additional Info: Version: 7.6.2.1 (X86_64) / LibreOffice Community Build ID: 60(Build:1) CPU threads: 4; OS: Linux 6.6; UI render: default; VCL: gtk3 Locale: it-IT (en_GB.UTF-8); UI: en-GB 7.6.2-3 Calc: CL threaded
Created attachment 190798 [details] mockup
I understand the issue. But isn't the "copy" button you envisage a duplication of the Clone Formatting button we already have? In the past, users have been campaigning _for_ joining rules into a single one if the rule was the same, because of the useless duplication in the conditional formatting manager. See for example bug 80768, bug 81086, bug 87274 and bug 95295. A consequence of that is that it extends the range to which the rule applies. It's great when the users wants exactly that (for example "highlight all values above 100") because they can then edit the rule once to affect all ranges; but it's quite bad if the ranges must work independently, like here (where top and bottom values of a range are highlighted, in each range independently). The same happens when cloning the formatting to another range. It's hard to know when the users wants one or the other, so maybe the cloning or copying of conditional formatting should prompt the user with two choices: ° extend the range to which this conditional formatting rule applies; or ° create a separate conditional formatting rule for this range UX/Design team, thoughts? I don't have a thorough understanding of the history of the feature. Mike, you've touched this area before. mattia, am I understanding the issue correctly?
@stragu yes, you hit the spot; when user need a *local* maximum/minimum, clone could not be taken into account and asking user with a prompt could be an option; anyhow, the bunch of issues related to conditional formatting you pointed out, let me think we need to re-think about *entire* conditional formatting workflow; my 2 cents
(In reply to Stéphane Guillou (stragu) from comment #2) Some conditional formatting flavors should not merge, specifically "All Cells". It was an implementation error when I implemented the merge. For other cases, a UI workflow is needed to be designed, to allow flexibility. I see the value in auto-merge, but indeed, a way to allow custom workflows is also needed.
Thank you both. Setting to "new" as we agree we need to improve it, but looking forward to input from UX/Design. (And there might very well be an earlier issue I'm not aware of that already covers this.)
Regarding the "forced" merge of areas when the condition(s) and the conditional format are the same, there have been several reports complaining about the results. For instance, duplicating one worksheet with several separated conditions would result in merged conditionals, and users would claim that this is not the result they want – I would have to search for such reports as I don't recall them specifically, but I indeed remember them. Perhaps the merge should not be automatically triggered. Instead, the procedure could be: 1. Manage Conditional Format. 2. Select specific (rows of) conditions. 3. Press a button to attempt to merge the selected conditions. 3.1. If (some of) the selected conditions are equivalent, then merge their respective ranges, unifying those conditions into one (but there might be several groups of those, that are equal within each group but different between groups). Among the initially-selected conditions, unique conditions would be left unmerged. 3.2. The Manage Conditional Format dialogue shows the "first condition" on each row; "second/third/next/follow-up conditions" are not initially shown, but they should be considered for evaluation before merging (or not merging). 3.3. Within the Manage Conditional Format dialogue, Conditions (i.e. rows in the list) that are not selected for the manual merge are left alone, untouched. IOW, make the merge optional, _manually_ triggered by the user on selected conditions, instead of forcing the merge on any-and-all conditions according to some automatic action.
I'm not convinced by the Copy button idea. It's not clear what you copy (condition type, value, style) and what it actually means (usually to store in the clipboard for pasting, here to duplicate). Excel has a button "Duplicate Rule". But I wonder if users could expect the range in CF to be adjusted to the selection when pasted. Workflow would be to create the rule/s for F5:F17, to copy, select the range F20:32, and inserting the format adjusts the range accordingly. Excel provides the paste option "Merge Conditional Formatting" but that overwrites the values too (don't understand why). But under Paste > Other Paste Options there is a "Formatting" option which exactly does what I have in mind. (However, only once. Everything breaks when applied again to 2022 although CF is inserted properly.)
(In reply to Heiko Tietze from comment #7) > I'm not convinced by the Copy button idea. FWIW, the original request in comment 0 is about such copy button. OTOH, comment 6 is about a different approach altogether. I hope Heiko, Mike K and other devs can (positively) evaluate comment 6 as an alternative possibility to solve this kind of issues in CF.
So we have two ideas: Ady's optional merge (comment 6) and my paste special adjusting the range (comment 7). Mattia.b89, you are very welcome to participate in the discussion. I have to admit that this merge procedure is unclear to me - so it would be to users.
(In reply to Heiko Tietze from comment #9) > I have to admit that this merge procedure is unclear to me - so it would be > to users. It is already not clear to users. Many of them are not aware that the merge is already happening automatically. So when the result of a cell/range copy+paste (or duplicating a worksheet containing CF) is not what they expect, they don't know what to do. Sometimes they report such unexpected result as bugs. This is why I am suggesting (comment 6) to transform the current automatic merging logic into a manual merge (see comment 6) on selected rules, for those users that would be interested in such merge and (might) understand the consequences. I would be interested in Mike K's opinion on comment 6.
As simple user, I see a problem with special-paste solution: *visually* conditional rules are invisible in the spreadsheet view: you cannot see where they are applied to, that's, when a cell is selected, you "just" see the value, the possible formula in the formula bar and the format (bold, font, etc.); a such option would create confusion because users easily forget where they have applied what; I prefer to keep conditional formatting stuff separated.
(In reply to mattia.b89 from comment #11) > *visually* conditional rules are invisible in the spreadsheet view... How about an option "[ ] Adjust Range", visible only when the source contains CF?
(In reply to Heiko Tietze from comment #12) > (In reply to mattia.b89 from comment #11) > > *visually* conditional rules are invisible in the spreadsheet view... > How about an option "[ ] Adjust Range", visible only when the source > contains CF? Sorry but what problem would that resolve? I don't see how a visual artifact in the main area would resolve the automatic merge problem. Let's be clear; the problem is that automatically/forcefully merging ranges every time the rules are equivalent/equal is not always matching users' needs or desired results in CF, and in fact such automatic merge makes some CF impossible. Considering _that_ problem, comment 0 includes one potential workaround. IMHO, comment 6 includes an alternative that would help in more cases and it would be easier on users.
(In reply to Heiko Tietze from comment #12) > (In reply to mattia.b89 from comment #11) > > *visually* conditional rules are invisible in the spreadsheet view... > How about an option "[ ] Adjust Range", visible only when the source > contains CF? Issue is before. IMHO, my proposal is the easiest but not finest solution: I think we should rework the entire CF workflow in order to get a good thing, otherwise we just place a bunch of "plasters".
(In reply to mattia.b89 from comment #14) > IMHO, my proposal is the easiest but not finest solution Let's assume that such suggested "copy" button is added to the Manage CF dialogue and that you use it once. Now you have a duplicate rule (i.e. conditions, styles and ranges are all the same as the original). Let's assume you modify the range on that copied rule; now the rule is not a duplicate of the original anymore (which is what we wanted). Let's assume then that you duplicate the worksheet > we have again a merged unique rule in the new duplicated worksheet. This is 1 example of the problem with forcing automatic merging of CF rules, and the (generic) reasoning behind my alternative proposal in comment 6 (replacing the forced automatic merging into manual merging on user-selected rules).
(In reply to ady from comment #15) > Let's assume then that you duplicate the worksheet > we have again a merged > unique rule in the new duplicated worksheet. This is 1 example of the > problem with forcing automatic merging of CF rules, and the (generic) > reasoning behind my alternative proposal in comment 6 (replacing the forced > automatic merging into manual merging on user-selected rules). I see this behaviour as a new different issue;
(In reply to mattia.b89 from comment #16) > (In reply to ady from comment #15) > > Let's assume then that you duplicate the worksheet > we have again a merged > > unique rule in the new duplicated worksheet. This is 1 example of the > > problem with forcing automatic merging of CF rules, and the (generic) > > reasoning behind my alternative proposal in comment 6 (replacing the forced > > automatic merging into manual merging on user-selected rules). > > I see this behaviour as a new different issue; The core issue is the same. If comment 6 is accepted as a valid alternative solution, then the "copy" button suggested in comment 0 is (much) less needed (or not at all), in which case a new different enhancement request would be needed (with the enhancement suggested in comment 6 here), making this report obsolete.
We discussed the topic in the design meeting but couldn't find a better solution. T sum up the ideas: a) add a Copy button to the CF dialog that duplicates the last entry (we could name it Duplicate; and it is worth to consider to always add the previous condition) b) copy/paste special to apply the CF and adjust the range (could also be an extra option) c) rework the merge range function to not always apply (could be necessary for a) too)