Excel Chart Average Line: Add and Format the Mean

To add an average line to an Excel chart, calculate the mean in a worksheet cell, repeat that value in a helper column beside the chart’s data, and add the helper column as a new chart series. For a column chart, change that new series to a line; for an ordinary line chart, add it as another line series. The line then marks the average of the selected values and can update when the data changes.

How to add an average line to an Excel chart

This method is for Excel for Microsoft 365 on Windows and works well for charts with categories such as months, products, or teams. Assume categories are in A2:A6 and the values you chart are in B2:B6. Use column C for the repeated average and put a label such as Average in C1.

In E1, enter a label such as Mean. With the values to average in B2:B6, enter this in E2: =AVERAGE($B$2:$B$6). The dollar signs keep the source range fixed if you copy the formula.
With the mean in E2 and the helper series in C2:C6, enter =$E$2 in C2, then fill or copy it down through C6. Each category now has the same plotted value.
Create a chart from the category and value data, or select the existing chart. On the ribbon, choose Chart Design > Select Data > Add. Set Series name to C1 and Series values to C2:C6, selecting the cells in the worksheet if convenient. Confirm the dialog.
If the existing chart is a column chart, choose Chart Design > Change Chart Type > Combo. Set the Average series to Line and leave the original data series as columns, then confirm. For an ordinary line chart, the added series can remain a line.

The average series has one value for each category, so Excel draws a straight, horizontal line across the chart. If the line does not appear, check that the new series is included in the chart and that its values are numeric.

A small worked example

Suppose five weekly sales totals are 8, 10, 12, 14, and 16 units. Their sum is 60, and dividing by five gives an average of 12 units. In the worksheet layout above, the formula in E2 returns 12, and C2:C6 each contain 12. A chart of the weekly totals with the helper series therefore shows a line at 12.

This line is a reference, not another set of observed sales. It does not indicate that every week had sales of 12; the columns or original line still show the individual results. Give the series a clear name such as Average so a chart legend explains what the line means. If the chart title or axis refers to dollars, percentages, or another unit, the mean line uses that same unit.

Which chart types suit an average line?

A combo chart with columns for the observations and a line for the average is usually the clearest choice when each value belongs to a named category. It lets readers compare each column with a common benchmark. A line chart with a second line series is useful for ordered categories, such as months, when the original data is already shown as a line.

For a horizontal bar chart, Excel’s combo-chart options and axis orientation can make the result less intuitive. Consider using a column chart if a horizontal benchmark line is the main goal, or test the chart after changing its type and confirm that the categories and value axis are still arranged as intended. Avoid putting the average on a secondary axis unless there is a specific reason: different scales can make a benchmark appear misleadingly high or low.

An XY scatter chart is different because both axes represent numbers rather than a list of categories. To draw a horizontal average reference on a scatter plot, add a separate XY series with two points: one at the left edge of the intended x-range and one at its right edge, both with y-values equal to the mean. Set the series’ X values and Y values in the scatter chart’s Select Data dialog. This produces a segment across that x-range; it is not the same procedure as adding a category-based line series.

Make the line update when data changes

The formula in E2 recalculates when a value in B2:B6 changes, and the helper formulas keep the plotted benchmark synchronized with that result. The chart itself still needs to include all the helper cells and the relevant observation cells. If you add new rows below row 6, extend the average formula, helper formulas, and chart source ranges, or place the data in an Excel table so its ranges can expand as rows are added.

For a growing data set, a table can make chart maintenance easier: select the data and use Insert > Table, then build the chart from the table columns and keep the helper series aligned with its rows. Verify the chart after adding rows, since chart behavior depends on how its source range was created. The key distinction is that a recalculating mean formula does not automatically fix an incomplete chart range.

A fixed average is appropriate when the question is, “How do these observations compare with the average of this full period?” If the desired benchmark is a moving average, such as the average of the latest three months at each point, repeating one mean value is not correct. A moving average changes from category to category and needs a separate rolling calculation or, for some chart types, a built-in moving-average trendline.

Format the average line so it reads clearly

Select the average series in the chart, then use the formatting options on the Chart Format tab or the Format Data Series pane. A contrasting solid color and a slightly heavier line can distinguish the benchmark from the data without overpowering it. If the chart has a legend, keep the series name meaningful; if it does not, add a text label only if its meaning is unambiguous.

