# Suppressing part of a graphical trendline

#### DavidB

##### Active Member
Good morning

We have a 13 month time series of data and want to show the linear trend of the most recent 6 months only on the graph.

How do we suppress the trend line going over the first 7 months data?"

Thanks DavidB

### Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

#### jc113883

##### Board Regular
I don't think the trendline can be supressed for sections of data.

Easiest way would be to plot another series on the chart, of only the last 6 months worth of data and put a trendline on that. You can then hide the data points so they can't be seen. You can delete the entry in the legend if required.

#### DavidB

##### Active Member
Thanks jc113883, you comments set me down a path to a solution. I learnt a few interesting things along the way.

1) Trendlines can be extrapolated beyond the left and right end of the data but can not be shrunk shorter than the data series. (We wanted the trendline to represent and show only the last 6 month's data.)

2) The NA() function can be used to suppress values from displaying on the chart.

Essentially, what I did was:

1) Used the COUNTA() function to determine the total number of datapoints and hence the location of the last data points.

2) Used the LINEST() and OFFSET() functions to determine the slope and intercept parameters for the trendline describing the last 6 datapoints.

3) From the LINEST() function I generated best fit data points for the six last data values and plotted these values. Plotting outside the range of the last 6 data points was suppressed using the IF() and NA() functions. These datapoints I charted.

Regards David

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Threads
1,168,093
Messages
5,857,318
Members
431,871
Latest member
Hakunabrotata

### 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.
Allow Ads at MrExcel

### Disable AdBlock

Follow these easy steps to disable AdBlock

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

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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