Creating a combo Stock-Candlestick chart & Line Chart

Excel doesn't allow you a combo Stock chart & a Line chart.

There is a 2016 comment (and attached excel sheet) on this forum which seems to have managed to do it.

I am new here & hence unable to post a link to that comment but this is it - /excel-general/1166181-candle-stick-chart-with-line-chart.html#post5202257

You can prepend it with this forum's url to get to this comment.

I read his comments & also looked at the excel sheet he has attached, but I am not able to figure out how he managed it.

Are there more detailed steps on how to do this?

I have a stock chart - I have been drawing lines on it manually - multiple non-connected lines - these lines do not pass through or connect any of the points on the candle itself - it's based of other data which I would like to look at together with the candlestick chart.

Instead of drawing it manually, I want excel to do it.

See the yellow banner at the top of the page - share your workbook with us.

Hi Ali,

I think my account is too new to be allowed to upload stuff. Anyway, I think I have figured out the invisible part also (it's by making the transparency 100%).

I am away from my worksheet computer currently, but I will try this out later tonight & or tomorrow morning & then upload the final solution if I do have enough permissions to do that.

There are no restrictions on uploading attachments - which is what the instructions in the yellow banner, if you follow them correctly, will allow you to do. What you may not do yet is post a link, but that's not what I asked for, anyway. I would not ask you to do something I knew you could not do!

These are the steps for Excel 2016 to create a Combo Candlestick(Stock) + Line Chart which excel doesn't allow you to create. The trick is to create Combo Line Charts & then add High-Low Lines & Up-Down bars to make it look like a Candlestick chart.

1) Create a chart from the following data
Sheet1!$C$1:$G$11

2) The Chart should be of type Combo. Each Series should be of type Line Chart
3) For the Open, High, Low & Close, click on Secondary axis so that these 4 series are on the Secondary Axis
4) In the chart, using Format Axes, make the minimum & maximum of both primary & secondary axes the same
5) Add the following 2 Chart elements - High/Low Lines & Up-Down Bars
6) In the Chart, Click on the High Line, Right Click on Format Data Series & then make it's transparency 100%. This line will become invisible
7) Repeat (6) for the Low Line, the Open Line & the Close Line - so those lines also disappear.

You can change colors etc for the lines, Up Bars, Down Bars etc

In my attached charts, the Up bars are Green, the Down bars are Red. The line I drew using the 5th column is Black

You can add a line to a standard stock chart if you want.

Create the stock chart excluding the "Line" data series.
The use Select data to add a new series.
Delete secondary axis so the line will align to primary axis.

See attached for example of stock chart and stock chart + line

I can see in your xls that you have done it, but I am unable to do the last 2 steps correctly.

After creating the stock chart with Sheet1!$C$1:$F$11 (I get the same stock chart as you have), I click on the chart, right click on Select Data & then click on Add
Then add a new series with series name =Sheet1!$G$1 & series value =Sheet1!$G$2:$G$11

The moment, I do this, my chart changes into what I have shown below which is quite different?
Also how do I "Delete secondary axis so the line will align to primary axis." - if you look at my screenshot below it doesn't even have a secondary axis.

What am I doing wrong?

Once added you need then select the "Lines" series. It does not show as it is a non formatted line at the base of the floating columns.
Select the chart, use the Format tab on the ribbon. Within the first group use Current Selection drop down to select the series.
You can now format it to the secondary axis.
Select the secondary value axis and press delete

Worked. Thanks a million. This is so much easier than my way.

Just for my benefit (and also for anyone else), let me document the detailed steps after creation of the Stock Chart.

1) Add the Line Series through Select Data
2) Click on Format Tab. In the Combo choose the Line Series. This will highlight the points on the Line Series
3) Right Click on the highlighted points & Click on Format Data Series. Choose Secondary Axis. Also go to Fill & Line in the Format Data Series & choose Solid Line under Line.
4) Click Design Tab, Add Chart Element, Click on Axes, & uncheck the secondary axis.

This one has an extra 9 series.

Just add them using the Select Data > Add, as you did for the first extra series

No idea what you did but if I look, using the change chart type dialog, all the stock chart series are on the secondary axis as Lines.