How to add a horizontal average line to chart in Excel?
In Excel, you may often create a chart to analyze the trend of the data. But sometimes, you need to add a simple horizontal line across the chart that represents the average line of the plotted data, so that you can see the average value of the data clearly and easily. In this case, how could you add a horizontal average line to a chart in Excel?
If you want to insert a horizontal average line to a chart, you can calculate the average of the data first, and then create the chart. Please do as this:
1. Calculate the average of the data with Average function, for example, in Average Column C2, type this formula: =Average($B$2:$B$8), and then drag this cell's AutoFill handle to the range as you need. See screenshot:
Note: Click to know more about applying the same formla to entire column, or applying the exact same formula/value to entire column without cell number incrementing.
2. And then select this range and choose one chart format that you want to insert, such as 2-D Column under the Insert tab. See screenshot:
3. And a chart has been created, please click one of the average data column (the red bar) in the chart, right click and select the Change Series Chart Type from the context menu. See screenshot:
4. In the popped out Change Chart Type dialog, click to highlight the Combo in the left bar, click box behind the Average, and then select the a line chart style from the drop down list. See screenshot:
5. Click the OK button. Now, you have a horizontal line representing the average in your chart, see screenshot:
Supposing you have created a column chart with your data in your worksheet, and the following VBA code also can help you to insert an average line across your chart.
1．Click one of the data column in your chart, and then all the data columns will be selected, see screenshot:
2. Hold down the ALT + F11 keys, and it opens the Microsoft Visual Basic for Applications window.
3. Click Insert > Module, and paste the following code in the Module Window.
VBA: add an average line to the chart
Sub AverageLine() 'Update 20130907 Dim ser As Series Dim arr As Variant Dim total As Double Dim outArr As Variant If VBA.TypeName(Application.Selection) <> "Series" Then Exit Sub Set ser = Application.Selection arr = ser.Values total = Application.WorksheetFunction.Average(arr) ReDim outArr(LBound(arr) To UBound(arr)) For i = LBound(outArr) To UBound(outArr) outArr(i) = total Next With ActiveChart.SeriesCollection.NewSeries .XValues = ser.XValues .Values = outArr .Name = "Average " & ser.Name .AxisGroup = ser.AxisGroup .MarkerStyle = xlNone .Border.Color = ser.Border.Color .ChartType = xlLine .Format.Line.ForeColor.ObjectThemeColor = msoThemeColorAccent6 End With End Sub
4. Then press F5 key to run this code, and a horizontal average line has been inserted into the column chart. See screenshot:
Note: This VBA only can run when the Column format you insert is 2-D Column.
You are guest
or post as a guest, but your post won't be published automatically.
- To post as a guest, your comment is unpublished.· 3 years agoI don't think the VB code works for pivot tables.. anyone know how to get it working for pivots?
- To post as a guest, your comment is unpublished.· 4 years agoIs there a way to have the average line extend past the center of the first and last bars? i.e. how can i make the average line extend to all the way to the horizontal bounds of the graph?
- To post as a guest, your comment is unpublished.· 4 years agoThis should help as long as you don't need to use the secondary axis for a different series. Still searching for the answer for when you do.
- To post as a guest, your comment is unpublished.· 5 years agoDear Sir,
Its good to know that you have done with a line BUT i want to start this from start till end across the table.
- To post as a guest, your comment is unpublished.· 5 years agoTried the first method, but changes both data two lines. I made sure i selected only the red column.
- To post as a guest, your comment is unpublished.· 6 years agoSteps 1-3 don't work. The formula is carried through the rows and it calculates an average for the data for each row.
- To post as a guest, your comment is unpublished.· 5 years agoDid you check that you had locked the reference (look for the $ infront of the Cell references)