How to connect the color of a legend in a horizontal chart for two groups?

Question

I am trying to display the results of the performance of a set of rooms based on their responses to a test.

My goal is that the results above or equal to 50% to be marked as achieved results and those below to display the word not achieved. This has to be indicated with two colors matching what it is in the legend and the horizontal bars.

The table which I am using is like this:

Results to test 1

Room name Result Status Room A 50% Achieved results Room B 25% Not achieved Room C 45% Not achieved Room D 60% Achieved results

Using conditional formatting I was able to change the colors of the cells so those who say Achieved results are of orange color and those which say Not achieved is cherry color.

For these I used cherry RGB(222,49,99) and orange RGB(255,159,0).

But when I create a chart using only Columns Room name and Result all is painted blue and says Series 1. I am able to change the name of the Series like below.

But whenever I try to change using the paint bucket tool to match the color theme I intend to use to distinguish both groups, I mean those who passed the test and those which require improvement the legend duplicates the information for each color as seen below.

For my presentation I need two versions, one where next to the bar chart the legend specifies the meaning of those two colors and other where the legend display only the meaning of one color I mean for the achieved group.

Is there a way to do this properly?. I am trying to find a solution without macros. Can someone help me?.

Answer 1

Create a data area with two columns (eg columns E and F), one for Achieved and one for Not Achieved. Use formulas to pull in the correct values for the matching columns, and zero for the other in each case. Something like this in cell E5:

=IF($C5>=0.5,$C5,0)

and in cell F5:

=IF($C5>=0.5,0,$C5)

This will result in something like this:

50% 0%

0% 25%

45% 0%

0% 60%

Now you make a STACKED bar chart of the two columns, and colour the two series as you want to (but colour the whole of each series, not one bar at a time). The legend should show the two colours, and the bars will be drawn as if the 0% ones don't exist. If you have any issues with them showing up at all (I think this can happen if you apply borders to the bars), then instead of 0 in the formula, use the function NA() which always returns a #N/A error which charts will never plot at all.