In this article:

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. 

Chart editor, Customize tab, Vertical axis option, Min and Max text boxes shown. 

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

Minimum value of y-axis set at 35,000. Maximum value of y-axis set at 53,000.

The chart immediately updates:

The same candlestick chart. The fluctuation in the price can be clearly seen. 

Sample sheet

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

Candlestick chart sample

Automate repetitive tasks with Lido

Save hours on repetitive and tedious work. Lido is a new spreadsheet that connects your spreadsheets, forms, PDFs, and email inbox.

Select trigger
When a cell value in Google Sheets changes
Check Google Sheets for today's date
When a new row is added to Google Sheets
On new Google Form submission
Send me a daily reminder
On new Typeform submission
When a cell value in database changes
Check database for today's date
When a new row is added in database
When a new HubSpot customer is created
Select action
Creates a one- or multiple-day calendar event with optional attendees
Adds an object to Hubspot
Create a Slack channel, and optionally add a topic or members
Makes any HTTP request
Replaces [@column_name] values in a Google Doc with the corresponding table row's values
Replaces [@column_name] values in a Google Doc with the corresponding table row's values, then export it as a PDF
Makes any HTTP request and returns the response
Inserts given array below defined values in given worksheet
Sends an email using your Google account
Sends an email using your Microsoft Outlook account
Sends a Slack message
Sends an SMS message using your Twilio account
Updates cells with given values
Updates a property of a Hubspot object
Trigger is required
Action is required
Get started