Candlestick Charts in Excel: How to Build and Read Them

A candlestick chart packs four numbers — the open, high, low, and close for each period — into a single shape you can scan at a glance. That’s its whole appeal: one glance tells you the range a price covered and whether it finished the period up or down. Excel has this chart type built in, and building one takes about a minute once you know the one rule that makes or breaks it.

That rule trips up almost everyone the first time, so we’ll get it right from the start. This guide builds the chart step by step, then explains how to actually read the candles — what each shape is telling you about that period’s price movement, described plainly and without pretending a chart can predict anything.

Educational content only — not financial advice. This article explains how to build a chart in Excel and how to interpret the shapes it produces. It is not investment advice and does not recommend buying, selling, or holding anything. Candlestick shapes describe what a price did in the past; they do not forecast the future, and this guide deliberately avoids framing any pattern as a signal to act on. For financial decisions, consult a licensed professional.

What a candlestick chart is

A candlestick chart — Excel calls it Open-High-Low-Close, or OHLC — is a hybrid of a line chart and a bar chart. Each period (a day, a week, whatever your data covers) is one candle, and that candle encodes four prices at once:

• The real body (the filled box) spans the open and the close.
• The wicks (the thin vertical lines above and below the body, sometimes called shadows) reach up to the high and down to the low.
• The fill color shows direction: one color when the close is above the open (the price rose over the period), another when the close is below the open (it fell).

So a single candle tells you four things instantly: where the period opened, where it closed, how high it reached, and how low it dropped. String a few hundred together and you get a compact picture of price movement over time. That density is why candlesticks are the default in technical analysis of stocks, currencies, and other securities — and it’s also why they’re overkill for most everyday data. If you’re just showing a trend over time, a line chart is clearer; the chart chooser guide covers when each type earns its place.

Step 1: Lay out the data — in the exact order Excel demands

Here is the rule that breaks more candlestick charts than everything else combined: the columns must be in the order Date, Open, High, Low, Close. In that sequence. No other order works.

Most Excel charts are forgiving — pick the wrong columns and you re-map them in a dialog box. Stock charts are not. They read columns by position, so if your data is out of order, you get an error, not a fixable chart. You have to be right from the start.

So set up five columns, left to right:

So set up five columns, left to right:

The Date column comes first. Then Open, High, Low, Close in that precise order. If your source data has them in a different arrangement — Close before Open is common — reorder the columns before you do anything else. Don’t leave stray columns (like Volume or a ticker name) sitting between them, either; that also breaks the chart.

If you’re pulling this data with a formula rather than typing it, the STOCKHISTORY method lets you force exactly this column order using its property arguments, and the chart then refreshes on its own.

Step 2: Insert the chart

Select the whole range, header row included. Then:

1. Click the Insert tab.
2. In the Charts group, click the Insert Waterfall, Funnel, Stock, Surface, or Radar Chart button.
3. In the dropdown, under the Stock heading, choose Open-High-Low-Close.

Excel draws the candlestick chart. Each row of your data becomes one candle: the vertical line marks the low-to-high range, the box marks the open-to-close range, and the color marks direction.

If Excel throws an error instead of a chart, it’s the column order almost every time. Go back to Step 1, confirm the sequence is Date, Open, High, Low, Close with nothing extra wedged in between, and try again.

Step 3: Clean it up

The default chart is functional but rough. Three quick fixes:

Change the up/down colors. Excel often defaults to red and green candles. Skip that. Red-green color blindness is common enough that a red/green chart becomes unreadable for a real chunk of your audience — the two colors merge into one. Use blue for up candles and orange for down instead. They read clearly for almost everyone and they’re already in Excel’s palette. Click one candle to select all the up (or down) candles, right-click, choose Format Data Series, and set the fill color. Repeat for the other direction.

Delete the legend. The auto legend just says “Open High Low Close” and clutters the plot. Click it, delete it.

Fix the crowded date axis. With daily data over a few months, the date labels overlap into mush. Right-click the horizontal axis, choose Format Axis, and set a larger interval between labels so only every fifth or tenth date shows.

Add a title, and you’ve got a clean, readable candlestick chart.

How to read the candles

This is where candlesticks earn their reputation. Once you know the anatomy, each shape tells a small story about what happened during that period. A quick, important caveat before the shapes: what follows describes what a candle’s form indicates about that period’s price action. It does not tell you what will happen next, and nothing here is a cue to trade. Read it as visual literacy, not as strategy.

The real body — how far open and close were apart. A tall body means the open and close were far apart: the price moved decisively in one direction over the period and finished well away from where it started. A short body means open and close landed close together: the period ended roughly where it began, regardless of what happened in between.

The color — which way the period finished. In the blue/orange scheme, a blue body means the close was above the open (an up period) and an orange body means the close was below the open (a down period). That’s all color encodes: the net direction from open to close.

The wicks — how far the price reached and retreated. A long upper wick means the price pushed well above where it eventually closed — it reached a high, then came back down before the period ended. A long lower wick means the price dropped well below the close before recovering. Short wicks or none mean the open and close were near the period’s extremes; the high and low were close to where trading started and stopped.

