Stock chart in Excel or candlestick chart in Excel

How to create Stock chart in Excel

Stock charts, as the name indicates, are useful to show fluctuations in stock prices. However, these charts are useful to show fluctuations in other data also, such as daily rainfall or annual temperatures

Note:  For creating Stock chart in Excel, the data needs to be in a specific order.

A Stock chart has the following sub-types −

  • High-Low-Close
  • Open-High-Low-Close
  • Volume-High-Low-Close
  • Volume-Open-High-Low-Close

Stock chart in Excel: 1. High-Low-Close chart:

The High-Low-Close Stock chart in excel is often used to illustrate the stock prices. It requires three series of values in the following order- High, Low, and then Close.

Data used for creation of this stock chart is shown below

DateHighLowClose
8-Feb-17132.22131.22132.04
7-Feb-17132.09130.45131.53
6-Feb-17130.5128.9130.29
5-Feb-17138135135
4-Feb-17136133134
3-Feb-17129.19128.16129.08
2-Feb-17129.39127.78128.53
1-Feb-17130.49127.01128.75
31-Jan-17121.39120.62121.35
30-Jan-17121.63120.66121.63
29-Jan-17118115115
28-Jan-17116113114
27-Jan-17122.35121.6121.95
26-Jan-17122.44121.6121.94
25-Jan-17122.1120.28121.88
24-Jan-17120.1119.5119.97
23-Jan-17120.81119.77120.08
22-Jan-17117114114
21-Jan-17111113112
20-Jan-17120.45119.73120
19-Jan-17120.09119.37119.78
18-Jan-17120.5119.71119.99
17-Jan-17120.24118.22120

To create this Stock chart, you need to arrange the data on your sheet in order of High price, Low price and Closing price.

Then select the data On the INSERT tab, in the Charts group, click the Stock, Surface or Radar chart icon on the Ribbon. In that select the first stock chart (which is High-low-close-chart) as shown below

stock chart in excel 1

The resultant chart will be

stock chart in excel 2

Stock chart in Excel: 2. Open-High-Low-Close Chart:

The Open-High-Low-Close Stock chart is often used to illustrate the stock prices. It is also known as candle stick chart. It requires Four series of values in the following order- Open, High, Low, and then Close.

Data used for creation of this stock chart in excel is shown below

DateOpenHighLowClose
8-Feb-17131.35132.22131.22132.04
7-Feb-17130.54132.09130.45131.53
6-Feb-17129.13130.5128.9130.29
5-Feb-17134138135135
4-Feb-17132136133134
3-Feb-17128.31129.19128.16129.08
2-Feb-17127.98129.39127.78128.53
1-Feb-17127.03130.49127.01128.75
31-Jan-17121.15121.39120.62121.35
30-Jan-17120.93121.63120.66121.63
29-Jan-17114118115115
28-Jan-17112116113114
27-Jan-17122.14122.35121.6121.95
26-Jan-17121.67122.44121.6121.94
25-Jan-17120.42122.1120.28121.88
24-Jan-17119.55120.1119.5119.97
23-Jan-17120120.81119.77120.08
22-Jan-17114117114114
21-Jan-17122111113112
20-Jan-17120.45120.45119.73120
19-Jan-17119.4120.09119.37119.78
18-Jan-17120120.5119.71119.99
17-Jan-17118.34120.24118.22120

To create this candle stick chart, you need to arrange the data on your sheet in order of Open Price, High price, Low price and Closing price.

Then select the data On the INSERT tab, in the Charts group, click the Stock, Surface or Radar chart icon on the Ribbon. In that select the second stock chart (which is Open-High-low-close-chart or Candle Stick chart) as shown below

stock chart in excel 3

The resultant chart will be in black and white as shown

stock chart in excel 4

In order to add color, right click on any of the white candle sticks and fill it with Green color as shown below

stock chart in excel 5

Similarly right click on any of the black candle sticks and fill it with Maroon color.so the final candle stick chart will look like

stock chart in excel 6

