Forum Discussion
Data bars
I wish to add a coloured data bar to represent the value in the cell. However the value in the cell is a percentage range (e.g 25-50%). Therefore I would like to file the cell with a colour between 1/4 - 1/2 of the way across. Any ideas/help on how to achieve this?
As far as I know, that is not possible.
You might create a stacked bar chart:
Sample workbook attached.
Easiest way is to use the REPT formula with the pipe "|" separator.
=REPT("|",K15*100)
Where you percent is in cell K15
If it is a number and not a percent then the *100 is not required.
Then format the cell with Font = Stencil or Playbill and use the Font Colour to change the "bar" colours
Should look like this.
You could use the built-in conditional formatting for data bars with the understanding the feature looks at the column or a matrix as a whole to determine if 'half-filled' is the display for 50%, for example.
For example:
In Val A, 50% is the highest percentage in the column so it's a full bar. In Val B, we have 100%, so the data bars adjust according, 25%, 50%, and 75% look as you might expect. The matrix is an example of applying the rule to multidimensional data range.
Let me clarify, in one cell you have text with percent range and in another cell you'd like the bar like
Correct?
SergeiBaklan thanks for your reply. Yes that is correct! Although if possible it would be neater in this case to have the text overlaying the partially filled cell.
I don't know how to do that with overlying the text, sorry. Closest solution which is in mind
is to add bar into the next cell using it it formula
=--MID($C2, SEARCH("-",$C2) + 1, LEN($C2) - SEARCH("-",$C2) -1 )In the sample that's in D2.
In conditional formatting for the data bar with formula for minimum
=-1*LEFT( $C$2, SEARCH("-",$C$2)-1 )and number 100 for maximum.
Unfortunately we may use only absolute references in above formula for minimum. There was a workaround with OFFSET() to apply the rule to the range. Last time I touched it few years ago, don't remember details right now. Idea is to use absolute reference for the first cell in the range applying OFFSET to it and other cells in formula.
