Using Excel’s TREND function to Create Trend Lines on Excel Charts or Dashboards

Excel

To see the trend data and to create dynamic trend lines you can control with menus you need to use Excel’s TREND function. Using the Trendline function in the Analysis group is an easy way to add a trend line to a chart, but it doesn’t show you the data for that line and it doesn’t allow you to create dynamic trend lines your users can control in Excel charts and dashboards. With the TREND function we can do all of that.

Using the TREND function we can calculate data, control display, and extend a forecast. It’s pretty easy to do. Start with the basic Excel chart from the first article. Download the file Dynamic Trend Charts – Basic.

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

Use the TREND function to create trend data, then add it to the Excel chart.

Use the TREND function to calculate the data of a trend line. This shows you the data in the worksheet so you can paste it into the chart and use other functions to control its appearance and behavior.

TREND is an array function. This is a function that uses a collection of data as input and produces a collection of data as an output. You must enter an array function with a special keystroke, which I’ll describe in just a bit.

You can see in the formula bar in the figure that after you’ve entered the TREND function as an array it appears in the formula bar differently from a normal formula. It appears with { } braces around it. This indicates it is an array and that one cell cannot be changed, all cells in the array must be changed simultaneously.

Excel has two functions it uses for calculating trends and filling the data points into a worksheet, TRENDS and GROWTH. Use TRENDS for linear or straight-line regressions. Learn more here,

http://office.microsoft.com/en-us/excel-help/trend-function-HP010062548.aspx

Use GROWTH to calculate exponential trends and fill data points in a worksheet. Learn more here,

http://office.microsoft.com/en-us/excel-help/growth-function-HP010342562.aspx

Both these functions are entered into worksheets in the same way, so when you learn how to calculate one function you also learn the other.

The TREND function is an array function. You must enter it into multiple cells all at one time, so follow these steps exactly.

1. Enter the title SL Trend, for straight line trend, in cell C24.

2. Select the cells where you want the TREND function results, D24:O24. The selection in which you enter TREND must match the width and height of the data used by TREND. Notice the selection in the figure is the same size and shape as the data cells above it. Array functions are entered into all cells of an array at one time so you must select them all prior to entering the function.

3. As you create the formula, with D24:24 select, it will appear as though you are only creating it in  cell D24,

=TREND(D23:O23,D22:O22)

The TREND function requires,

TREND(known y’s, known x’s)

In this example the known x’s are evenly spaced dates. In this case they are date values. The y’s are data values. All the cells from D24:O24 should still be selected.

4. Enter the array formula by pressing Shift+Ctrl+Enter. (Hold down Shift+Ctrl, then press Enter.) This enters the formula into all selected cells.

You’ll see the trend data in row 24 like in the figure above. But now you have to add the trend line data to the Excel chart.

1. Select cells C24:O24, right click and choose Copy.

2. Select the chart, right click and choose Paste.

The trend line will appear over the data line.

You can download the Excel workbook containing these basic TREND line charts here. Dynamic Trend Charts – Basic

Learn how to extend a TREND line to create a forecast.

Learn how to show or hide a TREND line under the check box control.

Learn how to select between different TREND lines with a drop-down menu.

Learn how to edit a TREND or array function.

Share the power...