Vba add trendline to chart in Excel

For example, I have a Chart ("Chart1") for Sales data of the last 6 quarters - I would like to get a 2-Quarters moving average trendline added to the chart.

excel vba add trendline to chart

To do it in Excel, here is the answer:

  1. Option Explicit
  2. Sub AddTrendLine()
  3. ActiveSheet.ChartObjects("Chart1").Chart.SeriesCollection(1).Trendlines.Add
  4. ActiveSheet.ChartObjects("Chart1").Chart.SeriesCollection(1).Trendlines(1).Select
  5. With Selection
  6. .Type = xlMovingAvg
  7. .Period = 2
  8. End With
  9. End Sub


a) Line 3 adds a Trendline to the Series Collection specified.

b) Line 6 specifies the Type of Trendline (could be xlExponential, xlLinear, xlLogarithmic,xlMovingAvg,xlPolynomial or xlPower)

c) Line 7 specifies the Peiod for moving average calculation.

Result after Macro execution:

excel vba add trendline to chart


You can find similar Excel Questions and Answer hereunder

1) How to print a worksheet in Excel VBA

2) Line break in vba message box in Excel

3) How can I turn off Alerts using VBA?

4) How can I add interval to dates?

5) How can I extract file name from a full path including folder path and file name?

6) How can I delete all shapes in a WorkSheet?

7) How do I use Find to determine last occurrence of a string in a WorkSheet range using VBA?

8) How can I activate a routine when there is a change in value of a cell?

9) How to do workbook protection with VBA in Excel

10) How to do worksheet protection with VBA in Excel


Here the previous and next chapter