How to Make a Candlestick Chart in Google Sheets

A candlestick chart instantly tells the story of a stock's price movement, but they look much more complicated to create than they actually are. With the right data and a few clicks, you can build your own right inside Google Sheets to track stocks, crypto, or any other asset that has a high, low, open, and closing price. This tutorial will walk you through exactly how to format your data, create the chart, and customize it to make it clear and readable.

What Exactly is a Candlestick Chart?

Before jumping into Google Sheets, let’s quickly break down what a candlestick chart really shows. It's a type of financial chart used to describe price movements of a security, derivative, or currency over a given period. Each "candlestick" on the chart typically represents one day, but the time period can also be an hour, a week, or a month.

There are two main parts to every candle:

• The Body: This is the thicker, rectangular part of the candle. It shows the range between the opening and closing price for the period.
• The Wicks (or Shadows): These are thin lines that extend above and below the body. They represent the highest and lowest prices reached during that same period.

The color of the candle's body tells you the direction of the price movement. While the specific colors can be customized, the standard scheme is:

• A green (or white) candle means the closing price was higher than the opening price. This is often called a bullish or "up" candle.
• A red (or black) candle means the closing price was lower than the opening price. This is known as a bearish or "down" candle.

By looking at a series of these candles, you can quickly spot trends, patterns, and general market sentiment in a way that’s much more visually intuitive than staring at a table of numbers.

Step 1: Preparing Your Data for a Candlestick Chart

This is the most important step in the entire process. If your data isn't structured exactly how Google Sheets expects it, the candlestick chart option won’t work properly. It might not even show up as an option.

A candlestick chart requires five specific columns of data for each time period.

The Required Data Order for Google Sheets

Here’s the part that trips most people up. A lot of financial data is presented in the "Open, High, Low, Close" (OHLC) format. However, for its candlestick chart, Google Sheets requires a different order:

1. Date (or Time period): This will serve as the label for your X-axis.
2. Low: The lowest price of the period.
3. Open: The price at the start of the period.
4. Close: The price at the end of the period.
5. High: The highest price of the period.

Your first column identifies the time period, and the next four numeric columns must be in that specific Low-Open-Close-High sequence. It's an unusual order, but sticking to it is the key to making this work.

Sample Data Structure

Let's create a clear example you can follow. Here’s how your sheet should be set up with some sample data for a fictional tech stock, "Innovate Inc."

Your Google Sheet should look like this:

You can get this type of data from financial data sites like Yahoo Finance, which lets you download historical data for free into a CSV that you can then open with Google Sheets. Just remember you may need to rearrange the columns to match the Date-Low-Open-Close-High format.

Step 2: Creating Your Candlestick Chart in Google Sheets

Once your data is correctly formatted, creating the chart is incredibly simple. Just follow these steps:

1. Select Your Data Range: Click and drag your cursor to select all the cells containing your data, including the headers. Using our example above, you would select the range A1:E6.
2. Insert the Chart: With your data selected, navigate to the top menu and click Insert > Chart.
3. Change the Chart Type: Google Sheets will likely pop up a default chart, often a line or column chart. Don't worry, this is normal. On the right side of your screen, the Chart editor sidebar will appear. Go to the Setup tab and click the dropdown menu under Chart type.
4. Select the Candlestick Chart: Scroll down through the list. The Candlestick chart is near the bottom, usually under the "Other" category. Click on it.

If your data was structured correctly, your line chart will immediately transform into a beautiful candlestick chart. It’s that simple!

Step 3: Customizing and Refining Your Chart

The default chart works, but you can make it much cleaner and easier to read with a few adjustments. In the Chart editor sidebar, switch from the Setup tab to the Customize tab.

Here are a few key adjustments worth making:

Chart & Axis Titles

It's always a good practice to title your chart and axes. Under the "Customize" tab, click on Chart & axis titles.

• For the Chart title, give it a descriptive name like "Innovate Inc. (INN) Stock Price - Nov 2023."
• You can title the Vertical axis "Price (USD)" to add context.

Series Colors

The default red and green colors are standard, but maybe you prefer a different scheme for aesthetic reasons or for colorblind-friendly viewing. Under Series, you can change the colors. Here, you'll see two key options:

• Falling color: Controls the color of the candle body when the price drops (Close < Open). Red is the conventional color.
• Rising color: Changes the color of the candle body when the price rises (Close > Open). Green is the standard.

Adjust these to your liking to ensure your chart communicates effectively.

Horizontal and Vertical Axis

These sections give you more control over the look of your chart's grid.

• Horizontal Axis: If your date labels are too crowded, you can go to this section and enable "Slant labels" to give them more space.
• Vertical Axis: This is where you can fine-tune your price scale. If all your price action happens in a narrow band, like between $140 and $160, you can set the "Min" and "Max" values here to zoom in on that area and make the candle details more prominent. This can prevent the candles from looking small in an otherwise large chart area.

Adding major grid lines in this section can also help read the price levels across the chart more easily.

Troubleshooting Common Issues

Sometimes, things just don't go as planned. Here are some of the most common hiccups when making candlestick charts in Google Sheets and how to solve them.

"The Candlestick Chart Option is Grayed Out, or a Line-Chart Appears"

This is almost always a data column issue. The #1 cause of failure is incorrect column order. Go back to your sheet and double-check that your columns follow the correct Date, Low, Open, Close, High structure. If you have an OHLC format, you may need to rearrange your columns into the proper order before rebuilding the chart.

"All of My Candles Are One Color, or Some Are Missing Their Bodies"

This usually indicates a problem with your open and close data. For a candle to have a body, the open and close prices must be different. When the open and close are the same (or nearly the same), the candle will appear as a line.

If all are green, it could mean that every closing price in your data range was higher than the opening price, and vice versa for red. If that's not right, check that your open and close data aren't accidentally reversed.

"The Dates on My Horizontal Axis Are Bunched Up"

This is a common issue when you are plotting high-frequency data (like daily prices or even more frequently).

Open the Chart Editor, and in the Horizontal Axis section, turn on the "Slant labels" option to angle the text for better readability. If that doesn’t work well, you may need to reduce your data set, such as plotting only every other day.

Final Thoughts

Creating a candlestick chart in Google Sheets is a surprisingly simple process once you understand how to structure the data correctly. It's a powerful tool to visualize financial data, giving you a quick snapshot of price movement and market sentiment at a glance.

There are also options for integrating your Google Sheets data with other platforms to create more advanced dashboards. By exporting or linking your data to tools like a CRM or dedicated financial software, you can build an intricate, dynamic dashboard tailored to specific needs without constantly updating figures manually.