Skip to Content

How to Create a Candlestick Chart in Google Sheets

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.

candlestick chart example

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:

Candlestick chart data

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:

Date-low-open-close-high

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

financial format-1

2. In the top menu select Format>Number>Financial

financial format-2

To change the format of your dates to Plain text:

1. Highlight the range of cells that contains your dates

plain text format-1

2. In the top menu select Format>Number>Plain text

plain text format-2

 

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.

Create candlestick chart-1

2. In the top menu select Insert and then select Chart

Create candlestick chart-2

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

Create candlestick chart-3

4. Select the Candlestick chart option

Create candlestick chart-4

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:

Create candlestick chart-5

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

customize candlestick chart-1

After you enter the Customize tab, there are 5 different areas that you can access to edit your candlestick chart:

  1. Chart style – here you will find options to change the color and font of your chart
  2. Chart & axis titles – here you can change the font and edit the title of your chart
  3. 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
  4. Vertical axis  – similar to the horizontal axis settings you can change the same things on your vertical axis here
  5. 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

Closing Thoughts

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.

More Google Sheets Tutorials:
How to Add Error Bars
How to Make a Histogram
How to Find Slope
How to Save a Chart as an Image