Excel automatically add trend line to your chart

By Pichart Y.

Excel chart always be able to format or chage any properties then automatically applied to all series in that selected chart, but it is not easy like that to add trend line to the chart, you need to do series by series of chart. Here is the code help you to add trend line in just one click...

Let Excel perform this task for you using the code...

Sub AddMultiTrendLine()

'suppose that the Chart series are in row1, then we want to add trend line to all chart series

SeriesCnt = Application.WorksheetFunction.CountA(Range("1:1"))

For i = 1 To SeriesCnt
    ActiveSheet.ChartObjects("Chart 1").Activate
    
    ActiveChart.SeriesCollection(i).Select
    SelColor = ActiveChart.SeriesCollection(i).Border.ColorIndex

    ActiveChart.SeriesCollection(i).Trendlines.Add(Type:=xlPolynomial, Order:=3 _
    , Forward:=0, Backward:=0, DisplayEquation:=False, DisplayRSquared:= _
        False).Select
    ActiveChart.SeriesCollection(i).Trendlines(1).Select
    With Selection.Border
        .ColorIndex = SelColor
        .Weight = xlThin
        .LineStyle = xlContinuous
    End With
    
    Next i
End Sub

Excel automatically add trend line to your chart  (2274 Views)