How to Create and Format Candlestick Stock Charts in Google Sheets

Financial analysts, active traders, and portfolio managers frequently track open, high, low, and close (OHLC) market valuations to identify market trends, volatility spikes, and reversal patterns. Google Sheets features native support for candlestick charts, enabling users to transform tabular financial metrics into intuitive graphical representations without relying on external plugins or manual plotting scripts. Configuring candlestick charts correctly requires a strict columnar architecture, appropriate price scaling, and deliberate colour formatting to distinguish between bullish price rallies and bearish market downturns.

Required Data Structure for Candlestick Visualisations

Unlike standard line or bar charts that accept variable row and column definitions, the Google Sheets charting engine enforces an exact five-column structure for candlestick plots. If the underlying data deviates from this arrangement, the chart tool cannot compute upper shadows, lower wicks, or candle bodies accurately.

Your spreadsheet columns must follow this sequence from left to right:

• Column A (Horizontal Axis / Time): The trading date, timestamp, or security identifier (for example, 2026-03-01 or Day 1).
• Column B (Low / Minimum Price): The absolute lowest price recorded during the trading interval.
• Column C (Open / Starting Price): The security price recorded when the trading window commenced.
• Column D (Close / Ending Price): The security price recorded when the trading session concluded.
• Column E (High / Maximum Price): The absolute highest price recorded during the trading interval.

Ensure that all numerical values in columns B through E are formatted as plain numbers or standard currency. Blank cells or misplaced alphanumeric ticker strings within the numerical ranges will cause the chart engine to omit candle bodies or produce parsing errors.

Building the Candlestick Chart in Google Sheets

Once your trading data adheres to the required five-column sequence, you can generate the baseline visual layout directly from the application toolbar:

1. Highlight the full data range, including the row headers (for instance, cells A1:E31).
2. Click Insert in the top menu and select Chart.
3. In the Chart editor pane that appears on the right-hand side, open the Setup tab.
4. Click the Chart type dropdown menu, scroll down past the standard column and line visualisations to the Other category, and choose Candlestick chart.
5. Verify that the checkbox marked Use row 1 as headers is ticked to ensure that column names are not parsed as numerical trading data.

Google Sheets will automatically map Column A to the X-axis and allocate columns B through E to the low, open, close, and high coordinate inputs respectively.

Customising Candle Bodies, Wicks, and Axis Scales

Raw candlestick outputs often compress price ranges when the vertical axis defaults to zero, making subtle daily movements difficult to analyse. Fine-tuning the display properties improves legibility across volatile trading periods.

1. Within the Chart editor, switch to the Customise tab.
2. Expand the Candlestick section to configure candle fills and outlines:
   • Falling colour: By default, Google Sheets renders falling (bearish) sessions—where the close is lower than the open—as red solid bodies. You can select custom colour accents to match company branding or reporting standards.
   • Rising colour: Rising (bullish) sessions—where the close exceeds the open—are typically rendered as green or hollow bodies. Select a high-contrast hue to ensure clear differentiation.
3. Expand the Vertical axis section to eliminate excess whitespace below the trading floor:
   • Set the Min value slightly below your dataset’s historical lowest price. For example, if a stock traded between £142 and £165, setting the minimum value to 140 expands the vertical range.
   • Set the Max value slightly above the trading peak to give upper wicks adequate breathing space.
4. Expand the Gridlines and ticks section to enable minor horizontal divisions, facilitating fast price estimations along the vertical axis.

Automating OHLC Price Pulls via GOOGLEFINANCE

To eliminate manual data entry, combine your candlestick chart with automated stock feeds generated by the GOOGLEFINANCE function. The function retrieves historical market records dynamically and populates the spreadsheet in an organised grid.

Because the raw array returned by GOOGLEFINANCE outputs columns in the order of Date, Open, High, Low, Close, and Volume, you must reorganise the array using the CHOOSECOLS or QUERY function to match the strict Low-Open-Close-High sequence demanded by the candlestick chart:

=CHOOSECOLS(GOOGLEFINANCE("NASDAQ:GOOG", "all", TODAY()-30, TODAY()), 1, 4, 2, 5, 3)

This formula queries thirty calendar days of trading data for Google stock, extracting the timestamp (column 1), low price (column 4), open price (column 2), close price (column 5), and high price (column 3). When point-to-point chart references are connected to this dynamic formula range, the visual candlestick display refreshes whenever Google Sheets updates exchange rates and market quotations.