How to build candlesticks in Google Sheets & Excel

1. Manual methods in Google Sheets and Excel

Manual creation is where most teams start. It’s slower, but it teaches the mechanics your AI agent will eventually automate.

‍

A. Manual candlestick charts in Google Sheets

1. Prepare your data
   Structure your table with five columns, one row per period (day, week, month):
   • Column A: Label (e.g., date or period name)
   • Column B: High
   • Column C: Open
   • Column D: Close
   • Column E: Low
   This is exactly how Google Sheets expects candlestick data. See Google’s help article:
   https://support.google.com/docs/answer/9146870
2. Select your data range
   Drag to select the full range including headers, e.g. A1:E31.
3. Insert the chart
   • Go to Insert ▸ Chart.
   • If Sheets guesses a different chart type, don’t worry.
4. Switch to Candlestick
   • In the Chart editor sidebar, open Setup.
   • Under Chart type, scroll to Other or Financial and choose Candlestick chart.
   • Sheets maps the columns as label / high / open / close / low.
5. Customize for clarity
   In the Customize tab of the Chart editor (see general chart help: https://support.google.com/docs/answer/63824):
   • Chart & axis titles: add a clear title like Weekly Revenue Volatility.
   • Horizontal axis: adjust label angle if dates overlap.
   • Vertical axis: set min/max to focus on relevant price or metric range.
   • Gridlines: lighten gridlines so candles stand out.
6. Interpret the candles
   By default, if the open < close, the candle is filled; otherwise it’s hollow. That gives you an instant sense of up vs. down periods.

‍

B. Manual candlestick-style charts in Excel

Excel doesn’t call them “candlestick charts”; it groups them under Stock charts.

1. Prepare OHLC data
   For a classic OHLC stock chart, use:
   • Column A: Date
   • Column B: Open
   • Column C: High
   • Column D: Low
   • Column E: Close
   Microsoft’s guide:
   https://support.microsoft.com/en-us/office/create-a-stock-chart-d3d7ec3e-6590-4acb-9d61-4696beea1a9b
2. Select the data
   Highlight your full range, including headers.
3. Insert the stock chart
   • Go to Insert ▸ Insert Stock, Surface or Radar Chart.
   • Choose Open-High-Low-Close (for true candlestick behavior).
4. Polish the chart
   Use Chart Design and Format tabs to:
   • Change colors of up/down bars.
   • Adjust axis scales and labels.
   • Add a descriptive title and legend if needed.

‍

Pros of manual methods

• Full control over every click and setting.
• Great for learning exactly how Sheets and Excel interpret OHLC data.

‍

Cons

• Repetitive for weekly or daily refreshes.
• Easy to mis-map columns or ranges when datasets change.
• Doesn’t scale if you maintain many similar dashboards.

‍

2. No‑code automation methods

Once you’re comfortable manually, you can remove a big chunk of friction by layering in no‑code automation. Think of this as pre‑AI optimization.

‍

A. Use connected data sources

Tools like Coefficient or native connectors can keep Google Sheets or Excel synced with your CRM, ad platforms, or databases. The candlestick logic stays in your sheet; the data simply updates.

Typical flow:

1. Connect Sheets or Excel to a data source (CRM, analytics, finance system).
2. Map incoming fields to your OHLC columns (e.g., daily high/low MRR, open/close price).
3. Keep a template sheet where the chart already exists.
4. Automations write fresh data into the template range; the chart updates automatically.

This keeps the chart creation manual, but turns data refresh into a background task.

‍

B. Template-driven chart creation

Create a master template in Google Sheets and Excel:

• Tab 1: Raw data (fed by imports or copy-paste).
• Tab 2: Cleaned data with five columns in the exact structure required for candlesticks.
• Tab 3: Prebuilt candlestick chart referencing Tab 2.

To create a new chart for a different client or campaign, you simply duplicate the template file, adjust the data source mapping, and everything else just works.

‍

Pros of no‑code automations

• Removes manual data entry and range re-selection.
• Keeps you in familiar tools (Google Sheets and Excel).
• Easy for non-technical teams to adopt.

‍

Cons

• Still requires initial chart setup by hand.
• If the layout changes (new columns, formats), you must fix each template.
• Automations don’t “reason” about errors; they blindly push data.

‍

3. Scaling with AI agents (including Simular)

For agencies, sales teams, and business owners managing dozens of sheets and workbooks, the real bottleneck is all the glue work: cleaning columns, validating ranges, inserting charts, and fixing subtle layout breaks. This is where AI computer agents shine.

‍

A. AI agent as your spreadsheet operator

Picture an AI agent working like a junior analyst who never gets tired:

1. It opens Google Sheets or Excel on your desktop.
2. It locates or creates your OHLC table, even if columns moved.
3. It inserts a candlestick (or stock) chart, mapping columns correctly.
4. It applies your standard style presets, axis ranges, and colors.
5. It repeats this across multiple client files on a schedule.

With a platform like Simular’s desktop agent, every click is transparent and inspectable, so you can review and adjust its workflow.

‍

B. Workflow examples for AI automation

‍

Workflow 1 – Daily trading dashboard refresh

• Pull latest prices into Google Sheets from a broker export.
• AI agent cleans and reorders to the five-column structure.
• Agent regenerates the candlestick chart if the date range has extended.
• Agent exports a PDF snapshot and drops it into a shared folder.

‍

Workflow 2 – Client portfolio reports in Excel

• Agent opens a master Excel template.
• For each client: duplicates the template, injects that client’s data, ensures the stock chart uses the full new range, then saves to a named folder and emails a link.

‍

Pros of AI‑driven methods

• True end‑to‑end automation: from raw CSV to finished candlestick chart.
• Handles interface changes and popups better than brittle scripts.
• Highly scalable across many Sheets and Excel workbooks.

‍

Cons

• Requires initial “teaching” and testing of the agent’s workflow.
• Best suited when you’re ready to standardize your chart formats and templates.
• Needs light monitoring at first, just like a new human hire.

The pattern is simple: master the manual steps using official docs from Google Sheets (https://support.google.com/docs/answer/9146870 and https://support.google.com/docs/answer/63824) and Excel (https://support.microsoft.com/en-us/office/create-a-stock-chart-d3d7ec3e-6590-4acb-9d61-4696beea1a9b). Then codify those steps into templates, wrap them with no-code automations, and finally let an AI agent take over the full workflow so your team can focus on reading the candles instead of building them.