An average line on a chart shows at a glance which values are above or below the average, such as months that beat the yearly average in sales. Excel doesn’t have a one-click “average line” button for most charts, but you can add one in a few minutes with a helper column and a combo chart. This guide shows that method, a quick trendline alternative, how to add a target line, and how to fix the most common problems.
For the examples, imagine months in A2:A13 and sales in B2:B13.
Method 1: Add an Average Line with a Helper Column
Step 1: Add an Average Column
In C1, type Average. In C2, enter =AVERAGE($B$2:$B$13) and fill it down to C13. Every row shows the same value, which is what makes a flat line. The dollar signs keep the range fixed as you copy the formula.
Step 2: Select the Data and Insert a Chart
Select A1:C13 and go to Insert > Recommended Charts > All Charts > Combo. Set the Sales series to Clustered Column and the Average series to Line, then click OK. You now have columns with a flat line across them.
Step 3: If You Already Have a Chart
Right-click the chart, choose Select Data, click Add, set the series name to C1 and the values to C2:C13, and click OK. Then right-click the new series, choose Change Series Chart Type, and set it to Line. Microsoft’s guide to combination charts covers the same Change Series Chart Type dialog.
Step 4: Format the Line
Right-click the line and choose Format Data Series. Under Fill & Line, pick a color, set the width to about 2 pt, and choose a dashed style if you like. Turn off markers under Marker > Marker Options > None for a clean line.
Step 5: Label the Average
Click the last point on the line twice to select only that point, then right-click and choose Add Data Label. Format the label to show the value, or type “Average” in it. Delete the legend entry if the label makes it clear.
Method 2: Make the Line Span the Whole Chart
In a column chart, the line starts and ends in the middle of the first and last columns. To stretch it edge to edge, plot the Average series on the secondary axis (Format Data Series > Series Options > Secondary Axis), then click the chart, choose Chart Elements (+) > Axes, turn on Secondary Horizontal, and in its Format Axis pane set Axis position to On tick marks. Hide the secondary axes afterward by setting their labels to None, and make sure both vertical axes have the same minimum and maximum.
Method 3: Quick Alternative with a Trendline
For a line chart or scatter chart, you can add a trendline from Chart Elements (+) > Trendline. Note that a trendline shows the direction of the data, not the average. Use a moving average trendline (Format Trendline > Moving Average) only if you want a rolling average, not a single flat line.
Add a Target or Goal Line
The same steps work for any fixed value. Put the target in one cell, such as F1, and use =$F$1 in the helper column instead of AVERAGE. Change F1 and the line moves. You can add both an average line and a target line to the same chart.
Average Line in a Line Chart
If your data is already a line chart, add the Average column as another series. Since it’s already a line, you don’t need to change the chart type. Just format it as a dashed line in a contrasting color.
Tips for Adding an Average Line in Excel
- Convert the data to an Excel table (Ctrl + T) so the chart and average update when you add rows. In a table, use =AVERAGE([Sales]).
- Use AVERAGEIF to ignore zeros or blanks, such as =AVERAGEIF($B$2:$B$13,”<>0″).
- Use a muted color for the average line so it doesn’t compete with the data.
- Save the chart as a template (right-click > Save as Template) if you make the same chart often.
Troubleshooting
The Average Shows as Columns Instead of a Line
Change the Average series to a Line chart type with Change Series Chart Type.
The Line Is Slanted
The helper column doesn’t contain the same value in every row, usually because the AVERAGE range moved when you copied it. Add dollar signs to lock the range.
The Line Doesn’t Match the Columns’ Scale
The Average series is on a secondary axis with a different scale. Move it back to the primary axis, or set both vertical axes to the same bounds.
The Average Doesn’t Update with New Data
Extend the AVERAGE range and the chart’s data range, or convert the data to a table so both expand automatically.
Frequently Asked Questions
What’s the purpose of an average line?
It gives readers a reference point, so they can see which values are above or below average without calculating anything.
Can I add an average line to any chart type?
It works best with column, bar, and line charts. For a bar chart, the average appears as a vertical line, which needs a scatter series instead of a line series.
How do I update the average line when data changes?
It updates automatically because it’s based on the AVERAGE formula. Just make sure new rows are included in the range.
Can I add multiple average lines?
Yes. Add a helper column for each average, such as one per product or per year, and add each as a line series.
Is there an automatic way to add an average line?
Not built into Excel for column charts. The helper column method is the standard approach, and once it’s set up, it updates on its own.
Related: How to Add a Line in Excel.
Related: how to remove lines in Excel.
Related: how to combine two charts in Excel.
Related: how to add a line of best fit in Excel.
Matt Jacobs has been working as an IT consultant for small businesses since receiving his Master’s degree in 2003. While he still does some consulting work, his primary focus now is on creating technology support content for SupportYourTech.com.
His work can be found on many websites and focuses on topics such as Microsoft Office, Apple devices, Android devices, Photoshop, and more.