A simple chart that I can't make! Please help :)

[Im_Offset]

Hello,

For simplicity's sake, the attached spreadsheet only contains the output I am trying to chart. It is updated automatically in my main spreadsheet, but I only need help wit making a chart look like I want it to.

The data lists strike prices of open option contracts in our portfolio. There are two kinds of strike prices: short strike prices and long strike prices. I also have listed the current market level. I would like to create a chart where the y-axis lists a range of strike prices (the range should be 30 points above and below the highest and lowest strike prices respectively. I would like a red bar to depict each of the short strikes, a blue bar to depict the long strikes, and a yellow bar to depict the current market level. There really is no need for an x-axis label.

Can someone help me create this chart? I should be able to do this myself, but I am having a brain cramp and cannot get it formatted correctly. Thanks!

Best Regards,
Im_Offset

 

[Villalobos]

Hi,

Is this chart Ok for you?

 

[Im_Offset]

Hi,

Is this chart Ok for you?

Thanks for looking at my post, but that isn't what I'm trying to create.

One thing that was confusing in my original data set was I had a space between some of the short strikes. So I have remade the list of short strikes in the spreadsheet attached to this message. I also created a visual of what the chart I want by formatting a blank area. I'd like the y-axis to list a range of market levels that is based on the existing range of short and long strikes (the range for the y-axis should be 30 points above the highest strike to 30 points below the lowest strike). I have that range shown, but you can barely read it, because I have made the cell height so small. The red bars represent the short strikes, the blue bars represent the long strikes, and the yellow bar represents the current market level. I want the visual I have created on the spreadsheet to be made into a chart that I can read and put on my dashboard.

How can I make a chart that looks like that?

Thanks!

Clearly and obviously.....Im_Offset

 

[Chihiro]

Something like below? Not easy to do in Excel and very manual process (though you can use VBA if you are familiar with Chart Objects).



See attached sample on how to do it. Unfortunately, you can't stack vertically the same value series. You will either have to live with only one showing, using data labels as indication... or you might be able to adjust Transparency and size of each series to get some result you'd like.

This is variation on the method outlined in the link.
http://peltiertech.com/plot-values-along-axis-of-excel-chart/

 

[Hui]

How about this?

It is 3 series in a XY Scatter chart, using Horizontal lines as the Markers

This is the same chart now with no markers and using colored Error Bars


Both are included in the attached file:

 

[Im_Offset]

How about this?
View attachment 29957
It is 3 series in a XY Scatter chart, using Horizontal lines as the Markers

This is the same chart now with no markers and using colored Error Bars
View attachment 29959

Both are included in the attached file:

This is Awesome! Thanks Hui.

I have looked at both charts, and I am not understanding how you got the second chart "with no markers and using colored Error Bars" What is that exactly? When I select the data series and look at the formatting options, I cannot see how the longer bars are made.

Thanks

 

[Hui]

If you select a chart then select a series
Then goto the Chart Tools, layout, Error Bars tab or click on the + next to the chart and select Error Bars, More Error Bar Options
set Vertical error Bars to 0
Set Horizontal error bars to any value that extends past the edge of the chart
then format as appropriate

Then select the series
Set the Marker type to None

So the error bars are displayed
But the Line and Markers aren't

To select an error bar click it, then use left/right arrows until it shows the Horizontal error bars are selected

 

[Im_Offset]

If you select a chart then select a series
Then goto the Chart Tools, layout, Error Bars tab or click on the + next to the chart and select Error Bars, More Error Bar Options
set Vertical error Bars to 0
Set Horizontal error bars to any value that extends past the edge of the chart
then format as appropriate

Then select the series
Set the Marker type to None

So the error bars are displayed
But the Line and Markers aren't

To select an error bar click it, then use left/right arrows until it shows the Horizontal error bars are selected

Thank you! I appreciate the help. I always knew that Excel can do just about anything. I just don't know how to do it....but thanks to Chandoo.org and people like you, I am learning how to do more!

Best regards