Knowledge base KB0188

How can I create a linear trendline in a line chart?

To create a trendline in a line chart, add a new series to the line chart, then calculate its values to form a trendline, e.g., by using the TREND function of Excel:

1. Open the internal datasheet and add a new series, e.g., "Trendline 1".

2. Calculate the first value of the trendline using the TREND function:

1. type "`=TREND(`" or use the Insert Function (fx) menu in Excel.

2. Select all "known y" values and press F4 (e.g., "\$B\$3:\$D\$3").
Enter Excel's arguments separator, e.g., "," (comma).
(Note that the character expected by Excel as an arguments separator depends on your Windows regional settings. To see the correct character for your region refer to the Excel tooltip appearing while you enter the formula).

3. Select all "known x" values and press F4 (e.g., "\$B\$1:\$D\$1").
Enter Excel's arguments separator.

4. Select the first x value (e.g., "B1") and press ENTER.

Note: By pressing F4 the cell reference type of the selected cells will change to absolute cell references. This way the selection will stay exactly the same when this formula is copied and will not be adjusted according to the new position.

3. Select the first value of the new trendline and copy the function by using Excel's Auto Fill feature, dragging the fill handle in the cell's lower right corner to the right until the cell of the last value is selected as well.

Note: If you receive a #VALUE! error, check if the "known x" and/or "known y" values were auto-filled incorrectly - you may have used relative references when setting up the formula. In this case change the cell reference type of the "known x" and "known y" values as described in step 2, using F4. Repeat step 3 afterwards.

think-cell uses cookies to improve the functionality, performance and security of this site. Your consent is necessary if you want to use the full functionality of this site. More information on the use of cookies by think-cell, your consent and your privacy rights can be found in our privacy policy.