Creating a Candlestick Stock chart with volume

To create a Candlestick chart in Excel 2007, do the following:

   1.   Depending on the type of stock chart you want to create, you must include a specific combination of data series in your worksheet - and put the data series in order:

• High-Low-Close:
  • High price
  • Low price
  • Closing price
• Open-High-Low-Close:
  • Opening price
  • High price
  • Low price
  • Closing price
• Volume-High-Low-Close:
  • Volume traded
  • High price
  • Low price
  • Closing price
• Volume-Open-High-Low-Close:
  • Volume traded
  • Opening price
  • High price
  • Low price
  • Closing price

   2.   Select the data range (in this example B5:F26, the data from Google Finance):

   3.   On the Insert tab, in the Charts group, choose the Other Charts button and then select the Stock group:

Choose Volume-Open-High-Low-Close.

   4.   Right-click in the chart area, in the popup menu select Select Data...:

   5.   In the Select Data Source dialog box, in the Horizontal (Category) Axis Labels group, click the Edit button:

   6.   In the Axis Labels dialog box, select the data range (in this example A5:A26):

   7.   Click OK twice.

   8.   Right-click one of the years along the category axis. In the popup menu select Format Axis...:

In the Format Axis dialog box:

• Choose the Axis Options tab, under the Axis Type label, click the field Text axis:
  
• Choose the Number tab, change the field Format Code (in example to show only days) and click the Add button:
  

You can then make any other adjustments to get the look you desire.

Also see Creating a Candlestick Stock chart.