A body that’s nearly a flat line. When the open and close are almost equal, the body shrinks to a thin horizontal line with wicks on either side. This tells you the period covered a range but ended almost exactly where it opened — a lot of movement, no net change. In candlestick vocabulary this shape has a name (a “doji”), but the name matters less than what it shows: indecision within that period, buyers and sellers finishing even.

The value of reading candles is descriptive. A long lower wick tells you the price fell and recovered during that period — a factual account of what the four numbers were. It does not tell you what the next candle will do. Any tutorial that jumps from “here’s a shape” to “so you should buy” has left description behind and entered forecasting, which a chart cannot actually do. Keep the two separate.

A worked example

Say one candle in your chart is blue, with a tall body, a short upper wick, and almost no lower wick. Read it piece by piece:

• Blue — the price closed higher than it opened. An up period, net.
• Tall body — open and close were far apart, so the move was decisive rather than a wobble around the starting price.
• Short upper wick — the price didn’t push much above where it closed; the high was near the close.
• Almost no lower wick — the low was near the open, so the price barely dipped below where it started.

Put together, those four facts say: the period opened near its low, climbed steadily, and closed near its high, without giving much back. That’s the entire story the candle contains — a plain summary of the four numbers. It is a description of one period, and it stops there. What you don’t get, and what the shape genuinely cannot give you, is any claim about the next candle. The candle is a photograph of what happened, not a forecast of what’s coming.

Contrast that with a candle that has a small body sitting on top of a long lower wick. That one says: the price dropped well below the open during the period but recovered to close near where it started. Same descriptive discipline — you’re reading the four numbers off the shape, not divining intent from it.

Once you can do this fluently, the whole chart becomes scannable: you’re reading a few hundred of these little summaries at a glance, building a picture of how the range moved over time. That’s the skill worth having. The step people should not take is the leap from “this candle shows a recovery” to “therefore the price will rise” — that inference isn’t in the data.

Why not just use a line chart?

Honestly, most of the time you should. A line chart of closing prices is cleaner, easier to read at a glance, and perfectly adequate for showing a trend. Candlesticks earn their complexity only when the within-period detail matters — when you specifically care about the range a price covered and where it opened versus closed, not just its ending value.

If you find yourself building a candlestick chart to show a general trend to a non-technical audience, you’ve reached for the wrong tool. Switch to a line. Save the candlesticks for when the open-high-low-close detail is the actual point. For dense grids of values where you want to spot hot and cold zones rather than track a single series, a heat map is often the better fit, and the chart chooser walks through the decision for other data types.

Adding volume (and why you might not want to)

Excel offers a Volume-Open-High-Low-Close variant that stacks trading volume bars beneath the candles. It’s available in the same Stock chart dropdown, and it needs Volume as the first data column, ahead of the OHLC columns.

The honest take: these cluttered charts are hard to read unless you make them large and carefully scale the two sections so volume fills the bottom and prices fill the top. For most purposes the plain OHLC candlestick is the better choice — it’s cleaner and it shows the four prices that carry the most information. Add volume only if you have a specific reason to, and give it the room it needs.

Troubleshooting

The chart won’t build — Excel shows an error. Column order. It must be Date, Open, High, Low, Close, with no extra columns interrupting the sequence. This is the single most common cause and it’s rarely anything else.

The candles display but look wrong. Check that Open, High, Low, and Close are formatted as numbers, not text. A stray text value (a number with a leading space, or imported as text) will distort or blank out a candle.

Some candles are missing. Usually a blank or non-numeric value in one of the four price columns for that date. Fill or fix the gap.

I can’t tell up candles from down candles. That’s the red/green default doing its damage. Change the fills to blue and orange in Format Data Series.

My data updates but the chart doesn’t reflect it. If you’re on manually entered data, the chart follows the cells — confirm you actually changed the source range the chart points to. If you built the chart on a STOCKHISTORY formula, remember that data only refreshes after market close, roughly once per trading day.

FAQ

What’s the difference between OHLC and candlestick? In Excel, they’re effectively the same chart — the built-in type is literally called Open-High-Low-Close, and its candles are candlesticks. Elsewhere, “OHLC bar” sometimes refers to a tick-mark style (a vertical line with small marks for open and close) rather than a filled body, but Excel’s version gives you the filled-body candlestick.

What data do I need? Four price columns per period — Open, High, Low, Close — plus a Date column, arranged in that exact order.

Can I change the candle colors? Yes. Right-click a candle, choose Format Data Series, and set separate fills for up and down candles. Blue/orange is far more accessible than the default red/green.

Can I add a moving average or other indicator? You can add a line series over the candles, which turns it into a combo chart. Treat it as a way to visualize the data, not as a trading signal — this guide, and Excel, can show you the shapes but not what they mean for any decision.

Do candlestick patterns predict price movements? No. A candle describes what a price did during one period. It carries no reliable information about the next period, and this guide deliberately does not frame any shape as a forecast. Read candles as a record of what happened, nothing more.

Is a candlestick chart the right choice for my data? Only if the within-period detail (the range, and open versus close) genuinely matters. For showing a trend, a line chart is clearer. See the chart chooser guide.

---

This article is for educational purposes only and does not constitute financial, investment, or trading advice. Candlestick shapes describe historical price movements and are not predictive; past performance does not indicate future results. Nothing here recommends any security, product, or course of action. Consult a licensed financial professional before making investment decisions.