Stock chart in Excel: 3. Volume-High-Low-Close Chart:

The Volume-High-Low-Close Stock chart is often used to illustrate the stock prices along with the volume traded. It requires four series of values in the following order- Volume, High, Low, and then Close.

Data used for creation of this stock chart in excel is shown below

DateVolumeHighLowClose
8-Feb-1723,004,072132.22131.22132.04
7-Feb-1738,183,841132.09130.45131.53
6-Feb-1726,845,924130.5128.9130.29
5-Feb-1748,183,841138135135
4-Feb-1742,183,841136133134
3-Feb-1724,507,301129.19128.16129.08
2-Feb-1733,710,411129.39127.78128.53
1-Feb-17111,985,040130.49127.01128.75
31-Jan-1749,200,993121.39120.62121.35
30-Jan-1730,377,503121.63120.66121.63
29-Jan-1740,677,503118115115
28-Jan-1723,347,508116113114
27-Jan-1720,562,944122.35121.6121.95
26-Jan-1726,337,576122.44121.6121.94
25-Jan-1732,586,673122.1120.28121.88
24-Jan-1723,211,038120.1119.5119.97
23-Jan-1722,050,218120.81119.77120.08
22-Jan-1740,677,503117114114
21-Jan-1723,347,508111113112
20-Jan-1732,597,892120.45119.73120
19-Jan-1725,597,291120.09119.37119.78
18-Jan-1723,712,961120.5119.71119.99
17-Jan-1734,439,843120.24118.22120

To create this Stock chart, you need to arrange the data on your sheet in order of Volume, High price, Low price and Closing price.

Then select the data On the INSERT tab, in the Charts group, click the Stock, Surface or Radar chart icon on the Ribbon. In that select the Third stock chart (which is Volume-High-low-close-chart) as shown below

stock chart in excel 7

The resultant chart will be

stock chart in excel 8

Stock chart in Excel: 4. Volume-Open-High-Low-Close Chart

The Volume-Open-High-Low-Close Stock chart is often used to illustrate the stock prices along with volume. It requires five series of values in the following order- Volume, Open, High, Low, and then Close.

Data used for creation of this stock chart in excel is shown below

DateVolumeOpenHighLowClose
8-Feb-1723,004,072131.35132.22131.22132.04
7-Feb-1738,183,841130.54132.09130.45131.53
6-Feb-1726,845,924129.13130.5128.9130.29
5-Feb-1748,183,841134138135135
4-Feb-1742,183,841132136133134
3-Feb-1724,507,301128.31129.19128.16129.08
2-Feb-1733,710,411127.98129.39127.78128.53
1-Feb-17111,985,040127.03130.49127.01128.75
31-Jan-1749,200,993121.15121.39120.62121.35
30-Jan-1730,377,503120.93121.63120.66121.63
29-Jan-1740,677,503114118115115
28-Jan-1723,347,508112116113114
27-Jan-1720,562,944122.14122.35121.6121.95
26-Jan-1726,337,576121.67122.44121.6121.94
25-Jan-1732,586,673120.42122.1120.28121.88
24-Jan-1723,211,038119.55120.1119.5119.97
23-Jan-1722,050,218120120.81119.77120.08
22-Jan-1740,677,503114117114114
21-Jan-1723,347,508122111113112
20-Jan-1732,597,892120.45120.45119.73120
19-Jan-1725,597,291119.4120.09119.37119.78
18-Jan-1723,712,961120120.5119.71119.99
17-Jan-1734,439,843118.34120.24118.22120

To create this Stock chart, you need to arrange the data on your sheet in order of Volume, Open Price, High price, Low price and Closing price.

Then select the data On the INSERT tab, in the Charts group, click the Stock, Surface or Radar chart icon on the Ribbon. In that select the fourth stock chart (which is Volume-Open-High-low-close-chart) as shown below

stock chart in excel 9

 

The resultant chart will be

stock chart in excel 10

so we have successfully created four different types of stock chart in excel

previous small stock chart in excel                                                                                                                next small stock chart in excel