How to Put an Average Line in an Excel Chart Easily

Charts & pivot tables · Updated November 20, 2026

To put an average line in an Excel chart, calculate the average in a helper column, repeat that value beside each data point, and add the helper column to the chart as a series. In Excel for Microsoft 365 on Windows, this works for an existing chart or one you create from scratch. You’ll need a set of numeric values and, for most charts, category labels such as dates or product names.

What You’ll Need

  • A worksheet with one column of categories and one column of numeric values. For example, months in A2:A13 and sales in B2:B13.
  • A spare column for the average series. The example below uses column C.
  • An existing chart, or the data range you want to chart. A column, bar, or line chart is a good fit for an average reference line.

Step-by-Step

  1. Check and arrange your data. Put category labels in one column and the values you want to compare in the next. Include clear headers in row 1, such as Month, Sales, and Average. Check that the value cells contain numbers rather than numbers stored as text. Decide which observations should contribute to the average: Excel’s AVERAGE function ignores blank cells and text, but it includes zeros. For example, a zero-sales month affects the average; a blank month does not.
  2. Calculate the average in a helper column. With the monthly values in B2:B13 and the header Average in C1, enter this in C2: =AVERAGE($B$2:$B$13). This example assumes B2:B13 contains 12 numeric values; if those values add up to 200, the formula returns 16.6667, or about 16.67. The dollar signs keep the calculation range fixed when you copy the formula. Use your own data range if it differs.
  3. Repeat the average beside every category. Select C2, then drag the small fill handle at its lower-right corner down to C13. You should see the same average in each row. A chart needs a value for each category to draw a continuous series across the plot. If you prefer, copy C2 and paste it into the remaining cells in the helper column. Make sure the last average cell lines up with the last data row.
  4. Create a chart or prepare your existing one. To create a chart, select A1:C13, then choose Insert > Insert Column or Bar Chart and select a clustered column chart. Excel includes the actual values and the repeated average series. If you already have a chart based on columns A and B, select it and go to Chart Design > Select Data > Add. In the Edit Series dialog, set Series name to C1 and Series values to C2:C13, then select OK. The existing category labels should remain in place.
  5. Set the average series to a line when needed. If you created a column chart, select it and choose Chart Design > Change Chart Type. In the Combo options, set the main data series to Clustered Column and the Average series to Line, then choose OK. Leave the Secondary Axis box cleared when the average and data use the same units and scale. If your original chart is already a line chart, the average series will usually display as another line without changing the chart type.
  6. Make the reference line easy to distinguish. Click the average line. If you have selected the wrong series, click elsewhere and try again, or use the chart’s Format selection controls to choose the Average series. Open Format Data Series and adjust the line color, width, or dash style so it stands apart from the data. You can also remove markers from the average series if they make it look like another set of individual observations. Keep the line visible against the plot background.
  7. Label and verify the chart. Add or edit the chart title and legend so readers can identify the average. Check that the legend says Average rather than Series 2; you can change the series name by editing C1. Confirm that the line is level and crosses the chart at the calculated value. If the line appears far above or below the data, check the chart axes and make sure the average series is not on a secondary axis by mistake.

In Excel for Mac, the same basic method applies: create the helper column, add it through the chart’s Select Data controls, and choose a line type for the average series if needed. Ribbon placement or dialog layout can vary slightly by release. Excel for the web has fewer chart-formatting controls in some cases; if you cannot set a combo chart or adjust the series as described, open the workbook in desktop Excel.

Common Mistakes

  • Using only one average cell in the chart. A single point may appear as a dot rather than a line. Fill the average formula down so there is one value for every category.
  • Including the wrong cells. If the formula includes a header, unrelated values, or only part of the data, the result will not represent the intended observations. Confirm the range in the formula before charting.
  • Leaving the average as a column. In a column chart, Excel may show the average values as a second set of bars. Change that series to Line in the Combo chart settings.
  • Putting the average on a secondary axis. A second axis can make a reference line look misleading because it uses a different scale. Use the same axis unless there is a specific, clearly explained reason not to.
  • Expecting blanks and zeros to mean the same thing. A blank is ignored by AVERAGE, while a zero is included. Decide whether missing observations should be left blank or represented as zero before interpreting the line.

Tips

  • Use a descriptive series name. Put Average in the helper-column header so the chart legend explains the line. If you want to show a different statistic, use a label that says what it is, such as Target or Median.
  • Keep the formula range understandable. For a fixed report, a clear range such as B2:B13 is straightforward. If you add new observations later, update the formula range, extend the helper formulas, and confirm that the chart includes the new rows.
  • Format for contrast, not decoration. A contrasting solid or dashed line and a modestly heavier width usually make the benchmark easy to see. Avoid a color that is too similar to the plotted data, and check readability if the chart will be printed in grayscale.
  • Use the same units. An average of sales belongs on the sales axis. Do not compare a dollar average to a percentage series on the same scale; make a separate chart or clearly explain separate axes if the chart genuinely needs them.
  • Consider whether one average answers the question. A single overall average can hide seasonal changes or groups with very different values. If the data has distinct periods or categories, calculate and chart separate averages only when that comparison is meaningful.

Troubleshooting

The average line does not appear: Open Chart Design > Select Data and check that Average is listed as a series with the expected cells in Series values. Confirm that the helper cells contain numbers and that the chart range reaches the last data row. If the chart type is a column chart, set the series to Line using the Combo chart options.

The line appears as a dot or has gaps: Check that the average was filled down for every category. A one-cell series cannot form a line, and blank helper cells can interrupt it. If the cells look filled but Excel treats them as text, clear the cells and enter the formula again, then check the number format.

The line is flat but not at the expected height: Compare the formula’s range with the intended input cells and verify whether zeros should be included. Then inspect the chart’s vertical axis bounds and whether the Average series is assigned to a secondary axis. A correctly calculated average will be level, but a changed axis can make its position look surprising.

The chart has bars instead of a line for the average: Select the chart, choose Chart Design > Change Chart Type, and assign the Average series to Line in the Combo settings. If you do not see the series listed, add it first through Select Data.

The line stops before the last category: Extend the helper formula to the final row, and check that the average series values and the chart’s category labels cover the same number of rows. For a scatter chart, categories are numeric x-values rather than text labels, so an added series may require explicit x-values as well as y-values. For a simple average reference, a line, column, or bar chart is usually easier to manage.

Frequently Asked Questions

Can I add an average line to an existing Excel chart?
Yes. Add the helper column to the chart with Chart Design > Select Data > Add, then set the new series to Line if the chart needs a combo type.

Does the average line update when I change the data?
Yes, if the formula refers to the changed cells and the chart includes the helper series. If you add rows outside the original range, extend the formula and chart data range too.

Can I show the average value as a label?
Yes. You can add a data label to the average series and format it to show the value, though labeling every repeated point can clutter the chart. For a cleaner chart, show one label or include the average in a nearby note.

Why is the average line not exactly in the middle of the data?
The arithmetic average is not necessarily the visual midpoint between the highest and lowest values. It uses all numeric observations, and a few unusually high or low values can pull it toward one end.

Can I draw an average line without a helper column?
You can draw a line shape manually, but it will not automatically track changes to the data and may shift when the chart is resized. A helper series is more reliable because Excel plots the calculated values as part of the chart.

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.