Search code examples
excelchartshistogramconditional-formatting

Create a histogram in a cell based on condition

I need a histogram in a cell based on condition.

Example: In:

enter image description here

My Out:

enter image description here

Needed Out:

enter image description here

So, If I'm using conditional formatting and choosing histogram, I have no option to choose the colour based on condition. My Output - this is what excel can suggest by basic tools. Needed Output - this is what I want to be suggested. Values < 0,85 - red histogram, < 0,95 - yellow, and < 1 - green.

Solution

  • I've created a similar graph on one of my dashboards. My output:

    enter image description here

    You have to work backwards a bit, the red/yellow/green color is just a background color, and is set by conditional formatting based on value, and doesn't change in size. To give it the appearance that it is growing or shrinking, you need a grey/white bar conditional format that "covers" up the color on a right to left basis.

    Conditional Rules: enter image description here

    Blank bar conditional formatting: enter image description here

    In each of the "bar" cells, you'll just need to point to the % you want to use for each bar and subtract one. So = 1 - percentage will give you the value that the blank bar conditional formatting will pick up.

    enter image description here

    I also used custom format ;;; to hide the text in the cells:

    enter image description here

    Let me know if this works for your purposes!