How to add a horizontal average line to chart in Excel?

To visually represent the average value of your data with a horizontal line on a chart, you can first calculate the mean using Excel’s built-in functions, and then incorporate the result into your existing chart. This method works well for column, bar, or line charts where you want the average clearly visible as an overlay.

1. Begin by calculating the average for your dataset. In a helper column (for example, column C), enter the following formula in cell C2: =AVERAGE($B$2:$B$8). This formula computes the average for all values in the range B2 to B8. Next, use the AutoFill handle to drag this formula down to match the range of your data. This ensures each row displays the same constant average value, which will form a straight line in the chart.

Tip: Double-check the range in your formula to make sure it encompasses all relevant data. If your dataset is larger or located in a different column, adjust the cell references accordingly to avoid incorrect average values.

2. With your original data and the helper column selected, go to the Insert tab and choose a chart type that best fits your analysis, such as a 2-D Column chart. Plotting both the data and the average series sets the foundation for your customized chart.

3. After you insert the chart, locate the newly plotted average series, which will typically appear as a separate set of bars (often in red) next to your original data. Click on one of these bars to select the entire average series, then right-click and choose Change Series Chart Type from the context menu.

4. The Change Chart Type dialog box will appear. In the left panel, select Combo chart type. Then, for the Average series, choose a line chart style from the drop-down menu. This converts the average series from bars to a straight horizontal line, making the average visually prominent on the chart.

Tip: Be careful not to accidentally change your data series chart type to line unless you intend to do so, as this will alter the chart’s display.

5. Click OK. Your chart should now display a horizontal average line across your data columns, serving as a reference point for quick comparison.

Applicable scenarios: This approach is ideal when you want a fully dynamic solution—if you update your dataset, the average and the chart will update automatically. It requires manual setup but doesn’t need any add-ins or code.

Advantages: Works with built-in Excel tools, updates automatically with data changes, highly customizable.

Limitations: Slightly more time-consuming for very large datasets or multiple series. You’ll need to adjust formulas and chart selections with more complex data structures.

Troubleshooting: If your average line doesn’t appear as expected, check the formula range, ensure you’ve selected both data and average columns before inserting your chart, and make sure you’re in the Combo chart dialog when changing chart type.

An alternative approach, especially useful when you routinely add average lines or other reference markers, is to utilize the Add Line to Chart feature in Kutools for Excel. This tool streamlines the process, reducing the number of steps and helping avoid manual formula setup. If you already have your chart created, Kutools can add an average line with just a couple of clicks and minimal configuration.

Suppose you have created a standard column chart, as shown in the screenshot below, and wish to quickly illustrate the average of your data points.

1. Select the column chart by clicking on it, then navigate to Kutools > Charts > Add Line to Chart. This will launch the feature dialog.

2. In the dialog box, simply check the Average option, then click OK.

Your column chart now displays a horizontal average line instantly, which makes it easier to present your findings or compare data visually.

Applicable scenarios: This solution is perfect for users who deal with many charts or who want a time-saving, no-fuss process. It’s particularly useful for presentations or dashboards where quick visualization is essential.

Advantages: Fast and simple, no formulas or manual series manipulation, suitable for routine work, other helpful charting features are available.

For those who prefer an automated or programmable solution, or need to repeat the process across multiple charts and worksheets, a simple VBA macro can be used to insert an average line directly onto a column chart. This method is flexible and can be customized, but involves basic coding and macro execution.

1．Click on any data column in your chart; all columns in the data series will be selected, as indicated by selection handles.

2. Hold the ALT + F11 keys to open the Microsoft Visual Basic for Applications window in Excel.

3. In the VBA editor, click Insert > Module to open a new module window. Copy and paste the following VBA code into the 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. After pasting the code, press F5 to run the macro. Upon execution, your column chart will immediately gain a horizontal average line as shown below:

Note: This VBA macro is designed for 2-D Column chart types only. If you use other chart formats, you may need to adapt the code or switch to supported chart types.

Applicable scenarios: Best suited for advanced users who need to automate chart modifications, or for bulk processing across multiple charts without manual intervention.

Advantages: Can be customized, enables batch processing, saves time for repeated tasks.

Limitations: Requires enabling macros and familiarity with VBA. Not as immediate as built-in charting tools or add-ins. May encounter issues if chart structure is complex.

Troubleshooting: If the macro does not execute or throws errors, ensure your chart is a 2-D column chart, double-check the VBA code for missing or incorrect references, and verify that macros are enabled within Excel’s Trust Center settings.

Practical tip: Save your workbook before running unfamiliar macros to avoid unintentional changes. For enhanced safety, run the macro on a copy of your data first.