Charts don’t just show numbers.

They show behavior.

They expose the fear, greed, hesitation, and euphoria that live inside every market move.

And if you know what to look for, they’ll tell you where the crowd is leaning, and when it might break.

Of all the ways to chart, candlesticks do this best.

They’ve been the go-to chart for traders for a reason.

They don’t just tell you where a price started and ended. They show you the fight in between.

They bring the story into focus. You can see the struggle – the highs traders chased, the lows they feared, and everything in between.

Every candle becomes a snapshot of sentiment, volatility, and momentum.

And while some people think you need expensive platforms to chart like the pros, you can do it right inside Excel.

In this guide, we'll show you how to build an Excel candlestick chart from scratch.

What is a Candlestick Chart?

A candlestick chart is a way of showing price action over time, but it does it with more personality than your typical line chart.

Instead of just plotting where the price went, candlesticks show the story behind the move.

Each candlestick packs four crucial pieces of information into a single visual:

• Open: Where the price started during the time period.
• High: The highest price reached.
• Low: The lowest point.
• Close: Where the price ended.

All of this is wrapped into one candlestick – usually color-coded (like green for up, red for down) – with a body and two wicks (also called shadows) sticking out.

The body shows the range between the open and close, while the wicks show how far the price stretched before settling.

Why do traders and investors love them?

Because they make it ridiculously easy to spot patterns, reversals, and momentum at a glance.

Candlestick charts expose price battles (who’s in control, bulls or bears) and they do it with a clarity that raw numbers or line charts just can’t match.

That’s why you’ll find candlestick charts everywhere from day trading desks to long-term investing playbooks.

Can You Create Candlestick Charts in Excel?

Short answer?

Yes, you can.

Excel has built-in support for candlestick charts; it just calls them Stock Charts.

Versions That Support It

• Windows: Excel 2010 and newer (including Excel 365).
• Mac: Supported, but some features or chart formatting might behave slightly differently depending on your Excel version.

Basically, if your Excel isn’t ancient, you’re good to go.

Common Roadblocks (and How to Dodge Them)

While Excel makes candlestick charts possible, it doesn’t always make them painless.
Here are some of the usual hiccups users hit:

• Data formatting issues: Excel demands your data to be in a specific order (Date, Open, High, Low, Close). Get it wrong, and your chart won’t work (or worse, will look bizarre).
  
• Missing chart type confusion: Some users struggle to find the correct chart type. It’s tucked under Stock Charts, not under the more obvious chart options.
  
• Customization headaches: Want to tweak colors, add moving averages, or layer on volume? Excel doesn’t make this as intuitive as it could be, especially for first-timers.
  
• Mac quirks: Certain formatting options and chart features may be missing or harder to adjust on Excel for Mac.

Step-by-Step: How to Create an Excel Candlestick Chart

Creating a candlestick chart in Excel isn’t rocket science, but it does have its quirks.

Here’s how to nail it, step by step:

1. Prepare Your Data (The Crucial First Step)

Excel is picky when it comes to stock charts. Your data needs to be clean and in the right order.
Here’s the format Excel expects:

• Date
• Open Price
• High Price
• Low Price
• Close Price

Example Table Format:

Date	Open	High	Low	Close
2024-05-01	150.00	155.00	148.00	154.00
2024-05-02	154.00	158.00	152.00	157.00
2024-05-03	157.00	160.00	155.00	159.00

Shortcut with Wisesheets:

Setting up this data manually is a headache.

With Wisesheets, you just punch in a formula, and Excel pulls the data for you – Open, High, Low, Close, whatever you need.

If you're also looking to pull in real-time prices directly into Excel, check out this guide on how to get live stock prices in Excel.

It saves you from downloading files, fixing formats, or double-checking if you missed a day.

2. Insert the Stock Chart

Once your data’s ready, let’s plot the chart.

1. Highlight your data, including headers.
2. Go to Insert > Charts > Stock Chart.
3. Choose Open-High-Low-Close chart (this is the candlestick format).

3. Customize the Chart (Make it Market-Ready)

At this stage, you’ll see a basic candlestick chart, but it might look clunky.

Here’s how to clean it up:

• Format the axes: Ensure the date axis displays cleanly without overlaps.
• Tweak the colors: Adjust the fill and line colors for up and down candles to match standard market visuals (green for up, red for down, for example).
• Remove clutter: Get rid of unnecessary gridlines or chart titles to keep it sleek.

Watch out:
Excel can sometimes leave gaps if there are missing data points. Check your table for blanks.

Pro tip:
Wisesheets automatically ensures complete datasets, without any holes or surprises.

For more control over your historical data setup, here’s a step-by-step guide to getting historical stock data in Excel.

4. Pro Tips for Power Users

Want to go beyond the basics?

Here are a few tricks to make your chart smarter:

Add a Moving Average Line:

• Right-click on the Close data series.
• Select Add Trendline > Moving Average.
• Choose your period (e.g., 10-day, 50-day).

