Apply icon depending on the text value in the cell

Question

Assuming as example the column E, the value of which Sim or the value Não, I am trying to create a conditional formatting that applies an arrow depending on the text present in the cell itself:

Sim   = Verde
Não   = Vermelho  
outro = Amarelo  

The point is that this doesn’t seem to work with text, only with numbers because by default it already assumes a comparison >=.

If you use numbers, I get the desired result:

Which results in:

But the problem is -1, 0 and 1 are not humanly readable (except for the programmer...).

Question

In the Libre Office Calc, how to apply an arrow automatically based on a text value present in the cell itself?

^{If impossible, an acceptable alternative is to apply a later format to conditional formatting where the value 1 is presented as Sim, and the value -1 as Não.}

Example of the spreadsheet: example.ods

Answer 1

The problem seems to be really the fact that this kind of conditional formatting (Icon Set) only work for numerical data types. But I did not find official confirmation for this suspicion anywhere on the Internet (just found indication that it is impossible to use a custom set of icons, because the image is compiled together with the Libreoffice source code).

Anyway, I figured out a way to do what you want with the alternative that you yourself suggested: actually use numeric values in the cell, but display them to the user in text form. To do this, just create new styles specific to the texts "Yes", "No" and "Other".

1. Open the Styles window by going to the "Format Menu" (Format) -> "Styles and Formatting" (Styles and Formatting) - in my Libreoffice, which is in English, the hot key is F11.

2. In this window, right-click on the base style (Default) and select new. You will need to create a style for each discrete value of your data (that is, one for "Yes", one for "No" and one for "Other" - as in your example). Name your style in the main tab.

3. In the "Numbers tab" (Numbers), select the user-defined format in the category (User-defined, in my case in English) and type the string you want to appear (in quotes! ) in the formatting code field (Format code). Click Ok and this style will be recorded.

4. Do the same for all styles. At the end you will get something like this:

5. Now, go to the data column you want to format (which, of course, should contain numeric values 1, 0 or -1 instead of text) and normally add the conditional icon formatting (Icon Set) you were already using. Then click "Add" (Add) to include a new simple formatting by choosing the value and style to be applied.

6. Repeat for all possible values/styles (in your example, at the end you will have 4 simultaneous conditions: that of the icons, and 1 for each possible text "Yes", "No" and "Other"). Once applied, your data will be displayed as you wish, although the value of the cells is still numerical (as you can notice by the number displayed above, where indicated in red).