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

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 acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

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