Conditional formatting and data bar sizes issue

[NeverHappyMike]

Hi

My question is:

Any ideas what I would need to change to make the conditional format data bar size represent the cell number compared to 100%? Rather than compared to the highest number in the range of numbers?

I was trying to make a table full of numbers look more...useful. So I thought I would add some conditional formatting to the cells in the form of data bars.

Although this looked nice I noticed that it wasn't doing exactly what I wanted it to do. The bars were not appropriate to the figures, in the way I thought they would be anyway.

My table has figures such as:

50.4% - 31.0% - 23.5% - 45.3%​

The cell/bar would be full when showing the 50.4% and half full when showing the 23.5%. I expected it to be half full for the 50.4%, as that is half of 100%

I realise that it must be taking the full bar length from the highest number in the range and then making all the other bars proportionate.

Many thanks for any guidance you can give.

Best
Michael.

 

[NARAYANK991]

Hi Michael ,

I doubt that anything can be done , since this is an in-built feature.

One possibility is that you have a hidden row within your data range where you insert a dummy value of 100% !

Narayan

 

[NeverHappyMike]

Thank you Narayan, I'll have a look into the additional column (hidden obviously).

Best
Mike.

[Update]
It worked a charm. I simply added 100% into one cell next to my top row of numbers. Selected the whole range and then chose data bars. The formatting/length of the bars was correct 50.4% was half the cell. It certainly helps in seeing the figures within this large table of data.