How to Add an Average Line to an Excel Chart: Steps

Charts & pivot tables · Updated November 20, 2026

To add an average line to an Excel chart, calculate the average in a helper column, repeat that result beside each chart category, and add the helper column as a new chart series. In Excel for Microsoft 365 on Windows, use Chart Design > Select Data to add the series; for a column chart, change that series to a line. The line then provides a visual reference for comparing each value with the overall average.

What You’ll Need

  • A chart with a numeric data series and its category labels, such as months and sales totals.
  • A blank column beside the source data for the average values.
  • Permission to edit the workbook and chart. If the chart is on a protected sheet, you may need to unprotect it first.

The example below uses five monthly sales figures. In the worksheet, A1 is Month, B1 is Sales, and C1 is Average. Rows 2 through 6 contain the months January to May and sales of 12, 18, 15, 25, and 20. The average is 18. The same process works with dates, categories, and other numeric measures.

Step-by-Step

  1. Check the source data. Put category labels in one column and the values to average in the next, with headers in the first row. For the example, the labels are in A2:A6 and the sales values are in B2:B6. Check that the sales cells contain numbers, not numbers stored as text. Avoid merged cells in the chart’s source range, because they can make series selection confusing.
  2. Calculate the average. With sales values in B2:B6 and the average helper column headed in C1, enter =AVERAGE($B$2:$B$6) in C2. The result is 18 because (12 + 18 + 15 + 25 + 20) ÷ 5 = 18. The dollar signs keep the source range fixed if the formula is copied. The formula uses the English-language function name and comma argument separators used by Excel.
  3. Fill the average down the helper column. Copy C2 through C6, either by dragging the fill handle at the lower-right corner of C2 down to C6 or by copying C2, selecting C3:C6, and pasting. Each row should now display 18. A separate copy of the same average for every category is what lets Excel draw a horizontal series across the chart. If your source data has more or fewer rows, fill the helper formula to match those rows.
  4. Create a chart or select the existing one. If you have not made a chart yet, select A1:B6 and choose Insert, then select an appropriate chart type, such as a line chart or a clustered column chart. If a chart already exists, click it once so the Chart Design tab appears. Leave the average column out of the initial selection if you prefer to add and check it separately in the next step.
  5. Add the helper column as a series. With the chart selected, go to Chart Design > Select Data. In the Select Data Source dialog, choose Add. Set Series name to C1, and set Series values to C2:C6. If Excel inserts a default value, replace it with the helper values; depending on the dialog, you may need to select the existing entry in the Series values box before entering the range. Confirm the dialog, and check that the category labels still correspond to A2:A6. The chart should now include a second series whose values are all 18.
  6. Make the new series a line when needed. In a line chart, the average series will usually appear as another line automatically. In a column chart, it may initially appear as a second set of columns. Select the chart and choose Chart Design > Change Chart Type > Combo. Set the Sales series to Clustered Column and the Average series to Line, then confirm. Keep Secondary Axis turned off for the average series unless you intentionally need a different scale. With the same value axis, the average line can be compared directly with the columns.
  7. Format the reference line. Click the average series in the chart. If it is hard to select, click a point belonging to that series or choose the series from the chart’s selection options. Right-click and choose Format Data Series. In the Format Data Series pane, open Fill & Line, then Line, and choose Solid line. Pick a contrasting color and increase the width enough to make the reference visible without overwhelming the data. You can also remove markers if they make the average look like another set of individual observations.
  8. Check the result and label it. Confirm the line runs across the full set of categories and sits at the value 18 on the example chart. If it is not obvious what the line means, add a legend and name the helper header Average, or add a text box near the line. Give the chart a descriptive title, such as Monthly Sales with Average. Save the workbook after checking that the line and data labels remain readable.

The chart is linked to the helper cells, so changing a sales value recalculates the average and updates the line. If you add new rows later, make sure the formula range, filled helper values, and chart series range all include them.

