Autofill area based on percentage

Hi all,

I’m looking for the way to color an area based on single cell percentage value:
let’s say we have a 10*10 cell square area (100 cells total) in which each cell represent the 1% of the total.

Now, I would like to color this area according a single cell value which would stand as percentage: example 0.36 => 36% of the total area filled.

Any method to achieve this?

Many thanks for your kind help.

I think it is possible. Please look at the attached file. I used a simple formula, which you can recognize when you click into the cells.


You sill see the calculated values in all cells. If you extend the conditional formatting also to the font, you will see only colored cells.

The zero value is a bit tricky because the real value is approx. -1.4E-16 thus no an exact 0. Thus I used the -0.01 value in the conditional formatting to change the color.

If you still have a question, please ask.

EDIT 1 after comments:

I opened the conditional formatting window
Condition 1 makes the back ground blue and white

image description

I added condition 2, which makes the color of the font in the cells with blue background blue

image description

I added condition 3, which makes the font of the white cells white

image description

Note: In my case the font in condition 2 did not turn blue. However, I could not find the cause and the solution for it.
Please test and see if the result is a blue font or a black font.

You can access “conditional formatting” in the Format > Conditional Formatting > Condition. The conditional formatting is the key element.

It might be good to read about “conditional formatting” in the free manual which you can download from:

All clear???

It works! But I don’t know what you’ve done. E.g. “If you extend the conditional formatting…” : Where do you find this? I can see that in cell format there’s a 0.00% value that must be related, but can’t see where you actually set the cyan background, etc. Could you add this to your answer? Thanks!

@ROSt53 & @karolus - thanks!

This was thought to be a comment - but wanting to attach a file I had to change it to an answer.

I had a look into the solution by ROSt52 and also tried two different but similar solutions ask35789ColourFractionArea001.ods. There were - not the first time - upsetting experiences with the present implementation of conditional formatting. Might someone test the attached file and tell me if the irregularities are also to see elsewhere?

There’s a way to hide numbers and show color only?

Tried many times with styles but no way at the moment.


Think you mean the solution (not correctly working because of CF flaws) with the border around? There are no hidden numbers. The calculations are exclusively done evaluating the formula inside the CF-condition. The cells themselves are empty (blank).
A way to hide numbers that ARE present and also text, of course, is using a style with background and font in the same colour. The same effect can be obtained by direct formatting.

Nice! Thanks for the tip :wink:

@Lupp - Very interesting approaches!. Need a bit more time to study and understand some of the more complexer functions you used. Great solutions.!

@ROSt53 - Thank you, You are very polite. I am just a tinkerer. The approach -as far as the formulae are concerned- is the same for both solutions. Just the evaluation is done offstage (by the condition-formula) in the first case and onstage (by the cell-formula) in the other case. The idea comes from old days (about 1965) when I sometimes had to care for memory mapping myself programming for the computers of that time in ALGOL 60.

@Lupp - Tinkering around on something is in my experience a strong driver of research and development. I do the same and when I get stuck, I do have a look into the LibO guides. ---- ALGOL in the mid 60s… I did it in the early 70s…

@ ROSt52 “---- ALGOL in the mid 60s… I did it in the early 70s…” Nowadays next to nobody still seems knowing anything about ALGOL (60, later 68) and its being progenitor to PASCAL - and by that of Delphi. Oh what a beutifully structured language! And how fast its compiling and linking! I didn’t stick to programming much in my later life - but up to this day I do not understand the reasons for preferring C/C++ over Object Pascal (which is available for all relevant platforms in the form of FreePascal). What is it what I missed?

@Lupp - I also touched PASCAL a bit but in my last research years I used again the language I learned first: FORTRAN. Unfortunately I don’t know the modern languages c/c++ except for their names. I actually wonder if FORTAN is in research still alive???

@ROSt53 - Alive? Of course! See , I didn’t really use FORTRAN for programming but I had to study some custom libraries and to port small parts of them to (flavours of) PASCAL.
By the way: LibO Calc V didn’t improve conditional formatting so far. (Concepts too ambitious?)

@Lupp - Wikipedia link - thanks! Very interesting to see that FORTRAN is still VERY MUCH alive. Seeing the punchcard picture created very sentimental feelings. I started with such punchcards for my first FORTRAN 66 program… - Conditional formatting - I was not aware of a is bigger change to come. So far conditional formatting never brought me to things which were not possible. Need to install 2.4.5 these days. To much of my surprise I have already 2.4.5 in the antergos linux distro.

Great! that’s it!

Many thanks :slight_smile: