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

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

Trigger
Action
Select trigger
When a cell value in Google Sheets changes
Check Google Sheets for today's date
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
Create a Slack channel, and optionally add a topic or members
CALLURL
Makes any HTTP request
Replaces [@column_name] values in a Google Doc with the corresponding table row's values
CREATEPDF
Replaces [@column_name] values in a Google Doc with the corresponding table row's values, then export it as a PDF
FETCH
Makes any HTTP request and returns the response
INSERTROWS
Inserts given array below defined values in given worksheet
SENDGMAIL