Trouble creating vertical trendline on clustered bar chart in Excel 2016

ExcelLearner25

New Member
Joined
Jan 20, 2023
Messages
8
Office Version
  1. 2016
Platform
  1. Windows
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?
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
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
 
Upvote 0
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)

1698073506863.png


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

1698073438269.png
 
Upvote 0
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.
 
Upvote 0
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.

BarChartWithVerticalLine1.png


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).

BarChartWithVerticalLine2.png
 
Upvote 0
Solution

Forum statistics

Threads
1,224,395
Messages
6,178,348
Members
452,841
Latest member
GenAkaman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top