For a column-and-line combo, markers on every average point are usually unnecessary because the values are identical. You can turn off markers in the series formatting pane and leave the line visible. Check that the line is not hidden behind columns, especially if it has a thin stroke, and make sure it remains distinguishable when printed in grayscale or viewed by someone who cannot rely on color alone.

Use the same value axis as the data series when both are in the same unit. If Excel assigns the line to a secondary axis, inspect the axis bounds and series settings; a secondary scale can distort the visual comparison. The benchmark should correspond to the plotted values, not merely look level with a particular column.

What the average includes—and what it does not

Excel’s AVERAGE function calculates the arithmetic mean: it adds the numeric values and divides by their count. When its argument is a cell range, blank cells and text are ignored, while numeric zeroes are included. That means a zero representing a genuine no-sale day affects the result, whereas an empty cell does not. Decide whether blanks mean missing observations or zero activity before interpreting the line.

Errors in the source range can cause the calculation to return an error rather than a usable benchmark. Also check whether the displayed values have been rounded: a mean such as 12.46 may appear near 12.5 depending on axis units and formatting, even though Excel uses the underlying value. For data with extreme outliers, the mean can be pulled toward those values; a median may better represent a typical observation, but it answers a different statistical question and should be labeled accurately.

Make sure the source range contains only the observations you intend to summarize. Including a total row, an average row, or unrelated values can alter the result. If categories are filtered or hidden, note that AVERAGE over a normal range still includes hidden numeric cells. A chart that filters categories may therefore show an average calculated from more data than is currently visible.

Common problems and how to fix them

The line is missing: Open Chart Design > Select Data and confirm that the Average series is listed and points to the helper cells. For a column chart, also confirm that its chart type is Line in the combo settings.
The line slopes: The helper cells may not all refer to the same mean cell. Check that each formula points to E2, or enter the same calculated value in every helper row.
The line appears at zero: The series may reference blanks, text, or the wrong range. Check the series values and the result in the mean cell.
The benchmark seems too high or low: Verify the source range, whether zeroes should count, and whether a secondary axis has been assigned.
New categories have no line value: Extend the helper formula and chart range, or revise the table/chart setup so added rows are included.

When editing a chart, click the line itself to select its series rather than an individual data point. If selecting it is difficult, use the chart’s current selection list on the Format tab to choose the Average series, then open Format Selection.

Average line versus trendline or a drawn shape

An average line is a constant benchmark calculated from a defined set of values. A trendline estimates a pattern in the data, such as a linear increase or decrease, and is not generally equal to the overall average. Excel’s Add Chart Element trendline options are useful for showing a relationship or direction, but choosing a trendline does not create a mean line.

A manually drawn line or shape can be quick for a one-off illustration, but it is positioned visually rather than tied to the data. It may not move to the correct value when the data changes, the axis scale changes, or the chart is resized. A helper series takes more setup but keeps the benchmark connected to worksheet calculations and is easier for readers to inspect.

Frequently Asked Questions

Does Excel have a one-click average line button?
No general one-click command adds a data-driven mean line to every chart type. The dependable approach for category charts is to calculate the mean, add a repeated-value helper series, and format that series as a line.

Should I use AVERAGE or a trendline?
Use AVERAGE when you want to compare observations with their arithmetic mean. Use a trendline when you want to show an estimated direction or relationship. They communicate different things and are not interchangeable.

Why does my average line not change when I edit the data?
Check that the mean formula points to the edited cells and that the line series points to helper cells linked to the mean. Also check that the chart includes those cells and that the source range covers any newly added rows.

Can I show an average line on a filtered chart?
Yes, but a normal AVERAGE formula over a range includes hidden numeric cells. If the line should represent only visible records, use a calculation designed for the filtering behavior you need and verify its result; do not assume the chart’s visible categories change the ordinary average.

Can I use the same method in Excel for Mac or the web?
The worksheet formulas work in Excel for Mac and Excel for the web. Ribbon names and chart-editing controls can vary by platform and version; if the web version does not expose the needed combo-chart controls, open the workbook in desktop Excel and use Chart Design > Change Chart Type.

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.