Common Mistakes

  • Adding just one average value. A single cell is not enough to make a line across multiple categories. Repeat the average beside every category and chart all of those helper values.
  • Using a moving average by mistake. A moving-average trendline is calculated across a changing window of values. It is not the same as a fixed overall average reference line. Use the helper-series method when you want one constant level across the chart.
  • Including the header or wrong cells in Series values. The series values should be the numeric helper cells, such as C2:C6, rather than C1:C6. An incorrect range can produce an unwanted point, an error, or a line that does not match the categories.
  • Putting the average on a secondary axis unnecessarily. If the average and original data use the same units, a secondary axis can make the line appear at a misleading height. Leave that option off unless the series genuinely uses a different scale.
  • Forgetting to extend the helper column. If you add categories but do not fill the average formula or expand the chart series, the line may stop before the latest data point.

Tips

  • Use a clear helper-column header such as Average; the header can become the series name in the chart legend.
  • Choose a line color that contrasts with the columns or data line, and avoid a color already used for an important series.
  • For charts that will grow, convert the source range into an Excel table using Insert > Table. Tables can make it easier to extend source data, but check that the chart and helper formula include new rows as expected.
  • Keep the average series on the same axis as the data when their units match. For example, a sales average belongs on the sales axis, not an unrelated secondary scale.
  • If the chart is filtered or contains hidden rows, decide whether the average should include those values. The AVERAGE formula shown here calculates from its specified cell range, even if some rows are hidden.

Troubleshooting

The average line does not appear. Open Chart Design > Select Data and verify that the Average series is listed and points to the helper cells. Make sure the helper cells contain numbers and that the line is not formatted as No line. In a column chart, use the Combo chart type and set the Average series to Line.

The line appears as columns or as markers only. In a column chart, set the average series to Line in Chart Design > Change Chart Type > Combo. If you see markers but no connecting segment, select Format Data Series > Fill & Line > Line and choose Solid line. For categories treated as text, the connected segments should still span the category positions.

The line is at an unexpected height. Check that the average formula references the intended numeric range and that the series uses the primary axis. Look for an accidental secondary-axis setting or data values stored as text. Also confirm the chart is not displaying a different series or a different range from the worksheet you intended.

The line ends early or changes when rows are added. The chart series may still point to the original short range. Use Chart Design > Select Data to update Series values and the category labels, and fill the average formula down for every added row. If the data is in an Excel table, inspect the chart after adding rows rather than assuming every chart range expanded automatically.

The average does not match what you expected. Check for blanks, text, zeros, and excluded cells in the range. AVERAGE ignores blank and text cells in referenced cells but includes zero values. Confirm that the formula range covers exactly the observations you intend to average.

On Mac, the Chart Design and Select Data controls are available when a chart is selected, though dialog layout and pane placement can differ from Windows. Excel for the web supports many basic chart edits, but if the Combo chart controls are not available for your chart, open the workbook in the desktop Excel app to set the average series to a line.

Frequently Asked Questions

Can I add an average line to a bar chart?
Yes. Add the repeated average values as a series, then use Chart Design > Change Chart Type > Combo to set that series to Line. If the chart’s orientation or type does not offer a suitable combo option, use a column chart or a scatter chart that supports the comparison you need.

Will the average line update when my data changes?
Yes, if the helper formula refers to the cells that change and the chart series includes the helper cells. When adding new observations, extend the formula and the chart’s category and series ranges as needed.

Can I show a different average for each group?
Yes. Calculate an average for each group and repeat that group’s result for the categories it represents. A single overall average line is appropriate only when you want one benchmark for the entire plotted set.

Can I use a chart trendline instead?
A trendline is intended to show a pattern, such as a linear trend, and its level can rise or fall. A fixed average line is constant across the categories. For a clear overall-average benchmark, the helper-series method gives you more direct control.

Why is my average line not perfectly horizontal?
Check that every helper cell contains the same average result and that the series is plotted on one axis. If the values differ, the formula may have shifted as it was copied; use a fixed range such as the dollar-locked reference in the example.

Related

Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.

More in Charts & pivot tables

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.