How to Create a Candlestick Chart in Excel: Step by Step

To create a candlestick chart in Excel, arrange each trading period in columns ordered Date, Open, High, Low, Close, select the full table, and choose Insert > Insert Waterfall, Funnel, Stock, Surface or Radar Chart > Open-High-Low-Close. Excel uses those four price values to draw each candle, so accurate data and the correct column order are essential. The guide below is for Microsoft Excel for Microsoft 365 on Windows.

A candlestick chart displays how a price moved during each day, week, or other period. The candle body compares the opening and closing prices; the thin lines, called wicks or shadows, show the high and low. It is useful for viewing price movement at a glance, but it does not calculate or verify market data for you.

What You’ll Need

Excel for Microsoft 365 on Windows. Excel for Mac also supports stock charts, though ribbon labels and chart formatting controls can differ slightly. Excel for the web may not offer the same stock-chart creation and formatting options; use the desktop app if the stock chart command is unavailable.
A table with one row per trading period and four numeric price columns: Open, High, Low, and Close. Include a date or period column as the first column.
Price data with consistent units and intervals. For a daily chart, use one row per trading day; for a weekly chart, use one row per week.

Step-by-Step

Prepare a clean data table. Put the headers in the first row and the trading periods in the rows beneath. Use this order: Date, Open, High, Low, Close. Excel’s Open-High-Low-Close stock chart expects the four price series in that specific sequence. For example, a small table could look like this:

Date Open High Low Close

Jan 2 100 108 98 105

Jan 3 105 107 101 102

Use real Excel date values in the Date column, not text that only looks like a date. Price cells should contain numbers, not currency symbols typed as part of the value. You can apply a currency number format later. Remove blank rows, merged cells, subtotals, and notes from the selected range.
Check that each row is internally consistent. The High value should be at least as large as the Open and Close values, and the Low should be no greater than either. In the first sample row, the high of 108 is above both 100 and 105, while the low of 98 is below them. A row that violates these relationships can produce a misleading-looking candle or prevent Excel from plotting the stock chart properly. Also make sure every row uses the same price scale and period length.
Sort the periods in chronological order. Select the table and use Data > Sort if your records are not already ordered by date. Sort oldest to newest for the usual left-to-right timeline. If Excel treats dates as text, the sort may place them in an unexpected order; convert them to real dates before continuing. Keep each row intact when sorting, so a date stays paired with its own four prices.
Select the complete table, including headers. Click inside the data and press Ctrl+A if Excel correctly detects the contiguous table, or drag to select the date column, all four price columns, and the header row. Check the selection before inserting the chart. Selecting only the four prices can leave Excel without period labels, while including unrelated notes or blank columns can cause it to interpret the series incorrectly.
Insert the stock chart. On Windows, go to Insert > Insert Waterfall, Funnel, Stock, Surface or Radar Chart, then choose Open-High-Low-Close. The stock chart options appear in the Charts group on the Insert tab; the icon or group layout can vary with window width. Choose Open-High-Low-Close rather than High-Low-Close, because the latter does not include opening prices or draw the same open-and-close candle body. If the stock-chart choices are hidden, widen the Excel window or open the chart dropdown.
Confirm the chart is reading the data correctly. Excel should show a candle for each row and use the dates along the horizontal axis. If the chart looks wrong, select it and use Chart Design > Select Data to inspect the chart data range and series. You can also delete the chart, correct the source table, and insert it again. Do not try to fix incorrect source-column order just by changing colors; the data mapping must be right first.
Add a useful title and labels. Click the chart title and replace the default wording with a clear description, such as Daily closing and trading range. To add axis titles, select the chart, choose Chart Design > Add Chart Element > Axis Titles, and select the horizontal or vertical option. Label the horizontal axis with the period and the vertical axis with the price unit or currency, if that context is relevant. Avoid adding labels that imply a currency when the data uses another unit.
Format the chart for readability. Click the vertical axis to open Format Axis and adjust its number format or bounds if needed. A currency number format can make prices easier to read; choose one that matches the data. Use Chart Design > Add Chart Element for gridlines or a legend when they improve interpretation. The stock chart may not need a legend if its title and axis labels make the content clear. Keep the candle colors and background high-contrast, and avoid decorative effects that make small price differences difficult to see.
Resize and review the finished chart. Drag a corner handle to enlarge the chart so that the candles and date labels are legible. Compare a few candles with the source rows: the high and low wicks should span the listed extremes, and each body should reflect the relationship between open and close. Save the workbook after checking the labels, units, date order, and chart range.

Date	Open	High	Low	Close
Jan 2	100	108	98	105
Jan 3	105	107	101	102

On Excel for Mac, select the data and look for the Stock chart choices on the Insert tab; ribbon grouping and the chart-formatting pane can differ from Windows. If you are using Excel for the web and cannot find the stock-chart type, open the workbook in the desktop version rather than substituting a line chart, which does not show the same open-high-low-close information.

