Overlay Moving Average on Stock Chart Type Starting with First Bar

Hello,
Excel 2010
Chart Type: Stock - Candlestick
Data:
A1:A100 - Open
B1:B100 - High
C1:C100 - Low
D1:D100 - Close
E41:E100 - 40 Bar Moving Average of Column D - Close

I've created a "Stock/Candlestick" chart using A50:D100.

My goal is to overlay the moving average line on top of the candlestick chart AND to have the moving average start on bar 1.

Option 1: I've attempted to use the built in moving average feature in the "trendline" tool but it won't begin plotting until the 41st bar. Is there a way to calculate the moving average using data that isn't plotted on the chart so it can begin on bar 1?

Option 2: I've manually calculated the moving average already, the data is in E41:E100. I've attempted to overlay column E as a "line" but it says that I can't combine a stock and line chart type. Is there another way to overlay data in column E onto a stock chart?

Thanks much.

Thank you Andy. That works perfectly. I've tried to replicate what you did on my chart but I still get the errors. No worries, I was able to copy your chart and modify from there. I'll use this as a template going forward.

Thanks much!