Free Microsoft Excel 2013 Quick Reference

[Solved] Charts: Add a vertical line, the x-intercept of whi

I have a chart with two lines, each representing a mutually-exclusive alternative. At the intersection of the lines is the point at which one should be ambivalent between the choices. For the purposes of this post, call this point the breakeven.

I'd like to insert a vertical line at the breakeven point. Depending on the variables involved, the breakeven can change. How can I drop a vertical line from the breakeven to the x-axis and--come to think of it--over to the y-axis?

Sample spreadsheet attached.


Post your answer or comment

comments powered by Disqus
I want to add a vertical line to a chart to call out a specific date. I have
two lines and am using 2 Y axis due to the difference in values of the two
data series. For a given X value I want a vertical line to appear on the
chart to make it clear that data to the right of the line is AFTER a certain
thing was done. How do I do this?

Hi All,

I need to add a vertical line to a column / line chart combo where the
secondary axis is already in use.

I have ready through John Peltier's site
(http://peltiertech.com/Excel/Charts/ComboCharts.html) and
specifically the two options he has under the 'Special Effects and
Features' section for adding vertical lines to a chart, but both
require the use of a secondary axis which is then hidden. That is not
an option for me as I am already using that secondary axis.

I am currently using a line object drawn over the top of the chart
object and manually positioned on the chart as per this image:

http://img143.imageshack.us/img143/5...celcombfm9.png

Is there any way to programmatically determine the location that I
would need to put the line object to have it divide between two points
on the x-axis (say, between Nov 2006 and Dec 2006), or to add it to
the chart object itself without losing what is already there?

Thanks,

Alan.
--

The views expressed are my own, and not those of my employer or anyone
else associated with me.

My current valid email address is:

I'm trying to add a vertical line that would indicate the current
month, i.e. May 2007, to a graph with a Line and a Stacked Area, and
months (e.g. January 2004 through December 2007) instead of days on
the x-axis. I've tried different variations of
http://peltiertech.com/Excel/Charts/...ertSeries.html, without
success. Any suggestions?

Hi,

I have created a simple bar chart with target prices on the x-axis and names of firms on the y-axis.

I now want the following:
(1) to add a vertical line showing the average price and another vertical lines showing the maximum price.
(2) align the data labels in such a way that are aligned at the right side of the graph (they are now located just outside the bars)

any help is much appreciated ...

thanks very much in advance

How do I add a vertical date line to a horizontal bar chart?
--
DMM

Your assistance here will help me with a problem at work. Lets assume that I have a normal chart with a vertical line, y-axis at (0,0), where 0,0 represents the origin of the graph. The x-axis runs through 0,0 also. In reality, the charts origin can be positive or negative, but for clarity here I use 0,0. I would like to have four distinct vertical lines. Each additional line would represent a value on the x-axis. For example, I could have -3,0 and -4,0 and 11,0 and 15,0. The x-values would appear in the top row of the spread sheet as in A1, B1, C1, D1. As the numbers in these cells change, so does the appearance of the vertical line move to new location. One final note, the numbers on the x-axis can be any real number. That is, positive, negative or zero. I hope my description is adequate. Your collective assistance will help a beleaguered engineer who wants to see a picture of his data, not just the numbers. Thanks much in advance.

Tony

So the question at hand, is how do I add a vertical line into these graphs that will show me today. The point for this is to be able to know where today is on these graphs, so that i can see what has already occurred and what is coming up.

I have been trying to do the second x-axis strategy but it has not paned out for me. Any thoughts?

I attached the file for viewing.

Thanks!

Hey folks,

I hope someone can help me. I need this issue fixed for a presentation. Let me make a example. There were surveys about the average age of young people which are going into clubs. Germany was 23,4 years, Italy were 24,4 years, Franch was 19,0 years etc. The average European average is 23 years. I would like to use a bar-chart where the country average is shown. For the European average there shall be a vertical line which goes through the whole chart.

Can someone help? Thanks in advance

Hello -

I am attempting to add a series on the xlSecondary axis of a PivotChart of BarStacked type. Please note that the data for the other series is not contained within the underlying PivotTable/ PivotCache. However, I am having difficulty in getting it to work. Attached is the relevant code:

    Dim cobj As Chart 
Set cobj = Me ' "Me" holds a reference to the relevant PivotTable chart

' Added explicit additions, just to be sure
With cobj
.HasAxis(xlValue, xlPrimary) = True
.HasAxis(xlValue, xlSecondary) = True
.HasAxis(xlCategory, xlPrimary) = True
.HasAxis(xlCategory, xlSecondary) = True
End With

Dim rngMilestones As Range ' Holds a Range object with the new series data (date/description pairs)
Dim marker_series As Series

Set marker_series = cobj.SeriesCollection.Add(Source:=rngMilestones, Rowcol:=xlRows, SeriesLabels:=True, CategoryLabels:=False, Replace:=True)
marker_series.AxisGroup = xlSecondary
marker_series.ChartType = xlLine

' Clean up
Set marker_series = Nothing
Set cobj = Nothing



All of the PivotChart data lies on the xlPrimary axis. I receive a run-time error #1004: Unable to get the Add property of the SeriesCollection class. Little help as to how one can add a secondary axis to a PivotChart using VBA? Please let me know if additional detail regarding the chart characteristics are needed.

Thanks!

If you build a line chart connected to a bunch of x/y values
Is there a way to draw a vertical line from a point on the chart line to the
bottom of the chart?

Do you need to layer another chart on top?
Can you add the vertical line right on the same chart?

thanks for any help

Each of my charts has about 60 days along the Y axis, and I would like the
senior manager to be able to see a dotted line that goes from the Y axis
(always from today's date) to the data point that represents today's value.

Is it possible to add a vertical series? Or to graph or draw a vertical line
that will "walk" along the Y axis?

Thanks!
John

I have been trying to add a Vertical line across my chart with no luck. This is the reference link I am using:

http://peltiertech.com/Excel/Charts/...ertSeries.html

I am adding a sample excel document of my date and the chart I have so far. I need to draw a Vertical line across from the present month, which I am not able to do.

Any help on getting this done is highly appreciated.

Thanks,
booo

Hi Guys,
I am working with line charts and i am successively drawing line charts based on the number of rows. Each row contains one line chart and every time chart is drawn on new worksheet (e.g., if there are 5 rows then on single click 5 line charts will be drawn).
The problem that i am facing is, How to put column headings on the x-axis of each chart.
NOTE: Row heading is already appearing on the y-axis of each chart.
I have also attached my excel sheet...Here is my code:

	VB:
	
 LineCharts() 
    Dim Ws As Worksheet 
    Dim NewWs As Worksheet 
    Dim cht As Chart 
    Dim LastRow As Long 
    Dim CurrRow As Long 
     
    Set Ws = ThisWorkbook.Worksheets("Sheet1") 
     
    LastRow = Ws.Range("A65536").End(xlUp).Row 
    For CurrRow = 2 To LastRow 
        Set NewWs = ThisWorkbook.Worksheets.Add 
        NewWs.Name = Ws.Range("A" & CurrRow).Value 
        Set cht = ThisWorkbook.Charts.Add 
        With cht 
            .ChartType = xlLine 
            .SeriesCollection.NewSeries 
             
            .SeriesCollection(1).Values = "=" & Ws.Name & "!R" & CurrRow & "C3:R" & CurrRow & "C8" 
            .SeriesCollection(1).Name = "=" & Ws.Name & "!R" & CurrRow & "C2" 
            .Location Where:=xlLocationAsObject, Name:=NewWs.Name 
        End With 
    Next CurrRow 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


Hi,

another question from my side...

I still have a stock chart (plotted on primary axis) together with a
count (plotted on the secondary axis).
(these values vary strongly, so both axis are needed)

Now I want to add a vertical line to the chart.
I've added another data series, changed the type of the series to "xy
(scatter)" and got a vertical line by using :
Name: Vertical
X Values: ={11}
Y Values: ={01}

This gives me a small vertical line. However it doesn't fill the
complete plot area.
Of course, when setting the second Y-value to the maximum with setting
the scale to this maximum, it would fill the plot area.
But this is undesirable, because I absolutely have no idea how big my
values for my stock chart will become. So the maximum for the scale
should stay "auto".

Is there a way to extend the vertical line to the whole plot area with
leaving the scale auto?
(like it works for horizontal lines with error bars and big error
values)

Thanks in advance,
Rene

I have a chart that summarizes survey responses:
- Y axis is the percent of total respondents
- X axis is the Likert scale responses, "Strongly Disagree (1)", thru
"Strongly Agree (7)".

I want to add a vertical bar that shows the average response (which the
chart would take from the spreadsheet).

For example, if the average was "5.5", I want the bar to be vertical, and
for it to cross the X axis where 5.5 would fall, between the "Somewhat Agree
(5)" and "Agree (6)" bars, as if the X axis were truly a numerical plotting
(as opposed to the text responses).

I suspect it is a secondary X axis without displaying the scale, but I
cannot seem to make it work. Help! Thanks VERY much!

Hi Guys,
I am working with line charts and i am successively drawing line charts based on the number of rows. Each row contains one line chart and every time chart is drawn on new worksheet (e.g., if there are 5 rows then on single click 5 line charts will be drawn).
The problem that i am facing is, How to put column headings on the x-axis of each chart.
NOTE: Row heading is already appearing on the y-axis of each chart.
I have also attached my excel sheet for better understanding of my problem.....I want to display DP1, DP2, DP3,... so on on the X-axis of each line graph/chart. Here is my code:

Sub LineCharts()
Dim Ws As Worksheet
Dim NewWs As Worksheet
Dim cht As Chart
Dim LastRow As Long
Dim CurrRow As Long

Set Ws = ThisWorkbook.Worksheets("Sheet1")

LastRow = Ws.Range("A65536").End(xlUp).Row
For CurrRow = 2 To LastRow
Set NewWs = ThisWorkbook.Worksheets.Add
NewWs.Name = Ws.Range("A" & CurrRow).Value
Set cht = ThisWorkbook.Charts.Add
With cht
.ChartType = xlLine
.SeriesCollection.NewSeries

.SeriesCollection(1).Values = "=" & Ws.Name & "!R" & CurrRow & "C3:R" & CurrRow & "C8"
.SeriesCollection(1).Name = "=" & Ws.Name & "!R" & CurrRow & "C2"
.Location Where:=xlLocationAsObject, Name:=NewWs.Name
End With
Next CurrRow
End Sub

Hi,

another question from my side...

I still have a stock chart (plotted on primary axis) together with a
count (plotted on the secondary axis).
(these values vary strongly, so both axis are needed)

Now I want to add a vertical line to the chart.
I've added another data series, changed the type of the series to "xy
(scatter)" and got a vertical line by using :
Name: Vertical
X Values: ={11}
Y Values: ={01}

This gives me a small vertical line. However it doesn't fill the
complete plot area.
Of course, when setting the second Y-value to the maximum with setting
the scale to this maximum, it would fill the plot area.
But this is undesirable, because I absolutely have no idea how big my
values for my stock chart will become. So the maximum for the scale
should stay "auto".

Is there a way to extend the vertical line to the whole plot area with
leaving the scale auto?
(like it works for horizontal lines with error bars and big error
values)

Thanks in advance,
Rene

I have created a custom bar chart that shows a series of bars across the page with a timeline as the X-axis, similar to a Gantt Chart. I need to add a vertical line indicating the progress to date. Every attempt I've made adds a horizontal bar or a rectangle covering everything left of the desired line. I believe I need to change the chart type of that particular series, but I can't seem to make it work. Any help would be appreciated.

I have a chart that summarizes survey responses:
- Y axis is the percent of total respondents
- X axis is the Likert scale responses, "Strongly Disagree (1)", thru
"Strongly Agree (7)".

I want to add a vertical bar that shows the average response (which the
chart would take from the spreadsheet).

For example, if the average was "5.5", I want the bar to be vertical, and
for it to cross the X axis where 5.5 would fall, between the "Somewhat Agree
(5)" and "Agree (6)" bars, as if the X axis were truly a numerical plotting
(as opposed to the text responses).

I suspect it is a secondary X axis without displaying the scale, but I
cannot seem to make it work. Help! Thanks VERY much!

I have been trying to add a Vertical line across my chart with no luck. This is the reference link I am using:

http://peltiertech.com/Excel/Charts/...ertSeries.html

I am adding a sample excel document of my date and the chart I have so far. I need to draw a Vertical line across from the present month, which I am not able to do.

Any help on getting this done is highly appreciated.

Thanks,
booo

Hi all,

I'm trying to enter a vertical line to my chart to show today's date. I'm using Excel 2003 and have a product serial number with 4 date variable's on the vertical axis with time scale along the horizontal axis.

I'm sure this is probably a very simple solution, but I haven't got my head round it yet.

Many thanks for the help.

Alex

edit - I have an attachment but forgot to attach it after posting. To clarify, the date variable's show the product's serial number manufacture date, 2 inspection dates and a final life limit date. All I want to show is where today's date is displayed across all the serial numbers. Many thanks.

Hi folks,

Picture, if you will, an Excel graph with 2 bell-curves plotted on
it...one curve shows the freqency distribution of measurement values at
"good," or reference, sites, and the other shows the distribution of
values at "bad," or impaired, sites. At some point on the graph the
curves intersect (hopefully at the inflection points, assuming a
more-or-less normal distribution for both populations of sites). The
"curves" are actually XY scatterplots with smoothed lines and no
markers, using data sorted into uniform "bins" generated by Tools | Data
Analysis | Histogram. Now, I can add vertical lines to the graph to
show the median (50th %ile) of each distribution using the technique at
Kelly O'Day's website
(http://processtrends.com/pg_charts_vertical_line.htm), because I "know"
(i.e. can calculate) the median value for each pop. But what I would
*really* like to do is add a vertical line to the chart at the
intersection point for these 2 curves. I have 21 of these charts (7
measures, 3 biological regions) and prefer not to have to eyeball all
those intersection points, then hard-code in the values for each graph
to make the intersection line. Is this even possible? Or let me phrase
it another way...is it possible without VB?

Using Excel 97, Win XP.

tia,

LeAnne

I have a chart that has 15 vertical items down the side, and measurements
across the bottom.

I want to add a horizontal line to the chart that will separate the first 5
items down the side to group them together to the eye. The same thing with
the second 5 items and third 5 items.

Each of these is a "group" that falls into a category that I want to show on
the chart. The first 5 items down the side fall under the group
"professional", the second 5 items fall under a group called "Manager", etc.

Is there any way to add a horizontal line that will make these appear
together, and off to the right add the word "Professional", etc. to the first
five items of the empty area of the chart?

I'm probably not explaining myself very well. Any help would be appreciated!

I have successfully placed a vertical line on my column chart using the
series method, however, the vertical line is placed on a secondard scale
which is not the same scale as my primary series. This causes the vertical
line to be misplaced. How can I fix this?


No luck finding an answer? You could always try Google.