How to Make Candlestick Charts in Google Sheets

Unsure which chart type to use? Click here to find the right chart for your data and the ideas that you want to convey.

Candlestick charts are prominently used in financial markets to visualize the movement of prices of financial products such as securities, derivatives, and currencies. For a given time period, It visualizes the maximum price and the minimum price as the ends of the thin lines, while the range between the opening and closing price is marked by a solid rectangle. Gains and losses are indicated with green and red shades, respectively. 

In this tutorial, you will learn how to make a candlestick chart in Google Sheets.

We will use the Bitcoin prices as sample data for our tutorial, using the data available at CoinCodex.

How to format the data for candlestick charts

‍

A candlestick chart requires five columns:

• The first column contains the label. Most of the time, the time interval will be in this column. This column must be text. If you are given a date, you must convert it into a text string. Here is our tutorial on how to convert numbers to text strings.. 
• The second column contains the minimum value (often called low) for the given time interval.
• The third column contains the opening value (often called open) of the time interval.
• The fourth column contains the closing value (often called close) of the time interval.
• The fifth column contains the maximum value (often called high) for the given time interval. 
  

To help you further, here is a diagram showing a candlestick with these values:

‍

The location of the opening and closing value for a given candlestick can be determined by its color. If the candlestick is green, then the closing price is at the top of the rectangle, signifying a net gain. If the candlestick is red, then the closing price is at the bottom of the rectangle, signifying a net loss.

How to make a candlestick chart

‍

For this tutorial, we will include a step for processing 

Step 1: We need to convert the date into a string. To do so, insert a new column to the right of the first column.

‍

Add the TO_TEXT() formula to the cells of the new column, pointing to the original column.

‍

Step 2: Select the data, then go to Insert and select Chart from the drop-down menu. 

‍

Step 3: Go to the Chart editor sidebar that will appear on the right side of Google Sheets, then go to Chart type, scroll down, and select Candlestick chart. 

‍

Here is the result:

‍
Google Sheets shades the net gains as filled boxes and the net losses as hollow boxes. You can further modify the chart. Here is our starter guide for the basic actions you can do to improve any chart.

How to change the bounds of the y-axis of a candlestick chart

Step 1: On the Chart editor, click the Customize tab, then look for Vertical axis. There are two small text boxes labeled Min and Max. This is where you can specify the minimum and maximum values for the y-axis. 

Step 2: Input the values in the corresponding text boxes.

The chart immediately updates:

‍

Sample sheet

Below is a link to the sample sheet containing the candlestick chart we made in this tutorial.

Candlestick chart sample