Forum Discussion
Data bars
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.
That's a good solution SergeiBaklan and with the right format you can show the Data Bar values
and it is flexible if you use a different percent range
h_christo1 you could add an other 4 Conditional Format rules to overlay the text:
like this:
For the new Conditional format rule, select the "Use a formula to determine which cells to format"
Set the formula as:
=B2="0-25%"
=B2="25-50%"
=B2="50-75%"
=B2=75-100%"
Set the Format Cells using the "Number" tab and Category = Custom
The Type for each rule is entered as
"0-25%"
"25-50%"
"50-75%"
"75-100%"
To give these 4 new rules
If you do not see the text, then check the Data Bar rule has not got the "Show Bar Only" selected
When you do not want to have a both the column with the "0-25%"... and the progress bar, you could consider putting a single values instead of the percent ranges.
Here for example
B14:17 are blank
C14 = 25
C15 = 50
C16 = 75
C17 =100
then the conditional formatting rules are:
with the Data Bar simplified to
When you use this solution you can also add "Data Validation" as a list to only allow the values 25, 50, 75 & 100 in these cells and prevent anyone form entering an invalid value.
Couple of comments
1) e.g. for 75-100% we fill not last quarter of the cell but entire cell
2) gradation of 25% (or any one else) could be not suitable on practice. I guess we shall visually see difference between 20-55% and 24-58% or so.
