Marking the current month on a line graph

[ackiss]

Excel 2003

I have a very simple Line graph. The series of data displays historic information up to the current month, and budgeted data in the future. I would like to insert a vertical line or some other type of marker to indicated where the actuals end and the projections begin.

My Data looks essentially like this

1 100,000
2 150,000
3 125,000
4 200,000
5 250,000

If the current month is 3, then 4 and 5 are budgeted, not actual. I want the graph viewer to know immediatley what is real, and what is budget by some sort of visual cue.

Any ideas?

 

[Andrew Poulsom]

Here is how to add a vertical line:

http://peltiertech.com/Excel/Charts/ComboCharts.html#AddLine

Or you could set out your data like this:

1 100,000      #N/A
2 150,000      #N/A 
3 125,000      #N/A 
4      #N/A 200,000 
5      #N/A 250,000

and plot both ranges with different colours. You can use the NA() function to return #N/A.

 

[Juan Pablo González]

Let's assume some data like this:

Book1
	A	B	C	D
2	Month	Data
3	1	$100,000.00
4	2	$150,000.00
5	3	$125,000.00
6	4	$200,000.00
7	5	$250,000.00
8	6	$225,000.00
9	7	$180,000.00
10	8	$160,000.00
11	9	$150,000.00
12	10	$230,000.00
13	11	$280,000.00
14	12	$240,000.00
Sheet1

Now, we'll insert a couple of extra columns, to help with the chart. Like this:

Book1
	A	B	C	D
2	Month	Data	CurrentData	ForeCast
3	1	$100,000.00	$100,000.00	#N/A
4	2	$150,000.00	$150,000.00	#N/A
5	3	$125,000.00	$125,000.00	#N/A
6	4	$200,000.00	$200,000.00	#N/A
7	5	$250,000.00	$250,000.00	#N/A
8	6	$225,000.00	#N/A	$225,000.00
9	7	$180,000.00	#N/A	$180,000.00
10	8	$160,000.00	#N/A	$160,000.00
11	9	$150,000.00	#N/A	$150,000.00
12	10	$230,000.00	#N/A	$230,000.00
13	11	$280,000.00	#N/A	$280,000.00
14	12	$240,000.00	#N/A	$240,000.00
Sheet1

The formula in C3 is

=IF($A3<=$F$1,$B3,NA())

and in D3 is

=IF($A3<=$F$1,NA(),$B3)

Now, in F1 I have this formula

=MONTH(TODAY())

or you can override it with the month number that you want.

Now, to create the chart do the following:

1. Select A2:A14, press the Control key, and select C2:D14
2. Click on the Chart wizard icon
3. Select 'Column' and 'Stacked column' (the second subtype)
4. Click next. In step 2, remove the 'Month' data, and change the 'Category X axis labels' for the other two series, to reflect the correct range (A3:A14)
5. Click on Finish.

You can then customize the chart.

You can change the