Excel Floating Bar Chart, and Lollipop graph

Question

Hello, I would like to create the graphs below:

-3 scenarios

-each with multiple bars within a scenario

-which "float" showing the low and high numbers

Please can someone suggest how this is done in Excel?

Thank you.

Answer 1

Let us start with a simple one. That looks similar what you want.

You want the data labels outside, Excel did not support that. Therefore we have to add 2 series that are below and above.

Change the colors, done.

For 3 scenarios this might an idea, but as one series goes across the 3 scenarios...

... it is better to shift the data, so we can change each series as we like.

Anything else is a matter of formatting.

Same sample file.

Andreas.

Answer 2

Many thanks for your kind answer - this doesn't help with the data points that straddle y=0 though. For example in one year, profits range from -$30 to $45 and I can't see how to get the stacked method to do that

Answer 3

Stacked Column Chart, 3 columns stacked up, lowest and highest for the data labels.

XY-Scatter

Sample file:

https://www.dropbox.com/scl/fi/2yrjdwzkrjlocn9pc4yqu/b2a9e474-5c35-408b-9aca-4ad9e974d926.xlsx?rlkey=mhfn8mmlhl795h3w8nofbyv1i&dl=1

Andreas.

Answer 4

I need the bars to be able to straddle y=0 like in scenario 3 below

Many thanks for your kind answer - this doesn't help with the data points that straddle y=0 though. For example in one year, profits range from -$30 to $45 and I can't see how to get the stacked method to do that

Answer 5

Thank you again - MS make something very easy, needlessly complicated!