Changing Trend Line Options on Your Excel Chart


In the previous article, Creating a Trend Line on Your Excel Chart, you could see how easy it is to add a trend line onto a data series. But, you need to add the correct type of trend line that uses the correct regression analysis. And you need to be able to identify whether the trend line is a good fit that has little error.

Control Trend Line Options

Excel can add six different types of trend lines to a chart. It’s up to you to choose which type is most appropriate for your data.

If you want more control over trend line options, then select the data series, and on the Layout tab, in the Analysis group, click Trendline, then select More Trendline Options. The Format Trendline dialog box displays.

From the More Trendline Options you can control which trend line is applied.

From the More Trendline Options you can control which trend line is applied.

Use the Format Trendline dialog box to select different types of trend lines and to show the R squared value.

The six types are,

Linear – a best-fit straight line where data increases or decreases with consistent change.

Logarithmic – a best-fit curved line where data increases quickly and then levels out.

Polynomial – a curved line where data may have more than one curve. For example, a third-order polynomial has two curves. (Remember the first time you saw that equation and curve in algebra class?)

Power – a curved line where the curve increases or accelerates at a consistent rate, for example, an apple dropping from a tree accelerated by gravity. Power trend lines cannot contain zero or negative values.

Exponential – a curved line that increase or decreases at an increasing rate. For example, rabbits over populating Australia without any predators.

Moving average – a trend line that smooths erratic data by averaging over two or more past periods. If you choose the More Trendline Options you can select the number of periods used for smoothing.
If you want some examples of these different choices, checkout this Microsoft article.


Next > Next issue Oct 21, 2014

Previous > Creating a Simple Trend Line

Start  > Excel Trends Tutorial – Creating Simple and Dynamic Trends on Charts