Create a histogram in a cell based on condition

user_12759374 0
Viewed 131

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.

conditional-formatting histogram charts excel
1 Answers

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!

user_165632860
Related