How to Create a Candlestick Chart in Excel

A candlestick chart can look complex at first, but it is one of the most powerful ways to visualize the volatility of a metric over time. While most people associate it with stock prices, its ability to show an opening, closing, high, and low value in a single snapshot makes it incredibly useful for business data, too. This guide will walk you through exactly how to prepare your data and create a clear, insightful candlestick chart from scratch in Microsoft Excel.

Graphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Build Your First Agent

What Exactly Is a Candlestick Chart?

A candlestick chart is a type of financial chart used to describe price movements of a security, derivative, or currency. However, its use extends far beyond Wall Street. Fundamentally, each individual "candlestick" visualizes four key data points for a single time period (like a day or a week):

• Open: The first value recorded in the time period.
• High: The highest value recorded during the period.
• Low: The lowest value recorded during the period.
• Close: The final value recorded at the end of the period.

These four points are represented visually in two parts:

1. The Body (the thick part): This represents the range between the opening and closing values. Its color indicates direction. Traditionally, if the close is higher than the open, the body is white or green (a positive period). If the close is lower than the open, the body is black or red (a negative period).
2. The Wicks (or Shadows): These are the thin lines extending above and below the body. The top wick shows the highest point reached (the "High"), and the bottom wick shows the lowest point reached (the "Low").

Combined, these elements give you a quick, comprehensive view of a metric's activity and volatility within a specific timeframe.

Beyond Stock Prices: Business Use Cases for Candlestick Charts

The Open-High-Low-Close formula can be applied to many business metrics, revealing patterns that a simple line chart would miss. Think of "Open" as the value at the start of the day and "Close" as the value at the end of the day.

• Daily E-commerce Sales: See the starting sales figure in the morning (Open), the total at the end of the day (Close), and the intra-day high and low points. This can help you understand daily sales velocity and the impact of flash sales.
• Website Traffic: Track daily or weekly user sessions. 'Open' could be your user count at midnight, 'Close' the count 24 hours later, with 'High' and 'Low' reflecting traffic peaks and troughs during the day. This is great for seeing the effect of a campaign or a viral social media post.
• Marketing Ad Spend: Visualize your daily ad budget consumption. 'Open' starts at $0, 'High' is the peak spend, 'Low' is less relevant here but can remain $0, and 'Close' is the final amount spent. A series of long red candles might show your budget is running out too quickly.
• Customer Support Tickets: Track the net change in open support tickets each day. 'Open' is the number of tickets at 9 AM, 'Close' is the count at 5 PM, 'High' is the peak number of tickets in the queue, and 'Low' is the fewest.

AI Agents for Marketing

Crash Course

Graphed

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Download the PDF

Step 1: Preparing Your Data for Excel

Excel is smart, but it has strict requirements for creating a candlestick chart. Your data must be organized in a specific order for the chart to render correctly. The standard format includes volume data, even if you don't plan to show it. If you don't have volume, you can simply use the Close value or any other numeric column that makes sense.

Your columns must be arranged in one of two ways:

• Option 1 (Typical): Open-High-Low-Close
• Option 2 (With Volume): Volume-Open-High-Low-Close

For our example, let's track the daily performance of an e-commerce store, along with the volume of orders. Set up your spreadsheet with these columns in this specific order:

Date | Orders | Opening Sales ($) | High Sales ($) | Low Sales ($) | Closing Sales ($)

Example Dataset:

Here’s what our data looks like for a sample week. Notice how 'Opening Sales' is often near the previous day's 'Closing Sales', and the 'High' and 'Low' values fall outside the Open-Close range.

Pro-Tip: Ensure your Date column is formatted correctly as a 'Date' in Excel. Mismatched formats can cause charting errors.

Step 2: Creating the Candlestick Chart

Once your data is correctly formatted, creating the chart takes just a few clicks. Follow these steps:

1. Select Your Data

Click and drag to highlight the entire data range you want to visualize, including the column headers.

Graphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Build Your First Agent

2. Insert the Chart

On the ribbon at the top of Excel, click the Insert tab. Find the Charts group and click the dropdown menu for Recommended Charts or click the small arrow icon in the bottom-right corner of the Charts section to open the “All Charts” dialog box directly.

3. Choose the Correct Chart Type

In the "All Charts" window:

