For business users of Microsoft Excel Free guides and templates

Windows & Mac Excel

Charley's Swipe File #52

Because Excel's Stock chart type provides little control over the chart format, this candle-stick chart uses Excel's XY (Scatter) Plot chart type.

by Charley Kyd, MBA
Microsoft Excel MVP, 2005-2014
The Father of Spreadsheet Dashboard Reports

Charley's Swipe File #52This chart ignores Excel’s built-in Stock Chart type, which offers less control than I like over chart formats. Instead, it relies on a useful charting technique I “invented” half a dozen years ago, and that I’ve never seen anyone else use.

So even if you don’t need a candle-stick chart like this, you really need to understand how I created it. This is because you’ll probably find other reasons to use a similar method, and it really needs to be in your bag of charting tricks.

The method combines both a decision and a technique.

First, I realized that using a column plot to create this chart would be very complex to do, and way too much work. Therefore, I decided to create the fat and skinny columns in this chart using an XY (Scatter) Plot, where the width of each “column” is determined by the width of its line segment. (I had to use an XY Plot because Line Plots can’t draw vertical lines in charts, and I needed to use those vertical lines to replace column plots.)

Then I needed to find a way to actually make my decision work.

Then I thought of the technique I invented several years ago for drawing time-series data. The technique would allow this chart to use only five data series for this chart: two greens, two reds, and a black. Without this technique, each of the many line segments in this chart would have required its own SERIES formula…and that looked like too much work.

Before you read my explanation of this technique, can you figure out how to turn one long line into the many segments used in the chart?

(Continued in the documentation.)

Order Details

Excel Versions:

Excel 2007 and after, for either Windows or Mac


Instant download of zipped Excel and PDF files. If you ever need another download, you can get it at any time.


One full year, unconditional.


All prices are in US Dollar currency.


Two copies: One for work plus one for home.

Credit Cards:

Credit Cards

Only $14.95

New Excel