Can Data Bars be displayed over Text?

NJR

New member
Joined
Jun 26, 2014
Messages
11
Reaction score
0
Points
0
Excel Version(s)
2010
I am using Excel 2010 on Windows 7. Looking to see if anyone knows a way to display Data Bars in cells containing Text.

I have Project Names in Column A - and six Tasks to be completed (Columns B-G).

(1) I'd like to have the cells in Column A show a Data Bar based on the number of Tasks complete. It's easy to show a Data Bar by counting the completed cells and putting a Numeric Value in Column A - but I'd like to retain the text for the Project Name and have the Data Bar show in the same cell.

(2) If one of the Projects has a Task which is "At Risk" I'd like to have the Data Bar for that Project to show in Red. Assuming all other Projects are completing their Tasks - the Data Bars for those would be Green. Not sure if this is possible to do...?

The attached screenshot shows what I'm working with.
 

Attachments

  • Capture.JPG
    Capture.JPG
    52.8 KB · Views: 328
Data bars are supposed to convey an impression of size, that does not apply to text or what you want. Why not just use conditional formatting?
 
If you use text along with a number, either by concatenate or some other formula, Excel will consider it text, in which case you will no longer be able to show the data bar. You may think about putting the project name with an inserted column beside it showing the data with the data bar(similar to a Sparkline only with a bar). As for the second part (your (2) bonus part), you can use conditional formatting with a data bar to show the color.
 
Bob/Mike - Thanks for having a look at the post and providing your comments. The conditional formatting didn't work for the solution I was attempting. I'll put some thought into another way to convey a Project "status"... perhaps the option of the inserted Column will do the trick.

Thanks again for your help - much appreciated.
 
Why didn't it work? I was thinking of checking the value for 'At Risk' say and setting the format to red. We must be missing something if that doesn't work.

I just knocked this up, isn't this what you want?
 

Attachments

  • Project Status.png
    Project Status.png
    7.7 KB · Views: 411
Yeah, what you have works like a charm. I’m able to replicate that. The bit that I was hoping to do was have the highlight take up a portion of the cell corresponding to the percentage of Tasks complete.

For example:
If Project 1 has all Tasks complete – then the highlight would fill the cell in the Status column.
If Project 2 has half the tasks complete – then the highlight in the Status column would only fill half the cell.
If Project 3 is at the quarter mark for Tasks and has one that is At Risk – then the highlight would fill 25% of the Status cell and be Red instead of Green.

The only way I could think of having only a certain percentage of the cell highlighted was to use the Data Bars – that’s why I went down that road at first.

I think the solution you’re proposing might be the closest I can get. I can get the correct colors to show - just not having a certain percentage of the cell highlighted.
 
Back
Top