Creating a Simple Trend Line on Your Excel Chart

Analysis

This first method of creating a trend line will add a trend line to a chart without showing any trend data in the worksheet. It’s a good method for getting a general idea about the trend, but I usually prefer to see the trend data and its statistics in the worksheet. In the following tutorials you’ll see how to calculate trend data, show or hide trend lines in charts, and select the trend type from drop-down.

Applying a trend line to an Excel chart shows the line but you can't see data values or give the dashboard user control.

Applying a trend line to an Excel chart shows the line but you can’t see data values or give the dashboard user control.

This Excel chart has a straight line trend applied. Notice there is no trend data in the worksheet.

Trend lines can be added to unstacked , 2D, area, bar, column, line, stock, XY (scatter plots), or bubble charts. You can have more than one trend line in a chart. If you are creating a trend line on a chart and there are multiple sets of data and you’ve haven’t selected one, Excel will prompt you to select the data you want to trend.

1. Click on the data set in the chart you want to apply a trend to.

Tip: If you select the chart and it has more than one data series, Excel displays an Add Trendline dialog box. In this dialog box select the data series you want a trend on and click Ok.

2. On the Layout tab, in the Analysis group, click Trendline, and select the type of trend line you want.

Use this basic Excel data throughout the trend line tutorial.

Select different types of trend lines to apply to Excel charts.

Select the type of trend line you want on the data series. There are different types of trend lines for you to select from. Choose the one that best fits your type of data.

Linear Trendline
The linear trend line calculates a straight line that where the trend changes at a steady rate. This method fits a straight line to the data with the least amount of error; hence the name of least squares fit regression. R-squared is a measure of “goodness of fit”. Look for R-squared values greater than .7.

<Link on R-squared>
Exponential Trendline
The exponential trend line is a curved line where data changes, rising or falling, at increasingly larger rates. This happens in the growth of unrestrained biological populations and networks. Over optimistic entrepreneurs use an exponential trend line to show how they will dominate the world in just three years. Using an exponential trend line is appropriate if the R-squared value is near 1 and no barriers are foreseen in the near future, just don’t get over enthusiastic about forecasts. Checkout the logarithmic trend line option to see a trend line that occurs when growth reaches its limits.

Linear Forecast Trendline
Choosing this option creates a straight line trend line and extends it for two additional periods. Use More Trendline Options to extend this forecast.

Two Period Moving Average
Moving averages smooth erratic data by averaging the data over periods of time. This enables you to see patterns that might be obscured by erratic data. This choice averages data over two periods. If you choose More Trendline Options you can set the number of periods you want averaged over.

To learn about additional trend line options and which trend line is most appropriate for your data, check the article Changing Trendline Options on Your Excel Chart.

Share the power...