Manually Creating a Candlestick Chart with Volume in Excel 2007:-

If you would like your stock prices on the left side of the chart, you have to abandon the built-in stock charts. In that case, you need to have a good understanding of how Excel draws in high-low lines and up/down bars.

Figure (given below) shows four line series on a single chart. The first series is the thick solid line from lower left to upper right. The second series is the dotted line at the top of the chart. The third series starts out as the lowest dashed line but crosses to become the second-lowest line late in the chart.



(These four series are used to illustrate the different behavior of high-low lines and up/down bars.)

You add high-low lines to a chart by selecting Layout, Lines, High-Low Lines. Figure (given below) shows that the vertical lines extend from the lowest value at each data point to the highest value at each data point. In February, the line extends from the 1 in series 3 up to the 12 in series 2. In October, the high-low line extends from the 1 in Series 1 to the 12 in Series 2.



(High-low lines look at all the line series in the chart and extend from the lowest to the highest at each data point.)

In contrast to high-low lines, up/down bars always extend from the first series line to the last series line. In the Figure (given below), the up/down bars always start at the solid line for Series 1 and extend to the dash-dot line for Series 4. You would think that there would be a setting somewhere that you could use to specify that the up/down bars should extend from one series to another series. Instead, Excel always draws them from the first series to the last series.



(Up/down bars always start at the first series and extend to the last series.)


Both the high-low lines and up/down bars are valid only for series that are plotted as line charts. If you need to add a series for volume, you need to make sure to plot that series as a column chart so that it does not interfere with your high-low or up/down elements. If you need to add a series to show the price of a competing stock, you can add the series as a scatter chart with a smooth line to prevent that series from interfering with the high-low or up/down elements.