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:

*Parts of a candlestick in a candlestick chart.* *Image source*

‍

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.

*An empty column inserted after the first column.*

‍

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

*TO_TEXT formula added to the inserted column.*

‍

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

*The data selected, Insert option selected in the main menu, and Chart option highlighted.*

‍

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.

*The candlestick chart option highlighted from the chart types available in Google Sheets.*

‍

Here is the result:

*The resulting candlestick chart. You can further improve upon it.*

‍
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.