Ask Your Question

Conditional Formatting - Colour Scale.

asked 2018-04-30 05:43:10 +0200

taffthefish gravatar image

Libre Office Version:

I need to colour code cells based on the the key below. 1 = Green 2 = Gold 3 = Blue 4 = Red 5 = Brown 6 = Light green 7 = Lime 8 = Yellow 9 = Dark Purple

Any number will be entered into multiple cells and calculations done based on the cell content. The colours are for others to view for reporting purposes. Can the numbers also be changed to reflect the colour they represent so they are not visible once entered and the corresponding colour is used to populate the cell?

edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted

answered 2018-04-30 11:52:52 +0200

Lupp gravatar image

updated 2018-04-30 12:00:49 +0200

The numbers must first be entered into the cells or returned by the formulae contained in the cells in any case. To colour the cells regarding your chosen assignment is then the duty of the Conditional Format (CF) you have to define.

By regular CF you will need to define 9 dedicated cell styles and 9 conditions for overlaying them. The ColourScale option is not appropriate for the purpose.

To make the numeric (or even string) values of the cells referable, and controlling the CF, but invisible, you need to set the appropriate format code under Format Cells... > Numbers in the appropriate way. To not display any content set the format code to "";"";"";"" where the 4 parts will apply to positive values, negative values, zero value, and string in this order. You may use this code (or a similar one better meeting your needs) to the cells generally or to any of the cell styles to overlay condtionally.

I don't clearly understand what you mean by "...and the corresponding colour is used to populate the cell?"
You should never try to use a colour as the primary representation of any information. To do so is very bad design and is not supported by standard functions.
A conditionally overlaid colour (or the used cell style) you cannot evaluate (refer to) anyway because it is only generated/applied for the view and never actually assigned to the cell object.

(This applies to all versions of LibreOffice I know.)

edit flag offensive delete link more


I agree with you, a colour should not be the primary representation of the information. Sadly, this is something that has been requested and I have not been able to convince otherwise. The person inputting the numbers (1-9) wants the cell highlighted by the corresponding colour. Thanks for replying to my question.

taffthefish gravatar imagetaffthefish ( 2018-05-01 05:09:45 +0200 )edit

As soon as the numbers are entered there is actual information accessible in the way spreadsheets are made for. The additional representation by numbers based on CF does not endanger the functionality, and isn't even a serious aggravation concerning efficiency.
But: NEVER represent an information primarily by setting a colour. This can definitely not be handled without custom code, and only with a lot of complications if resorting to user functions.

Lupp gravatar imageLupp ( 2018-05-01 13:33:28 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2018-04-30 05:43:10 +0200

Seen: 910 times

Last updated: Apr 30 '18