The new Icon Set should have a switch for color reversal (like Excel). Small values are not always bad (red) and big values are not always good (green).
An alternative would be also an "Icon Set 2 invers colors".
Additionally there should be a way to change each icon but I have no plans to implement that in the near future.
I second this enhancement request. However, maybe it can be solved easier:
Instead of switching the order of icon sets, why not just switching the signs. It should be possible to select:
"<=" instead of ">="
Then, the problem is solved. I assume this is easier to implement?!?
This has been implemented for a long time. The options are just not exposed in the UI.
The remaining part that needs implementation for this feature is to introduce advanced configuration solutions for the icon set entry in the conditional format dialog.
The code for the icon set entry in the dialog is in sc/source/ui/condformatdlg
The code for the icon set model part, including the reverse flag, the configuration that allows any image for an icon can be found in sc/inc/colorscale.hxx and sc/source/core/data/colorscale.cxx
I'd like to work on this, however I don't completely understand this. Can anyone please provide code pointers, and a more clear description of the problem ?
@abstatic: That's great to hear that you want to work on this bug. When I use icon sets in conditional formatting, I frequently need to use workarounds, because Calc is lacking the feature to reverse the color or icons.
I illustrate the issue with an example using the icon set "3 smileys":
The issue is that both, the order of the smileys :-) :-| :-( AND the order of the sign >= are hard coded. In other words, at the moment, Calc always assigns a :-( to high values. This does not always make sense.
For example, if you imagine an exam with 100 points. You fail if you have < 50 points, you pass if you have >= 50 points and you get an excellent grade if you have => 75 points.
What Calc can do (this is wrong!):
Grades (100 points in exam is very good, 0 points is very bad)
What Calc should be able to do (this is correct!):
Grades (100 points in exam is very good, 0 points is very bad)
What we need is that the order can be inversed. IMHO, it should be possible for the user to switch the sign >= to <= if the scale is inverse. Currently, in the dialog, the sign >= is hard coded and cannot be changed by the user.
Please see the attached screnshot "Smiley dialog example" to illustrate that I cannot change the order. Instead of an >= I need an <= for my scale of exam points.
Created attachment 140125 [details]
Smiley dialog example.png
For some scales, it is important that the user can inverse the order of the smileys/icons/colours, because high values are not always bad or good. In this example with points in an exam, I cannot correctly assign smileys, because higher values mean a better grade. What we need is an inverse order switch.
Additional info: Markus Mohrhard had implemented the conditional formatting feature, so he probably is the person who can help you with the code pointers.
He already gave a few hints in comment 4
Hi @Markus Could you please elaborate what do you mean by "introduce advanced configuration solutions".
Hi @Gerry! I am trying to work on this, and I have took some inspiration from Latest MSexcel. In that conditionalformat dialog one can select from "<","<="etc.
I have made a list and removing that hard coded label. I would like to ask whether this is accepted or not.
About the title of this bug:Iconset should have a color reverse switch, In MS Excel
I see the above feature but I can't see the color reversal switch instead of it one
can find dropdown in which he can select different types of ICon. Should this be implemented or color reverse switch should be ok? Please let me know if I am not clear.
Hi @Ekansh Jha : Thank you very much for working on this. I think the original bug description was not very precise. I think @Horst actually meant "Icon set should have a reverse order switch". I think Horst had these icon sets in mind, where the icons only differ by color (e.g. the icon set that looks like a traffic light). In other icon sets (e.g. the yellow smilies), it is actually a reverse order switch, which is needed.
On your question how to set the UI, I can give you my opinion and ideas, but I guess that @Markus Mohrhard has a clearer idea how the solution could best look like. So, please ask him, too. I am just a user :-)
I also added @Heiko Tietze to this bug, because he deals a lot with the user experience and design of LibreOffice.
I could imagine following solution:
For ITEM SETS:
I could imagine to have a selection field where currently ">=" is. The selection field could have the two options ">=" and "<". This in fact reverses the order. The selection field could be there for the first icon and then automatically applies for the other icons (to not make the order inconsistent).
[For comparison: Microsoft Excel has a switch called "Reverse Icon Order". Hence, it does not reverse the signs, but just the order of icons. This would also work. Please see attached screenshot]
Created attachment 140316 [details]
Excel 2010 Screenshot: Reverse Icon Order button
(In reply to Gerry from comment #11)
> Hi @Ekansh Jha : Thank you very much for working on this. I think the
> original bug description was not very precise. I think @Horst actually meant
> "Icon set should have a reverse order switch". I think Horst had these icon
> sets in mind, where the icons only differ by color (e.g. the icon set that
> looks like a traffic light). In other icon sets (e.g. the yellow smilies),
> it is actually a reverse order switch, which is needed.
> On your question how to set the UI, I can give you my opinion and ideas, but
> I guess that @Markus Mohrhard has a clearer idea how the solution could best
> look like. So, please ask him, too. I am just a user :-)
> I also added @Heiko Tietze to this bug, because he deals a lot with the user
> experience and design of LibreOffice.
> I could imagine following solution:
> For ITEM SETS:
> I could imagine to have a selection field where currently ">=" is. The
> selection field could have the two options ">=" and "<". This in fact
> reverses the order. The selection field could be there for the first icon
> and then automatically applies for the other icons (to not make the order
> [For comparison: Microsoft Excel has a switch called "Reverse Icon Order".
> Hence, it does not reverse the signs, but just the order of icons. This
> would also work. Please see attached screenshot]
Hi @Gerry! actually I have MS16 I think they have removed that color switch icon
(afaik). Looking forward to hear suggestions from @heiko and @markus.
Created attachment 140670 [details]
Excel 2016 Screenshot: Reverse Icon Order button (.png file)
> Hi @Gerry! actually I have MS16 I think they have removed that color switch
> (afaik). Looking forward to hear suggestions from @heiko and @markus.
Finally, I got hand at MS Excel 2016. They have not removed the reverse switch. Please see the attachment.
BTW, you see this dialog after (1) having applied a conditional formatting in Excel, then afterwards (2) select edit conditions. Then you see this dialog.
(In reply to Ekansh Jha from comment #13)
> Looking forward to hear suggestions from @heiko and @markus.
I have seen that Markus was not copied to this bug to answer the open questions how the UI of the fix should look like. So, I felt free to add @Markus Mohrhard to this bug. Maybe, also @Heiko has not seen your question or was busy.
@Markus and @Heiko: The main question here is how the reverse switch for icon sets / color scales in conditional formatting should look like. There is the solution by MS Excel (see the two screenshots) or a solution like mentioned in comment 2 and comment 11.
I'm always in favor of simplicity and against awkward controls to manipulate the presented options. So the easiest solution is to add "3 inverse colored smileys" to the list. Should be an easyhack.
But the actual problem here are the hard-coded symbols, as Markus pointed out in comment 1. So what we need is a dropdown with a set of symbols including the smileys, and you select whatever sequence you want. In other words, [Cell value is] [greater than] 10 [Insert] [:-)] (with Insert additional to "Apply Style".
Even better the user can add own symbols from the emojis (ideally colored) or unicode dialog.
Thanks Heiko for commenting on this bug.
(In reply to Heiko Tietze from comment #16)
> I'm always in favor of simplicity and against awkward controls to manipulate
> the presented options. So the easiest solution is to add "3 inverse colored
> smileys" to the list. Should be an easyhack.
Well, we have 22 icon sets, which means that we would duplicate the icon sets to 44. Does this really make sense?
I also wouldn't say that the idea in comment 11 is an "awkward control". I suggest to have the >= as a button. If you click on it, it changes to < (and automatically changes the signs below to make it consistent):
| | | |
| >= | --> | < |
That's straight forward, isn't it?
Re-evaluating the EasyHack in 2023
This enhancement is still relevant, as the issue is still present in the UI.
One should go to "Format > Conditional > Icon Set..." to see the icon set used for conditional formatting.