Trouble creating vertical trendline on clustered bar chart in Excel 2016

[ExcelLearner25]

I have 2 columns of data in Excel 2016. Col. A is a list of states and Col. B is a percentage. I've successfully made a clustered bar chart (vertically oriented list of bars) with A as the Y axis and B as the X axis. Now I'd like to add a vertical line showing the median. I've been trying to follow the instructions on this page in the section entitled "How to add vertical line to Excel bar chart" but I'm stuck on Step 6.

Step 6 says "The new data series is now added to your bar chart (two orange bars). Right click it and pick Change Series Chart Type in the pop-up menu." But I can only select one of the 2 new bars at a time, not both as shown in the image. After that, selecting only 1 bar, Step 7 says to choose "Scatter with Straight Lines" for my new series, but that option isn't available (maybe because I only selected one of the 2 bars?).

Any suggestions on what I'm doing wrong? Or, alternatively, is there another way to do this?

 

[SlinkRN]

Hi ExcelLearner,
What you can do is add another column (column C) and put the formula =median($B$2:$B50) in every row for that column. Then create your chart with all 3 columns included. Now you can click on that one column that is to the right of your state column, and then right click and select Change Series Chart Type - Line. That should give you what you're looking for.
Another option is to use the Insert -> Shapes and choose a straight line and draw it across where you want it. (That's an easy cheat but will not move around with your chart.)
Hope this helps! Slink

 

[ExcelLearner25]

I'm afraid that didn't do it, or maybe I misunderstood. This is the chart I'm trying to recreate (copied from a PDF)

This is the closest I can get, but I'd like to have it rotated 90 degrees:

 

[ExcelLearner25]

Update - I managed to do it by changing the orientation of the axis labels and rotating the graph 90 degrees in MS Paint. But I'd still like to know if there's a way to do it in Excel.

 

[Jon Peltier]

Not too tricky, actually.

Below are bar chart data, bar chart, and helper series data, which has the median repeated in the first column and the values 0 and 1 in the second.

Copy the range with the helper data (3 rows x 2 columns), select the chart, and choose Paste Special from the Paste dropdown on the Home tab. Select New Series, Series Names in First Row, Categories in First Column, and click OK. The result is the first chart below.

Right-click on either series and select Change Series Chart Type. In the dialog, change the Median series to an XY Scatter type (second chart below). Add a data label if desired.

Format the new series as desired (thinner line, dashed if desired, no markers, etc.). Format the right-hand vertical axis so it's minimum and maximum are 0 and 1, and change the labels from Next to Axis to None (third chart below).