Excel Chart Formatting

[MSIsam]

I have a line chart with month on the x-axis and a Percent Metric on the y-axis. I would like to keep the x-axis static with 12 months (Jan 2006 to Dec 2006). I have data for the first 6 months and do not want the graph to plot the months without data (July - Dec). Basically, there would be a trend going half way across the chart

In addition, I would like a 13th column that has the Year-to-Date (YTD) Percentage as a stand alone data point on the chart.

The only thing I can do right now is to only show the months with data and manually delete the line that connects to the YTD percentage. I need to be able to create about 50 of these charts and update them on a monthly basis and was wondering if there is a way to make this less labor intensive.

Thanks and I can send file of what I have so far for clarification if needed.

 

[MSIsam]

I just saw the FAQ that shows how to not display zero values so that part is solved (apologies for not seeing that earlier).
I am now left with the challenge of finding an automated way to delete the line connecting the trend to the "YTD" data point. If it helps, the constant is that the YTD will always correspond to the 13th value in the x-axis.

 

[ThomasLafferty]

Hi! If I read you correctly, you want to consider the 13th data point separately. If this is the case, when charting it, first select the previous 12 points and their labels, then hold down you control key and select the 13 point and its label.

=Sheet1!$A$1:$L$2[!],[/!]Sheet1!$M$1:$M$2

The comma in the reference causes it to be evaluated as a separate range. Had the control key not been held down, you would have gotten:

=Sheet1!$A$1:$M$2

Hope this helps.

Tom

Born once die twice; born twice die once.

 

[MSIsam]

Hi Thomas,
Thanks for your reply,
I tried what you suggested, putting in

='Monthly PTP'!$AB$4:$AN$10,'Monthly PTP'!$AO$4:$AO$10

Unfortunately, the result is the same and it connects June 2006 to the YTD datapoint.

 

[ThomasLafferty]

Hi! Here's a link to what I think your chart looks like:

http://www.geocities.com/thomas_e_lafferty/ChartImage.jpg

Let me know if I'm on track.

Tom

Born once die twice; born twice die once.

 

[ThomasLafferty]

Oops!
Try this link...

http://www.geocities.com/thomas_e_lafferty/index.html

Tom

Born once die twice; born twice die once.

 

[CautionMP]

MSIsam,
Instead of using the built-in trendline are you in a situation where you can calculate your own trendline points then add this as a series to your chart?

CMP

[small]For the best results do what I'm thinking, not what I'm saying.[/small]
(GMT-07:00) Mountain Time (US & Canada)

 

[MSIsam]

Hi Thomas,
I was able to remove the lines connecting the zero values by using a formula to evalutate the cell to #NA if the value is zero. My chart look like yours but it connects June to the YTD.

Sam

 

[ThomasLafferty]

So it actually looks more like this:

http://www.geocities.com/thomas_e_lafferty/index.html?1157998781788

Have you tried Caution's suggestion?

Tom

Born once die twice; born twice die once.

 

[MSIsam]

Correct Thomas. That's what I have.
I dont' want to have separtate series for each of the YTD points since I have a data table as well. I am working ona macro that seems to be working at first run.

Sub RemoveYtdLine()
    ActiveSheet.ChartObjects("Chart1").Activate
    ActiveChart.SeriesCollection(2).Select
    ActiveChart.SeriesCollection(2).Points(13).Select
    With Selection.Border
        .LineStyle = xlNone
    End With
    ActiveChart.SeriesCollection(3).Select
    ActiveChart.SeriesCollection(3).Points(13).Select
    With Selection.Border
        .LineStyle = xlNone
    End With
    ActiveChart.SeriesCollection(4).Select
    ActiveChart.SeriesCollection(4).Points(13).Select
    With Selection.Border
        .LineStyle = xlNone
    End With
    ActiveChart.SeriesCollection(5).Select
    ActiveChart.SeriesCollection(5).Points(13).Select
    With Selection.Border
        .LineStyle = xlNone
    End With
    ActiveChart.SeriesCollection(6).Select
    ActiveChart.SeriesCollection(6).Points(13).Select
    With Selection.Border
        .LineStyle = xlNone
    End With
 End Sub

Hopefully I am on the right track...

Sam

 

[ThomasLafferty]

Looks like your on track. Let me know.

Tom

Born once die twice; born twice die once.