How to Create a Candlestick Chart in Google Sheets


It’s a valuable ability to be able to generate graphs and charts in Google Sheets.
Data visualization aids in the comprehension and presentation of information. A candlestick chart is one such useful technique of expressing data.

A candlestick chart is a type of chart used in analysis that shows starting and closing values as well as total variations of values across time.
They’re frequently used to keep track of stock prices over time. In this case, candlestick charts will display the opening, closing, low, and high values of stock securities for a given time period.
For traders attempting to spot patterns and trends in data, this type of data visualization can be extremely valuable.

candlestick chart example

Steps to Create Candlestick Chart In Google Sheets

  • Formatting the Data

We want to reduce the potential of errors before making the chart, so we’ll take the effort to properly format our data so that Google Sheets can construct the graphic.
Before you start making a candlestick chart, double-check that your columns are in the right order. You’ll want to reorganize your data in the following order: Date-Low-Open-Close-High.

Date-low-open-close-high

Next, we’ll double-check that the format of our cells is correct. Changing the format of all of your price values to Financial format and your date values to Plain text format is the recommended practice.
To convert your price values to financial format, follow these steps:

  • Select your target range of cells.
financial format-1
  • From the Format tab on menu bar, select Number option and from further pop-up select Financial option.
financial format-2

Or, you can also change the format of your data to plain text by –

plain text format-2
  • Creating the Candlestick Chart

Now, to insert a chart in your sheet-

  • Select the range of cells that contains data for the chart.
Create candlestick chart-1
  • From the insert tab of menu bar, select Chart option.
Create candlestick chart-2
  • Now a chart editor will appear on right side of your screen. Here, from a Chart Type drop-down box, select the Candlestick Chart.
Create candlestick chart-4
  • Now, you’ll be able to see a Candlestick Chart representing all your data in a chart form.
Create candlestick chart-5
  • Customizing the Chart

You might wish to change the way your chart looks by customizing it. You’ll need to open the chart editor to do this.
Double-click your chart to open the chart editor if it isn’t already open. You can also pick Edit chart from the three-dot menu in the top right corner of your chart.
Select Customize once the Chart editor is open.

customize candlestick chart-1

You can change your candlestick chart in five different ways once you’ve entered the Customize tab:
1. Chart style – this section has choices for changing the color and typeface of your graph.
2. Chart & Axis Titles – change the typeface and update the title of your chart in this section.
3. Horizontal axis – in this option, you can change your horizontal axis settings. For your horizontal access, you can modify the font, text color, and label styles.
4. Vertical axis – You can adjust the same things on your vertical axis that you can on your horizontal axis.
5 Gridlines and ticks — Change the spacing values, gridline colors, and add additional gridlines and ticks to your chart with this option.

Select according to your need and customize your chart.

Thus, If you need to evaluate pricing variations over time, candlestick charts are extremely useful. You can see that with a little know-how, they’re actually quite simple to make in Google Sheets on your own.

Harshita Mathur

My name is Harshita Mathur. I come from Jaipur, Rajasthan. I am a law student. Apart from this I am an amateur writer.

Recent Content