Creating a flexible '100% stacked' bar chart

[Chris220]

Hi All,

Hope you are having a good day.

I'm looking to create a spreadsheet which visually captures the following in a '100% stacked' bar chart:

- if Michael's is less than 1 at any point the color reflected in the bar chart should be red. For example if the number is less than 1 between week 1 to 2 in June then that period should be reflected in red, otherwise show green.
- The stacked bar chart should be flexible enough to accommodate multiple reds areas in a single bar
- The chart should be able to automatically capture and update the colors based on a simple data set which contains 1 or (0.xx)

Apologies i couldn't upload an excel attachment as I don't know how to go about doing that.

[Pepe Le Mokko]

Attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[jomaor1]

Hi,

So you can do it only using Conditional Formatting (Like I did in the Workbook attached)

If you really need to be in a Official Excel Chart, it's going to be a little more complicated,
and it will take me a little more time, 'cause you'll need to use helper columns, so you can have multiple colored bars...

Tell me if is this what you wanted.

[Chris220]

Hi,

So you can do it only using Conditional Formatting (Like I did in the Workbook attached)

If you really need to be in a Official Excel Chart, it's going to be a little more complicated,
and it will take me a little more time, 'cause you'll need to use helper columns, so you can have multiple colored bars...

Tell me if is this what you wanted.

That's perfect Jomaor1. Could you please let me know how you did the conditional formatting. Did you have to apply the rule one cell at a time. I'm looking to apply this at a bigger scale without having to create one rule at a time.

[jomaor1]

Okay,

So first, I selected a range (e.g.: "U13:AI18")
And created a conditional Formatting based on a formula: "=C3=1" (Green)
And Another one: "=C3<1" (Red)

Then, I inserted columns between the months, and rows between the names, just to keep it more clean.

Did you get everything?

[Pepe Le Mokko]

@Chris
Please don't quote entire posts unnecessarily. They are just clutter and make threads hard to read.
Use the "Quick reply" instead
Thanks