# Trendline - Excel Chart



## feathead (Apr 5, 2002)

I know how to add a trend line to a chart I've created in Excel. Is it possible to choose the data for the trendline so I can exclude one data point? I have a series of data and I don't want the trendline to include the first data point. Thanks.


----------



## kiwiguy (Aug 17, 2003)

My suggestion would be treate a second data series on the chart that excludes the first data point.

Set the color of the second data the same as the chart background, create the trendline on that series. The trendline will be able to be visible but the data it pertains to will be unseen.

As the trendline pertains only to the true series it represents, I know of no other way.


----------



## feathead (Apr 5, 2002)

Tried your suggestion and it didn't come out as hoped. Tried two ways. Created a second series as suggested. Excluded the first data point (first row). When I did that and created the trendline using that series the chart shifted the data set one data point to the left so that the titles were no longer correct for that data series. So, then created the second data series including the first row only with a null value in the first postion. When I then added the trendline it extended to the first data point (first row, null value). So unfortunately it appears there is no way to do this to get the desired result. It was a good idea but didn't pan out. Thanks for the help.


----------



## sbarlage07 (Aug 21, 2004)

i'm not an expert at this but I hope this is what you want to do. I'm not sure if this will work easily 100% of the time but it should work easily when you have low numbers like 1,2,3... on your x axis.

Create the chart with all the numbers. Then create a second chart with all the numbers but the one you want to exclude (don't worry about labeling and all those options as you can just delete this chart when you're done). Add the trendline to the second chart and when you do this, go to the options tab and check to display equation on chart. Click OK. If this is a really important graph and you need high accuracy, I would right click the equation>format data labels>Number tab and select Number and add a lot of decimal places. OK. Highlight the number in the equation after the + sign. Copy this and go to make your trendline in your first graph. Under the options tab, check "Set intercept =" and paste the number in the box. Click OK and your trendline should be how you want it. Hope this is what you want!


----------



## feathead (Apr 5, 2002)

Tried this and it appears to work with the exception that the trend line extends back to the first data point (the one I don't want included). Looks like the line is not using the data point but that the trend line automatically extends to the beginning data cell as well as the ending data cell. Thanks for the help. If you know how to eliminate that anomaly let me know. Sure seems like you should be able to eliminate a beginning data point more easily than this but maybe it's not that common a request. Thanks again.


----------



## corbins (Jun 26, 2008)

Feathead, Did you ever find a way to exclude same data points for your trend line? I'm trying to exclude several data points. If anyone knows how to do this please let me know.


----------



## jimr381 (Jul 20, 2007)

Welcome to the forum.

Have you tried adding a second data series and plotting it along a second axis? If you attach your dataset I will see what I can do with it.


----------



## corbins (Jun 26, 2008)

Thank you. See attached. What I would like to do is add a trendline for 2nd Qtr 2006 to the end.


----------



## jimr381 (Jul 20, 2007)

I am about to head out home, but am working on the file. I will post something for you in the morning.


----------



## jimr381 (Jul 20, 2007)

This is what the OP (Original Poster) had asked for.


----------



## corbins (Jun 26, 2008)

That's it. How did you do that? Thank you!!!!


----------



## jimr381 (Jul 20, 2007)

Stacked one chart on top of another and got rid of a few things like the axis labels and the background of the second chart.


----------



## corbins (Jun 26, 2008)

I'm sorry, I don't know how to do that. I assume I make two charts and somehow combine them.


----------



## jimr381 (Jul 20, 2007)

You make a second chart that holds the values that you want and add a trendline to that. You then changed the background fill of it to none and double click on the axis labels and set those to none as well. If you click on the chart you will see the two charts in there. I then resized it to fit and it was all completed.


----------



## corbins (Jun 26, 2008)

I almost have it however, I have a white line between my two graphs. See attached Chart 4.


----------



## valis (Sep 24, 2004)

jimr381 said:


> You make a second chart that holds the values that you want and add a trendline to that. You then changed the background fill of it to none and double click on the axis labels and set those to none as well. If you click on the chart you will see the two charts in there. I then resized it to fit and it was all completed.


dude, that was pretty slick.....:up:

got a super duper all complex baseball chart, and was curious how to dump the first month of the season as far as winning % goes, and that did the trick.

nice, and thanks.

v


----------



## jimr381 (Jul 20, 2007)

corbins said:


> I almost have it however, I have a white line between my two graphs. See attached Chart 4.


You needed to set the chart area's background to none and take off the border around the plot area.



valis said:


> dude, that was pretty slick.....:up:
> 
> got a super duper all complex baseball chart, and was curious how to dump the first month of the season as far as winning % goes, and that did the trick.
> 
> ...


Not a problem, always happy to help when I can.


----------



## corbins (Jun 26, 2008)

Sorry, I'm not finding the "area's background" to change to none. I can find "Format Plot Area" and that is set to none. And I assume the area's background on the second chart.


----------



## jimr381 (Jul 20, 2007)

It is called "Chart Area." Select it from the drop-down list and click on the "Format Chart Area" button on the toolbar. You should see options for formatting the area or background of the chart the lines for the chart area.


----------



## slurpee55 (Oct 20, 2004)

A minor thing here Jim - I looked at the data and your graph shows Q1, 2006 as having a value of $291,000.00 but the actual value for that period is $274,900.00. The value of $291,000.00 belongs to the Q2, 2006.


----------



## jimr381 (Jul 20, 2007)

Aye the OP or original poster was requesting Q2 values. It is easy enough to fix though.


----------



## slurpee55 (Oct 20, 2004)

Yeah - I was just excited to catch you making a mistake!


----------



## jimr381 (Jul 20, 2007)

I happens once in awhile.  I actually had to open the application for this one. A lot of times I answer posts without opening the applications which is bad and good. I have been teaching so long, that I have a majority of the application's menus memorized.


----------



## corbins (Jun 26, 2008)

Thank you so much for all your help. Worked out great. If anyone knows of a better charting program where I could trend data like this easy, please let me know. For now, this works!!:up:


----------

