Predict data trends

You can extend a series of values that fit either a simple linear trend or an exponential growth trend by using the fill handle.

Use the following procedures to learn how to display and predict trends and create a projection.

Create a trend series from your data

Create a linear best-fit trend series

In a linear trend series, the step value — the difference between the starting value and the next value in the series — is added to the starting value and then added to each subsequent value.

1. Select at least two cells that contain the starting values for the trend.

   To increase the accuracy of the trend series, select more than two starting values.

2. Drag the fill handle in the direction of either increasing or decreasing values.

   For example, if you selected cells C1:E1 and the starting values in these cells are 3, 5, and 8, drag the fill handle to the right to fill with increasing trend values, or drag it to the left to fill with decreasing values.

   Tip: To manually control how the series is created, on the Edit menu, point to Fill, and then click Series.

Create an exponential growth trend series

In a growth trend series, the starting value is multiplied by the step value to get the next value in the series. The resulting product and each subsequent product is then multiplied by the step value.

1. Select at least two cells that contain the starting values for the trend.

   To increase the accuracy of the trend series, select more than two starting values.

2. Hold down CONTROL and drag the fill handle in the direction that you want to fill with increasing or decreasing values.

   For example, if you selected cells C1:E1 and the starting values in these cells are 3, 5, and 8, drag the fill handle to the right to fill with increasing trend values, or drag it to the left to fill with decreasing values.

3. Release CONTROL , and the mouse button, and then on the contextual menu, click Growth Trend.

   Excel automatically calculates the growth trend and continues the series in the selected cells.

   Tip: To manually control how the series is created, on the Edit menu, point to Fill, and then click Series.

Display the trend series by using a trendline in a chart

2016 2011



You can use trendline and moving average in two-dimensional charts to graphically display trends and analyze problems of prediction (also known as regression analysis). A moving average smooths out fluctuations in data and shows the pattern or trend more clearly. By using regression analysis, which estimates the relationship between variables, you can extend a trendline in a chart beyond the actual data to predict future values.

1. On your chart, click the data series to which you want to add a trendline or moving average.

2. On the Chart Design tab, click Add Chart Element, and point to Trendline.

   Note:  You can add a trendline only to unstacked, 2-D, area, bar, column, line, stock, xy (scatter), or bubble charts.

3. Click the kind of trendline or moving average that you want to use.

4. Click OK.

   Note: If you add a moving average to an xy (scatter) chart, the moving average is based on the order of the x values plotted in the chart. To get the result that you want, you might have to sort the x values before you add a moving average.

You can use trendline and moving average in two-dimensional charts to graphically display trends and analyze problems of prediction (also known as regression analysis). A moving average smooths out fluctuations in data and shows the pattern or trend more clearly. By using regression analysis, which estimates the relationship between variables, you can extend a trendline in a chart beyond the actual data to predict future values.

1. On your chart, click the data series to which you want to add a trendline or moving average.

2. On the Chart menu, click Add Trendline, and then click Type.

   Note:  You can add a trendline only to unstacked, 2-D, area, bar, column, line, stock, xy (scatter), or bubble charts.

3. Click the kind of trendline or moving average that you want to use.

4. Do one of the following:

   If you select	Do this
   Polynomial	In the Order box, enter the number for the highest power that you want to use for the independent variable.
   Moving Average	In the Period box, enter the number of periods that you want to use for calculating the moving average.

5. Click OK.

   Note: If you add a moving average to an xy (scatter) chart, the moving average is based on the order of the x values plotted in the chart. To get the result that you want, you might have to sort the x values before you add a moving average.

See also

FORECAST function

TREND function

GROWTH function

LINEST function

LOGEST function