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?

  • Does it have to be data bars or can you use color scale? – Raystafarian Oct 21 '14 at 14:25
  • I know I can change color, but prefer not to have a wide range of colors on the spreadsheet - I think it would look distracting as I have multiple effort columns. – Roger Oct 21 '14 at 14:27

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.

enter image description here

  • I was afraid of this. Thanks for confirming. – Roger Oct 22 '14 at 0:44

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

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:

Steps 2 through 6


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

  • Welcome to Super User. Any chance you could show a more explicit example with some screenshots to better explain the how-to? Thanks. – fixer1234 Dec 18 '18 at 5:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.