Excel Made Easy

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

Description:

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

Excel Made Easy was founded in 2002. Excel Made Easy is a service to people looking to learn Microsoft Excel or any other spreadsheet SW on the market.

We endeavor to provide the best service and support and to respond quickly to your questions.

Excel Made Easy is a proud sponsor of Dismantle-It.com the site that shows you the belly of your daily objects, Poupounette.com the site of cars loving cat and PowerPointMadeEasy.com the world top reference for PowerPoint Presentations questions and Pet Cemetery Online a site dedicated to your loving Pets.