We help IT Professionals succeed at work.

on
Medium Priority
340 Views
I have a chart with dates (X) and values (Y). I need to add a vertical line that would indicate the date entered in cell A1. For example, if I have a chart with some data for the entire month November, and the date in A1 is 11/3/11, the line line would be on Nov 3rd. If I change the date, the line would move.
Comment
Watch Question

View Solution Only

Commented:
the vertical bar (|) is above the enter. shift + \ and you will get |.

Commented:
for example:
="11/3"& "|"

Commented:
here is what I am looking for ( the red line on the chart)
chart.jpg
Top Expert 2011

Commented:
You need to add a new x-y scatter series with 2 points, with connected lines.  One point needs to be x=11/3/2011 and y=0, and one x=11/3/2011 (same as the other) and y=1200.  If you're not happy doing this I can post an example.
Top Expert 2011
Commented:
Here's an example set up as I described vert-bar.xlsx

Commented:
how do I add a new scatter?

Commented:
got it! Thanks for the example!
Top Expert 2011

Commented:
1. Set up your line chart as usual
2. Create the range starting in D5, with the date on which you want the line in the the first column and the low and high values in the second.
3. Right click on the chart and take Select data..
4. Click to Add new series, and in the dialog box select E5 thru E6 as the Series values (the high and low values)
5. OK to clear the Select data box.  The chart will be screwed up but don't panic
6. Right click the new series on the chart and pick "Change series data type"
7. Change it to X-Y scatter with straight lines
8.  Return to the Select data dialog, select the new series and Click Edit
9. Now you will have an X Values box - click in this and select D5 thru D6, making the x values the dates you entered
10. With luck that should do it.

Sounds scary but it's OK once you done it a few (hundred) times.  If you have Excel 2007, under no circumstances choose x-y scatter with smooth lines, as this performs like a dog.
Top Expert 2011

Commented:
I wish I'd refreshed before writing that novel...