Candlestick Chart in Excel (Table of Contents)
Introduction to Candlestick Chart in Excel
If you are not familiar with Candlestick chart in excel, it is a chart that can be used to represent the stock data in excel. Analyzing stock data in a simpler manner is a big task. Because you always will have a lot of history getting generated. Fortunately enough, excel has inbuilt stock charts of different types for this purpose. One of those charts is the Open-High-Low-Close chart which is also known as Candlestick chart. This chart is usually helpful in describing the stock, currency, capitals, derivatives movements over time. It allows the user to have a clean and simple visually attractive output which is easy to understand as well. This graph allows you to have an analysis of stock and price patterns.
There are two types of Candlestick chart in Excel:
- Open-High-Low-Close chart: It is a conventional candlestick chart that uses stock values arranged in order as Opening value, Highest Value, Lowest Value, Closing Value.
- Volume-Open-High-Low-Close Chart: This is the chart that is similar to the first one. However, it adds the stock volume as well under graphical representation to have a better visualization output.
How to Create a Candlestick Chart in Excel?
Candlestick Chart in Excel is very simple and easy. Let’s understand how to Create Candlestick Chart in Excel with some examples.
Example #1 – Candlestick Chart
Now, we will see how to create a conventional Open-High-Low-Close stock chart a.k.a. Candlestick chart.
Suppose we have data as shown in the given screenshot below. This data consists of an opening value, the highest value, lowest value, and closing value for stock in US dollars.
Now, we will see how we can create a candlestick chart using this information step by step:
Step 1: Click on Insert tab present on the uppermost corner of the ribbon. Select Recommended Charts under it.
Step 2: Once you click Recommended Chart option, it opens up with a list of charts suitable for your data. Click on All Charts option and you’ll see all the charts list. Select Stock in the left-most corner. It has all the charts associated with stock data.
Step 3: On the right-hand side, you’ll see all recommended charts under stock charts section. Select Open-High-Low-Close chart under it. Which is nothing but a second graph out of four.
Step 4: After doing this, right-click on graph layout and choose Select Data option from the list of options.
Step 5: A new pop up window will appear named Select Data Source. In this window, on the right-hand side go to Edit option under Horizontal (Category) Axis Labels.
Step 6: Again a new dialog box will appear called Axis Labels. Under ”Axis label range:”, select the range of data which you wanted to be under axis. In this example, select A3:A7
Step 7: Click OK button twice. Once for Axis Labels and the second time for Select Data Source window. Add chart title as “Candlestick Chart” under Chart Title option on graph generated. You should be able to see the graph output as below.
If you can see, this chart has boxes with lines extending upwards and downwards the boxes. If the box is empty then the opening value is the same as the lower value for the stock and you ideally have gained in your stocks. If the box is filled, the opening value is nothing but the upper bound/high value of the stock and you ideally have been in loss.
Example #2 – Candlestick Chart with Volume
Suppose you have a data as shown below which contains volume traded for stocks, opening value, highest value, lowest value, and closing value.
We will see step by step how to create a candlestick chart from this data with volume.
Step 1: Select the data from cell B3:B7 which needs to be included in the chart.
Step 2: Click on Insert tab present on the uppermost corner of the ribbon. Select Recommended Charts
Step 3: On the right-hand side, you’ll see all recommended charts under stock charts section. Select Volume–Open-High-Low-Close chart under it. Last graph in the list.
Step 4: Hit the OK button and you will be able to see the initial graph layout as below.
Don’t get confused with the layout. It’s halfway done. We are yet to add the axis labels under it and format the same.
Step 5: Right-click on graph layout and choose Select Data option from the list of options.
Step 6: A new pop up window will appear named Select Data Source. In this window, on the right-hand side go to Edit option under Horizontal (Category) Axis Labels.
Step 7: A new dialog box will appear once again, called Axis Labels. Under ”Axis label range:”, select the range of data which you wanted to be under axis. In this example, select A3:A7.
Step 8: Click OK twice and you should be seeing the graph layout as below:
Step 9: Change the chart title as “Candlestick Chart with Volume” and save the sheet.
This is how you create the Candlestick chart in Microsoft Excel with stock data for better visualization and that is it from this article on how to create the Candlestick Chart in Microsoft Excel. Let’s wrap the things with some things to be remembered.
Things to Remember About Candlestick Chart in Excel
- It is absolutely necessary to put your data column-wise in sequences. Like for Candlestick Chart, the sequence in which data should be added in columns is Date, Open Value, High Value, Low Value, Close Value. The same is the case for Candlestick Chart with volume. If the data is not sequential, you will not be able to add the chart. Ideally, the name of the chart indicates the sequence of the data to be inputted.
- Candlestick chart is known as Open-High-Low-Close chart or Volume-Open-High-Low-Close chart as well in excel. Actually, these are the conventional names for the graphs and Candlestick Chart is the name that is used because the graph looks like candlesticks standing tall on the surface.
- Every box has elongated tales with them. These are helpful in figuring out whether the stock value is going up or down and eventually you are making money or losing the same.
- This chart is pretty simple to understand and with naked eyes itself, the user can decide whether the stock is moving up or down.
Recommended Articles
This is a guide to Candlestick Chart in Excel. Here we discuss How to Create Candlestick Chart in Excel along with practical examples and downloadable excel template. You can also go through our other suggested articles –
