# Marking the current month on a line graph

#### ackiss

##### New Member
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?

### Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

#### Andrew Poulsom

##### MrExcel MVP
Here is how to add a vertical line:

Or you could set out your data like this:

Code:
``````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

##### MrExcel MVP
Let's assume some data like this:
Book1
ABCD
2MonthData
31\$100,000.00
42\$150,000.00
53\$125,000.00
64\$200,000.00
75\$250,000.00
86\$225,000.00
97\$180,000.00
108\$160,000.00
119\$150,000.00
1210\$230,000.00
1311\$280,000.00
1412\$240,000.00
Sheet1

Now, we'll insert a couple of extra columns, to help with the chart. Like this:
Book1
ABCD
2MonthDataCurrentDataForeCast
31\$100,000.00\$100,000.00#N/A
42\$150,000.00\$150,000.00#N/A
53\$125,000.00\$125,000.00#N/A
64\$200,000.00\$200,000.00#N/A
75\$250,000.00\$250,000.00#N/A
86\$225,000.00#N/A\$225,000.00
97\$180,000.00#N/A\$180,000.00
108\$160,000.00#N/A\$160,000.00
119\$150,000.00#N/A\$150,000.00
1210\$230,000.00#N/A\$230,000.00
1311\$280,000.00#N/A\$280,000.00
1412\$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

Replies
3
Views
1K
Replies
0
Views
380
Replies
3
Views
752
Replies
3
Views
1K
Replies
5
Views
254

1,195,623
Messages
6,010,749
Members
441,567
Latest member
Flitbee

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

### Which adblocker are you using?

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

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