Many thanks John for rapid response.
Followed your guide well.
By adjusting Series1 x-values to all 0 and setting X-axis range 0 to 44, the
data labels (stock names) line up at zero.
Then setting Series1 Markers as "none" hides these, and left aligning
Series1 data Label Positions flips them out of the chart area - useful if
stocks have v.low "Low" values (ie near zero).
Good one. I would not have got this out without your guide.
Tim

"John Mansfield" wrote:

> Tim,
>
> Assuming your data is first set up like this and cover the range A1:D8:
>
> High Close Low
> Conoco 41 39.5 38
> Exxon 42 40.5 39
> Shell 41 39.5 38
> Baker 40 38.5 37
> Halliburton 39 37.5 36
> Marathon 40 38.5 37
> Diamond 40 38.5 37
>
> Rearrange it to look like this - covering the range A10:E17:
>
> Dummy X Dummy Y Close X Error + X Error -
> 36 1 39.5 1.5 1.5
> 36 2 40.5 1.5 1.5
> 36 3 39.5 1.5 1.5
> 36 4 38.5 1.5 1.5
> 36 5 37.5 1.5 1.5
> 36 6 38.5 1.5 1.5
> 36 7 38.5 1.5 1.5
>
> The dummy X value of 36 is an arbitrary value based on the Low. In this
> case I just took the minimum value of the Low range. This dummy X range will
> eventually be used to add data labels to the Y axis. The + X Error is the
> difference between the High and the Close. The - X Error is the difference
> between the Close and the Low.
>
> Select the range B11:B17. Activate the chart wizard and choose the XY
> Scatter Chart option. Run through the prompts until Excel creates a basic
> embedded XY Scatter chart. Double click on the X axis values. In the Format
> Axis dialog box, set the minimum value to 36, the maximum to 44, and the
> major and minor units to 1. Hit the OK button.
>
> Now add a second series to the chart. To do so, click once on the chart.
> In the Standard toolbar go to Chart -> Source Data -> Series -> Add. The X
> values are the Close values (range C11:C17). The Y values are the dummy y
> range (range B11:B17). Hit the OK button.
>
> Now add positive and negative X error bars to the new second series. To do
> so, double click on the points associated with the second series. In the
> Format Data Series dialog box, go to the X Error Bars tab. In the error
> amount options, choose Custom. For the + option, choose the range D11:D17.
> For the – option, choose the range E11:E17. Hit the OK button.
>
> To more accurately view the results, activate the chart again by clicking on
> it once. Go to Chart -> Chart Options -> Gridlines and uncheck all of the
> gridlines options.
>
> Finally, you will need to use an XY data labeling tool to add the stock
> names to the first series. You can use John Walkenbach’s Chart Tools add-in
> or Rob Bovey’s XY Data Labeling add-in to perform this task. If you are
> using John Walkenbach’s Chart Tools add-in, click once on the chart. In the
> Standard Toolbar go to Chart -> JWalk Chart Tools -> Data Labels Tab. In the
> Data Label Range box, choose the range A2:A8 and apply it to Series 1. Hit
> the OK key.
>
> At this point all you need to do is format the chart to your specifications.
> Adjustments will be needed to the X axis maximum and minimum values so that
> the actual data appears centered in the chart.
>
> ----
> Regards,
> John Mansfield
> http://www.pdbook.com
>
>
>
> "Tim" wrote:
>
> > Is it possible to produce a Hi-Lo-Close chart with reversed axes - ie with
> > the Hi-Lo bars horizontal rather than vertical? The normal HLC chart has
> > stocks listed along the x axis and price up the y axis. How would I switch
> > this around?
> > I want to display a series of stocks listed down the y axis and the hilo
> > price range given across the x-axis.
> > Thanks for any advice on this.
> >