Learn how to make candlestick charts in Google Sheets, useful for showing the movement of prices
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.
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.
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:
Below is a link to the sample sheet containing the candlestick chart we made in this tutorial.