Waterfall Chart (Stacked Column) auto Format question.

[wisemank]

Not sure if anyone can resolve this issue, but any suggestions are welcome!

I can send an example upon request, would be nice to be able to upload for reference, but please read entire post.

I have a stacked column chart (two series)to show a start point ($5000) and 9 option affects($+/-),therefore I have cost ups and cost downs, then a total. The "Waterfall" principle is that the plotted data is all positive and we lie to the padding to show up or downward affects and then change the color and label sign accordingly.

I would like for the color scheme to be red for cost ups and green for cost downs. I would also like for the label to have () around the cost up and no () for cost downs.

Since all the values are positive and we reduce the padding to visually reflect the cost effect, can I conditionally format a chart color based on a cell value or formula?

 

[pplstuff]

Excel Waterfall Chart - Tutorial and Template - Learn how to make waterfall charts using MS Excel | Chandoo.org - Learn Microsoft Excel Online

Use this as your starting point. Just note, his is in one series (so that means 1 color). You'll need to adapt to his method where yours falls into different series. You could use VBA to conditionally color these series if you think sometimes it'll be positive and other times negative.

Including the "()" can be done with a custom number format.

 

[wisemank]

Thanks for the quick reply, I actually have adapted from his examples and I am at the point that I may have to solve this programmatically. Has anyone programmatically formatted charts for number format and color fill?

Thanks again...

 

[pplstuff]

You'd probably want to put the code in a worksheet_change event. So that as your formulas change (presumably from +/-) the appropriate series will change from green to red.

Automatically Set Chart Series Colors to Match Source Cell Colors » Bacon Bits:

You could probably adapt this method to work. I'm sure there are plenty of other ways too.