• Select the Stock category from the list on the left.
• Several stock chart options will appear. Hover over them to see what they require.
• For our example, choose Volume-Open-High-Low-Close. Excel will show you a small preview. If you didn't have a volume-related column, you would choose Open-High-Low-Close.

Click OK. Excel will instantly insert the candlestick chart into your worksheet.

Step 3: Customizing and Formatting Your Chart

The default chart is functional, but a little formatting can make it much easier to read and understand. Here are some key customizations you should make.

1. Change the Candle Colors (Up vs. Down)

Excel's default might use plain black and white, but red and green are more intuitive for "down" and "up" periods. To change this:

• Single-click on one of the "down" (e.g., black) candles. This will select all the down candles in the series.
• Right-click the selected candles and choose Format Data Series.
• A pane will open on the right. Click the Paint Bucket icon for Fill & Line options.
• You will see options for "Up Bars" and "Down Bars".
• For "Down Bars", select 'Solid Fill' and choose a red color.
• Click on the dropdown for "Up Bars", select 'Solid Fill' and choose a green color.

2. Improve the Axes

The chart's axes can also be cleaned up for better readability.

• Value Axis (Y-Axis): Double-click the vertical axis (where the sales figures are). In the "Format Axis" pane, you can adjust the minimum and maximum bounds to give your data more room to breathe. Under the 'Number' section at the bottom, you can also format the labels to "Currency" with the dollar sign ($).
• Category Axis (X-Axis): Double-click the horizontal axis (where the dates are). You can change the label frequency or date formatting under the 'Axis Options' and 'Number' sections.

3. Add or Modify the Chart Title

A good title is critical for context. Simply double-click on the "Chart Title" placeholder text at the top and type in something descriptive, like "Daily E-commerce Sales Performance - First Week of December". You can format the font size and color using the standard home tab tools.

AI Agents for Marketing

Crash Course

Graphed

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Download the PDF

4. Adjust the Volume Bars

The volume bars give you a sense of activity. In our example, they show the number of orders each day. You can customize them too:

• Single-click on any of the blue volume bars.
• In the "Format Data Series" pane, go to the three-bar icon for Series Options.
• Adjust the Gap Width. Decreasing it will make the columns wider and more prominent. A setting of 50-75% usually looks good.

Reading and Interpreting Your New Chart

Now that you have your beautifully formatted chart, what does it tell you?

• Long Green Candles: A strong positive day. Sales increased significantly from morning to night.
• Long Red Candles: A weaker day. Sales ended much lower than where they started, suggesting a drop-off in the afternoon or evening.
• Short-Bodied Candles with Long Wicks: A day with high volatility. Sales fluctuated wildly during the day but ended near where they started. Maybe you had a mid-day flash sale that drove a traffic spike (long top wick) but overall conversion didn't hold.
• High Volume on a Big Green Day: Your positive performance was backed by a lot of orders, indicating genuine strength.
• High Volume on a Big Red Day: A high number of orders occurred, but sales still closed down. Perhaps it was a day with many low-value-item sales or returns.

This level of detailed, daily insight is something a simple line or bar chart would completely obscure.

Common Issues and Quick Fixes

• Chart Appears Blank or Jumbled: The most common problem is incorrect column order. Double-check that your data is set up exactly as required: [Volume], Open, High, Low, Close.
• Dates Are Wrong: Ensure the date column is actually formatted as a 'Date' type in Excel. Right-click the column, select "Format Cells," and choose the 'Date' category.
• Candles Look Weird: Verify that your High value is always the highest number for that day and your Low value is the lowest. If High is lower than Open or Close, the chart may not render correctly.

Final Thoughts

Creating a candlestick chart in Excel is a straightforward process once your data is properly structured. It’s an incredibly valuable visualization tool that allows you to analyze volatility and performance in a way that standard charts can't, providing much deeper insights into your daily business metrics.

This kind of multi-platform, manual reporting in spreadsheets can eat up a lot of time - from downloading different CSVs to wrangling columns into the perfect format. That's why we created Graphed. We wanted to make it easy for anyone on the team to get these kinds of powerful visualizations in seconds, not hours. By connecting directly to sources like Shopify, Google Analytics, and various ad platforms, we automatically create real-time charts and dashboards using simple, natural language, freeing you up to uncover insights instead of just formatting data.