Being able to create graphs and charts in Google Sheets is a great skill to have.
Data visualization makes it easier to understand and present data. One such useful way of representing data is through a candlestick chart.
In this tutorial, I will show you how to create a candlestick chart in Google Sheets.
Table of Contents
What is a Candlestick Chart?
A candlestick chart is a kind of chart used in the analysis that displays opening values, closed values, and total variances of values over a period of time.
They are often used to track stock prices over a specific period of time. Candlestick charts used in this context will show the opening price, closing price, low value, and high value of stock securities for a given time period.
This kind of data visualization can be incredibly useful for traders looking to identify patterns and trends in data.
Making a Candlestick Chart in Google Sheets
Before creating a candlestick chart, you will need to have the data that will be used in the chart.
You will need to have Open, High, Low, Close, and Date data. Here is the data that I will be using to create the chart in this walkthrough:
Formatting Your Data
Before creating the chart, we want to minimize the chance of mistakes, so we want to take the time to format our data appropriately so that Google Sheets can create the chart correctly.
Before creating a candlestick chart, you have to make sure your columns are in the appropriate order. You’re going to want to rearrange your data so that it is in this order: Date-Low-Open-Close-High.
Here is my data rearranged in the appropriate order:
Next, we want to make sure the format of our cells is appropriate. The best practice is to change the format of all of your price values to Financial format and your date values to Plain text format
To change the format of your price values to Financial format:
1. Highlight the range of cells that contains your price values
2. In the top menu select Format>Number>Financial
To change the format of your dates to Plain text:
1. Highlight the range of cells that contains your dates
2. In the top menu select Format>Number>Plain text
Creating the Candlestick Chart
Now that your data is formatted correctly, we can move on to actually creating the chart.
Here is what you need to do:
1. Highlight the range of data that will be used in your candlestick chart. You want to include every column, headers and all.
2. In the top menu select Insert and then select Chart
3. You should now see a chart on your screen and the Chart editor should appear on the right-hand side of your screen. We need to change the chart type. Find the “Chart type” area and select the drop-down arrow to select a different kind of chart
4. Select the Candlestick chart option
5. You have now created a candlestick chart in Google Sheets. You should be seeing a chart that looks like the below image in your sheet:
Customizing the Candlestick Chart
Now that you’ve created your chart, you may want to customize it to edit how it displays. To do this you will need to open the chart editor.
If it is not already open, double click on your chart to access the chart editor. You can also select the three-dot menu in the top right of your chart and select Edit chart.
After the Chart editor is open, select Customize
After you enter the Customize tab, there are 5 different areas that you can access to edit your candlestick chart:
- Chart style – here you will find options to change the color and font of your chart
- Chart & axis titles – here you can change the font and edit the title of your chart
- Horizontal axis – in this option you will find settings for changing your horizontal axis. You can change the font, text color, and label styles for your horizontal access
- Vertical axis – similar to the horizontal axis settings you can change the same things on your vertical axis here
- Gridlines and ticks – in this option you will find settings to change the spacing values, gridline colors, and add additional gridlines and ticks to your chart if desired
Candlestick charts are incredibly useful if you need to analyze pricing movements over time. You can see that with a little know-how, they are actually very easy to create on your own in Google Sheets.
If you really want to use Google Sheets for your securities analysis, you can use the GOOGLEFINANCE function to fetch current or historical stock information and use the data to create a candlestick chart. That is beyond the scope of this article, but there are many useful things you can do once you learn to create and edit these kinds of charts.