How can I use Excel data bars to format cells with text values?

Question

I'm creating a spreadsheet for project planning and have a column that lists estimated effort as follows:

• Unknown
• Very High
• High
• Medium
• Low
• Very Low
• None

I like the visual cues provided by data bars in Excel, and would like to use them to show relative effort for the "Very High" to "None" values. However I can't figure out how to do this. I thought about adding a numeric value to each value - e.g. "5 - Very High" - but I'm now stuck trying to apply the data bar criteria to a substring of the cell contents.

Is there a way to do this, or an alternate approach that would achieve the same results?

Answer 1

I tried to do this yesterday - I don't believe you can achieve this with conditional formatting data bars, which can only apply to cells containing numerical values.

The way I got round it was to overlay a bar chart, removing all the backgrounds, borders, axes and gridlines, and setting the bar colour to be 60%+ transparent.

Answer 2

I was able to use Brynjar Hallmannsson's tip to recreate what Andi did.

Steps:
1. Type values for databars where the inputs will be i.e.(C2:C8).
2. Link databar cells to inputs (i.e. B2 Formula: =C2) - you can just use the data bar cells as inputs but your numbers wont be visible anywhere
3. Highlight databar cells and apply databar conditional formatting
4. Right click on databar cell B2 and go to Format Cells -> Number -> Custom -> Type:
5. Replace General with "Unknown" and click OK
6. Cell B2 will now say Unknown. Left Align the text.
7. Repeat steps 4 though 6 for each databar cell

You can use VBA if you want to control the text of the databars via another group of cells. Lets say D2:D8. Use the OnChange Event to format the Custom Type of the databar cell whenever the associated cell in column D is changed. Like so:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Me.Range("D2:D8")) Is Nothing Then Target.Offset(0, -2).NumberFormat = Chr(34) & Target.Value & Chr(34)
End Sub

Steps 2 through 6:

Answer 3

you can paste the text in double quotation marks into the type section in custom format for the cell, when you then enter a numeric value in the cell the text will appear. to create the bar use conditional formatting