Note: Excel doesn’t let you add a moving average directly to a standard candlestick chart. If you want to overlay one, you’ll need to plot the Close prices separately as a line chart and then apply a moving average to that.

Adding Volume:

Excel doesn’t natively support candlestick charts with volume.

So, you'll need to add volume as a secondary axis using a separate bar chart.

It’s a bit of a workaround and takes some manual adjustments.

With Wisesheets, pulling volume data is as simple as using the =WISE formula, saving you hours of manual scrubbing.

5. Common Troubleshooting Tips

If your candlestick chart isn’t showing up right, here’s what to check:

• Wrong data order: Excel demands Date, Open, High, Low, Close – in that order.
• Incorrect data format: Make sure your dates are true dates, not text.
• Zero or blank data points: They’ll throw off the chart.
• Using the wrong chart type: Double-check you picked Open-High-Low-Close, not High-Low-Close.

Quick Fix:
If the chart looks weird, delete it and try again after double-checking your data format. It’s faster than endlessly tweaking settings.

Bonus:
Using Wisesheets can help you skip most of these problems by pulling in clean and structured data directly into your spreadsheet.

When and Why to Use Candlestick Charts in Your Analysis

Candlestick charts are built for situations where you want more than just where the price was.

They show you how the price moved, how hard the bulls or bears pushed, and where the battle lines were drawn.

When Candlestick Charts Beat Line and Bar Charts

• Line charts are fine when you only care about the closing price over time. They’re simple, clean, and good for high-level trends.
  
• Bar charts give you more detail (open, high, low, close) but they’re clunky to read and don’t pop visually.
  
• Candlesticks give you the same data as bar charts but make it faster to read at a glance. Up candles, down candles, long wicks – these tell you the story of the day, hour, or minute.
  If you care about momentum, reversals, or spotting trader psychology on the chart, candlesticks win.

Patterns Worth Knowing (Even if You Don’t Trade Full-Time)

You don’t need to be a technical analysis junkie to pick up on the basic candlestick signals.

Here are a few bread-and-butter patterns that even Excel charts can help you spot:

• Doji: Price opened and closed at almost the same spot. Signals indecision, or a pause before the next big move.
  
• Hammer: Small body, long lower wick. Shows sellers tried to take over but got slapped down. Often hints at a reversal to the upside.
  
• Shooting Star: Opposite of the hammer. Long upper wick, small body near the bottom. Shows bulls ran out of gas.
  
  You can learn more about this reversal signal in our full breakdown of the shooting star candlestick pattern.
  
• Engulfing Patterns (Bullish or Bearish): Big candle fully swallows the previous one. Shows a sudden power shift between buyers and sellers.

There are dozens more, but start with these. Even just spotting a hammer at a support level can save you from walking into a bad trade.

Frequently Asked Questions (FAQs) About Excel Candlestick Charts

Can you make Excel candlestick charts without stock data?

Yes, technically.

As long as you've got the Open, High, Low, Close data, it doesn’t matter if it’s stocks, crypto, or potato prices.

But most people use them for stocks or trading data.

---

Can Excel candlestick charts show volume?

Not out of the box, no.

Excel’s candlestick charts don’t have a built-in option for volume overlay.

You’ll have to hack it by adding volume as a separate column chart on a secondary axis.

It works, but it’s a bit fiddly.

A powerful shortcut is to use Wisesheets to pull volume data alongside OHLC, then plot it in Excel.

---

How do I add moving averages to an Excel candlestick chart?

Easy.

Right-click on your Close series, pick 'Add Trendline', and select 'Moving Average'.

You can choose the period (like 20-day or 50-day).

Just remember – it’ll only work on the Close prices, not the candles themselves.

---

Can I create real-time candlestick charts in Excel?

Kind of.

If you’re using plain Excel, no. You'll need to manually refresh your data or paste in new numbers.

But if you're using Wisesheets, you can pull in live or latest OHLC data with formulas and set your chart to update automatically when the data refreshes. It’s not tick-by-tick live like trading platforms, but it’s close enough for most DIY setups.

---

Conclusion: The Candles are Lit – What's Next is Up To You

So now you’ve got the candles on the chart.

The data’s no longer just sitting there, flat and silent. It’s telling you a story.

Where the buyers stepped in. Where the sellers slammed the brakes. Where the fight got ugly.

You’ve seen how to pull it off in Excel. From setting up the data (the right way), to plotting, cleaning, tweaking, and even layering on those extra touches like moving averages.

You’ve also seen how tools like Wisesheets eliminate the manual work by pulling clean, real, usable data into your sheets without the copy-paste circus.

But all of that is still just the setup.

The real edge comes when you start reading the candles, spotting the signals, making moves others miss because they’re stuck staring at line charts.

Excel gives you the canvas.

Wisesheets gives you the brush.

What you paint on it – that’s all you.

Read the Market Heat Faster with Wisesheets