Common Mistakes

Using the wrong price-column order. Excel relies on the order Open, High, Low, Close after the date column. A table with Close before High, for example, can create incorrect candles even though all values are present. Reorder the source columns before inserting the chart.
Choosing High-Low-Close instead of Open-High-Low-Close. The first chart type omits the opening price, so it cannot display the full candle body showing open versus close. Select the Open-High-Low-Close option when you want a candlestick chart.
Mixing numbers and text. Values pasted with leading apostrophes, spaces, or text labels may not be recognized as numeric prices. Check a cell by selecting it and looking at the formula bar, then convert text values to numbers before charting. Consistent number formatting is fine; inconsistent underlying data types are not.
Including extra columns or a second header row. Excel may treat an unrelated volume column, blank column, or repeated heading as an additional series. Keep the selected range limited to the date and four price columns. If you want to chart volume too, use the appropriate Volume-Open-High-Low-Close stock-chart type and arrange its columns in the required order.
Assuming a chart proves the data is correct. A chart can render a typo or an incorrect date just as readily as valid data. Review the source and validate that each high and low contains the open and close before drawing conclusions from the visual.

Tips

Convert the source range into an Excel table with Ctrl+T before charting if you expect to add rows regularly. Check that My table has headers is selected. Tables make the data easier to maintain, although you should confirm the chart’s data range includes new records as expected.
Use a consistent date interval and a clear time zone or market convention when comparing periods. Missing trading days are normal for many markets; do not add artificial zero-price rows to fill weekends or holidays.
Keep source data on a separate worksheet from the finished chart when the workbook is becoming crowded. Give the data sheet and chart a descriptive name so another reader can trace the chart back to its inputs.
For a cleaner chart, reduce unnecessary gridlines and use a vertical axis scale that shows useful differences without hiding the overall range. Be cautious about setting a narrow minimum and maximum: it can make small moves look unusually dramatic.
When you refresh or replace the data, recheck the chart range and chronological order. A chart that excludes the newest row can look polished but still be out of date.

Troubleshooting

The stock chart option is unavailable. Check that you selected a rectangular range containing the date column followed by four numeric price columns, with one header row and no blank columns in the middle. Stock charts may be disabled or absent in some Excel web experiences. Open the file in Excel for Microsoft 365 desktop and try Insert again.

Excel says the chart type cannot be used. Inspect the selected range for text in the price columns, merged cells, extra series, and empty rows. Confirm the headings and values follow Date, Open, High, Low, Close. If the data was pasted from a website, convert date and price text into actual Excel values and retry.

The chart has no visible candles or the scale looks strange. Check that prices are numeric and that the vertical axis is not set to bounds that exclude the data. Right-click the vertical axis and choose Format Axis to review its bounds and number format. Also check for a stray value with a very different scale, such as a price entered in cents among values entered in dollars.

The dates are out of order or spaced oddly. Sort the source data by actual date values. If dates are stored as text, convert them to dates, then sort oldest to newest. To adjust how Excel treats the horizontal axis, select it and open Format Axis; available axis options depend on the data and Excel version. Do not sort only the date column, because that would separate dates from their matching prices.

The candle bodies or wicks do not match a row. Verify the stock chart series are assigned to the correct columns and that the table uses Open, High, Low, Close. Check the source row’s high and low against both opening and closing values. If the mapping remains wrong, use Chart Design > Select Data to review the range, or remove the chart and recreate it from a clean selection.

Frequently Asked Questions

What is the correct column order for an Excel candlestick chart?
Put the period or date first, followed by Open, High, Low, and Close. The order of the four price columns matters for the Open-High-Low-Close stock chart.

Does Excel call it a candlestick chart?
Excel groups it with stock charts and names the relevant option Open-High-Low-Close. That option draws the open-and-close candle body and the high-low range.

Can I make a candlestick chart without dates?
You can use period labels in the first column, but dates or clearly named periods help readers understand the timeline. Keep one label for each row and use the same interval throughout.

Can I include trading volume?
Yes. Arrange the data for the Volume-Open-High-Low-Close stock-chart type, with Volume before the four price columns, and select that chart option. Follow the column order expected by the specific stock-chart choice.

Why are my candles all the same color?
Check whether the data values really show different relationships between open and close, then select the chart and review its formatting options. Color conventions and default formatting can vary by Excel version or theme; do not infer price direction from color until you have checked what the chart is displaying.

Can Excel create a candlestick chart on a Mac?
Excel for Mac supports stock charts, though the Insert ribbon and formatting controls may be arranged differently. Select the same correctly ordered data and choose the Open-High-Low-Close stock chart option if it is available in your version.

Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.

More in Charts & pivot tables

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.