Creating a Candlestick Stock chart

Excel 2016 2010 2007 2003
Candlestick chart is a style of bar-chart used primarily to describe price movements of a security, derivative, or currency over time. It is a combination of a line-chart and a bar-chart, in that each bar represents the range of price movement over a given time interval. It is most often used in technical analysis of equity and currency price patterns.
Candlestick chart in Excel 365

To create a Candlestick chart in Excel for Microsoft 365, 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

   2.   Select the data range (in this example, A2:E22, the data from Yahoo Finance):

Candlestick chart data Excel 365

   3.   On the Insert tab, in the Charts group, choose the Insert Waterfall, Funnel, Stock, Surface, or Radar Chart button:

Insert Waterfall, Funnel, Stock, Surface, or Radar Chart in Excel 365

In the Insert Waterfall, Funnel, Stock, Surface, or Radar Chart drop-down list, in the Stock group, choose Open-High-Low-Close: Open-High-Low-Close stock chart in Excel 365.

   4.   Do one of the following:

  • On the Chart Design tab, in the Data group, choose Select Data:
    Select Data in Excel 365
  • Right-click in the chart area and choose Select Data... in the popup menu:
    Select Data in popup menu Excel 365

In the Select Data Source dialog box, make sure that all your data series are added correctly in the Open-High-Low-Close order:

Select Data Source in Excel 365

   5.   Right-click one of the years along the category axis, and select Format Axis... in the popup menu (or double-click the axis):

Format Axis popup in Excel 365

   6.   On the Format Axis pane, in the Axis Options tab:

  • In the Axis Type group, select the option Text axis:
    Text Format in Excel 365
  • In the Number group, choose Custom in the Category field and then change the field Format Code (for example, to show only days) and click the Add button:
    Add Format Code in Excel 365

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

See also this tip in French: Comment créer un graphique chandelier japonais ou un graphique boursier.

If you have any questions or suggestions, please feel free to ask OfficeToolTips team.