How to plot Stock Chart and Bar graphs (for volume) instead of Line graphs on same chart?

Hello. I've been attempting to plot volume (as columns on a secondary axis) below a Open-Hi-Low-Close stock chart without success. The best I'm able to do is plot volume as a line graph. Can anyone tell me how I can plot volume in a column rather than a line? Attached is a abbreviated sample sheet. I'm using Excel 365 under Win7.

If I right click on the chart and select Format Plot Area and then select "Volume" under the Plot Area Options drop down menu, all the options are for Line graphs. Right clicking on the chart and choosing Change Chart Type > All Charts I see this chart is a Combo > Custom Combination. Under "Choose the chart type and axis for your data series" All my series are blank for their chart type. For the series "Volume" it's blank and the Secondary Axis is checked. If I try to change the Chart Type for Volume, it doesn't seem to take. I can change to "Clustered Column" and it doesn't change anything.

Can anyone tell me how I can change my Volume plot from a line to a column? Thanks for any help!

MrShorty - thanks for the detailed reply! I think both my problems I'm having with Excel charts stem from using their built-in Stock charts although the strange thing is that I think after a lot of messing around with it I've lost some of the stock features and my Excel file seems to be messed up. For example, when I go to Change Chart Type my chart is a Combo type, not a Stock type although it did start as a Stock type and I've no idea how that got changed.

Anyway, I'm trying to follow your directions for creating my own stock type chart using another sample file and I'm having problems at step 5. I don't see "Open data series" to add error bars. I can only go to Chart Tools > Add Chart Element > Error Bars which doesn't give me an option of doing custom value and indicating a data range as you suggest. I'm using Excel 365 if that matters?

Thanks again for the help. I worked through most of your suggestions on how to make my own stock charts but now I'm stuck on getting that moving average to plot nicely with the Up/Down bars. I did figure out some additions/changes to your instructions too. I'm not even worrying about adding the volume yet as making a line + column with a secondary axis should be the easy part so I'll just save that for last. Here's what I learned on how to make a stock chart from scratch:

1. Make a simple line chart with just the Open and Close values and the date.
2. Click on the chart to activate Chart Tools > Add Chart Elements > Add Up/Down Bars
3. Very Important! Right Click in the chart and choose Format Plot Area, Click on the "Open" series and choose "No Line" to get rid of the line and only leave the Up/Down bars.
4. Do the same for the "Close" series.
5. Click on the chart to activate Chart Tools > Add Chart Elements > Add Error Bars. Choose any type because you'll be changing it.
6. Make a new column in your data (example: Data!AE2 and enter the formula =ABS($B2-C2) where B is the Open & C is the High & D is the Low and Copy/paste/fill into AE2:AF30.
7. So now AE contains the Positive Error ($B-C Open-High) and AF contains the Negative Error ($B-D Open-Low), both calculated off the Open price.
8. Right Click in the chart and choose Format Plot Area, Click on the series "Open" Y Error Bars, Click on the 3 bar icon under Error Bar Options, under Error Amount choose Custom and click Specify Value and input your Positive and Negative Error range (columns AE and AF in our example). Click on the Paint Bucket Icon, under Line choose Automatic, under the 3 column icon > Vertical Error Bar choose Both Directions.
9. Right Click in the chart and choose Format Plot Area, Click on the series "Close" Y Error Bars, Click on the paint bucket icon under Error Bar Options, Choose No Line under Line because the error bars are calculated from the Open price and not the Close price. This is important because if you don't choose No Line you'll have 2 sets of Error Bars.

The above gives me a nice Open-High-Low-Close chart. Now I'm having problems adding in the Moving Average line. Mr.Shorty suggested:

7) While here, enter the formula for the moving average trendline. In Data!AG10 (9th row down) =AVERAGE(D2:D10) (note the relative references). Copy/paste/fill into AG10:AG30.
8) Select AG2:AG30 -> Copy -> Select the Chart -> paste and the moving average trendline is added. Note that Excel decides to move the up down bars. It appears that Excel wants to connect the first and last line chart series with the up down bars, so change the order of the data series (edit the last argument of the SERIES() "formula" or change the order in the select data dialog) so that the moving average series is between the open and close series. The up down bars should move back to being between the open and close series.

The critical statement is here: "It appears that Excel wants to connect the first and last line chart series with the up down bars," The problem is, sometimes the MA contains values that are not simply between the High/Low daily price, often the MA values are below or above, so no matter how I position the order of my series in the Select Data dialog if my MA is outside my high/low bounds the up/down bars want to incorporate it. I've searched the internet but don't find any way to make a Up/Down bar just for a select 2 line series and have other line series (the MAs) be separated from the Up/Down pair?

MrShorty - Thanks for attaching your sample file - using it I finally got mine working too. After much studying I realized you only had an Open & Close data series in your chart while I actually had 4 - Open, High, Low & Close - although I had High & Low unchecked (thus not displayed), they still existed. So when we both created a MA and changed the order in the select data dialog to put the MA between the open & close, I actually had a High & Low series in there between the open & close as well. Once I deleted the High & Low (since we don't really need them as your method uses the error bars in their place) and placed the MA between the Open & Close it worked and I got the same results as you do.

I still don't understand why that select data dialog order makes it work or why having a High & Low in there messes it up, but thanks for all the help! It's much appreciated!