In OpenOffice I'm getting trend-line and average-line properties by RightClick. But in Excel 2007 I see only trend-line. Same with Excel 2010. How do you make an average-line in Excel?

  • 4
    Make one yourself from an extra column in your spreadsheet;-)
    – trolle3000
    May 26 '10 at 19:29
  • I can... but I need this occasionally for different columns. Maybe there is any "two-click" shortcuts...
    – stim
    May 26 '10 at 20:59
  • 1
    There is no existing short-cut. You will have to write your own macro.
    – Geoffrey
    Jul 13 '10 at 13:47

BTW, I've got desired macros:

Sub averageline()

' Adds line of average value for the selected series

Dim s As Series, v, m As Double, v1, i As Long
On Error GoTo err_selection
Set s = Selection
On Error GoTo 0
v = s.Values
m = WorksheetFunction.Average(v)
v1 = v
For i = LBound(v) To UBound(v)
    v1(i) = m
With ActiveChart.SeriesCollection.NewSeries
    .XValues = s.XValues
    .Values = v1
    .Name = "Average " & s.Name
    .AxisGroup = s.AxisGroup
    .MarkerStyle = xlNone
    .Border.Color = s.Border.Color
End With

Exit Sub

    MsgBox "Selection don't represent series on the chart", vbCritical
    Exit Sub
End Sub

After adding it, you'll be able to simply select any row on your graph, then alt+f8, select macros and enter.


I only have Excel 2003, but I think it should be the same.

I created a line chart then right-clicked on one of the data lines. The first item on the right-click menu was "Format Data Series..." On that right-click menu is "Add Trendline"

This will give you a bunch of options for the type of trendline. From the Options tab, you can have it forecast forward and backward as well.

  • 1
    I need average - exact value, not the upward or downward trend. I can't see value on trend line.
    – stim
    May 26 '10 at 21:04
  • What do you mean by "you can't see value on trend line"? If you want to see the equation of the trendline, that's an option when you customize the trendline. If you mean you can't see the use of a trendline, it's so that you can see the best-fit line for the dataset.
    – nhinkle
    Jul 21 '10 at 5:51
  • 1
    No. Trendline just tell us the trend. Average line is a simple horizontal line with average value.
    – stim
    Mar 21 '12 at 18:16

Assuming an already existing XY Scatter chart:

  1. Add a column to your spreadsheet where the chart data resides. In this column, for every row enter the average. You will have calculated this average beforehand using AVERAGE() or SUM(data range)/(number of rows). You should now have a column that has the same value for every cell. You can title this Average, or something relevant to your needs.

  2. In your chart, add another data series. This new series will be of the same type as your existing data line. Everything about this new series will be the same as the existing data series, except you will now use this new column as the range instead of the range you used for the data series (you'll probably also want to use a different title). Most likely you are changing the range used for the Series Y values (assuming a horizontal line showing the average). The point I'm trying to convey here is that the only difference between your data line and your average line will be that the average line uses the average column you just created instead of values used to create the original chart. This will draw a straight horizontal (no slope) average line.

  3. Format the line to your tastes.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.