To insert an average line in an Excel chart, calculate the average in a helper column, repeat that value beside each chart category, then add the helper column as a chart series and format it as a line. The result is a horizontal reference line that helps readers compare each data point with the overall average. You will need a chart based on the data and a spare worksheet column for the average series.
What You’ll Need
- A Microsoft Excel for Microsoft 365 workbook on Windows, with the data already arranged in columns or rows.
- An existing chart, or data you are ready to chart. This guide uses categories in column A and values in column B.
- An empty helper column next to the chart data. It will contain the same average value for every category.
The steps use the desktop version of Excel for Windows. On a Mac, the chart commands are generally on the Chart Design tab as well, though the dialogs and their arrangement can look different. Excel for the web has chart-editing options, but if you cannot find the series or combo-chart controls described below, open the workbook in the desktop app.
Step-by-Step
- Check the data the chart should represent. Put category labels in one column and the corresponding numeric values in the next. For example, A1 can be Month, B1 can be Sales, and rows 2 through 7 can hold six months of data. Check that the values are numbers rather than numbers stored as text, and decide which records belong in the average. If the chart already exists, identify the exact source range and use the same records for the average.
- Add a heading for the helper series. In the next empty column, enter a clear heading such as Average in C1. The heading will be used as the series name in the chart legend. Keep the helper column beside the source data when possible; this makes the ranges easier to select and the chart easier to maintain.
- Calculate the average in the first data row. With category labels in A2:A7 and the numeric values in B2:B7, enter this in C2:
=AVERAGE($B$2:$B$7). The dollar signs keep the source range fixed if you copy the formula down. For example, if B2:B7 contains 12, 15, 9, 18, 14, and 16, the average is 14 because the values total 84 and 84 divided by 6 is 14. - Copy the average down beside every category. Select C2 and drag its fill handle down through C7, or copy C2 and paste into C3:C7. Each row should display 14 in the example. The repeated values create a series with one point for each category, which Excel can draw as a horizontal line. If you add more data rows later, extend both the chart and the helper series to include them.
- Add the helper range to the existing chart. Click the chart, then select Chart Design > Select Data. In the Select Data Source dialog, choose Add under Legend Entries (Series). For Series name, select C1; for Series values, select C2:C7, replacing any default entry in the box. If Excel inserts a sheet name or dollar signs as you select the cells, that is fine. Click OK, then OK again to close the dialog. The average values are now part of the chart.
- Make the added series a line if the chart is a column chart. With the chart selected, choose Chart Design > Change Chart Type, then select Combo. In the list, set the original data series to its existing type, such as Clustered Column, and set Average to Line. Leave Secondary Axis unchecked when both series use the same measurement and scale. Click OK. For a line chart, the added series is usually already shown as another line, so this conversion may not be needed.
- Format the average line so it is easy to distinguish. Click the average series in the chart. If it is difficult to select, use the chart element selection list on the Format tab or click the Average entry in the legend. Open Format Data Series, then use Fill & Line > Line to choose a solid line and a contrasting color. Increase the line width enough to see it, and use a dashed style if that helps separate it from the data. Remove markers if you want a clean reference line; retain them if the individual average points should be visible.
- Label and check the result. Confirm that the line crosses the chart at the same value as the calculated average and that its legend entry says Average. If readers may not know what it represents, add a brief chart title or text label. Compare the line with the source values and verify that all intended categories are included. Save the workbook after checking that the chart updates when a source value changes.
For a chart you have not made yet, you can select the category, value, and helper columns together before choosing Insert and a chart type. Excel may treat the repeated average values as a second data series automatically. For a column chart, still check the chart type and set Average to Line if Excel displays it as another set of columns.
Common Mistakes
- Adding the average as only one point. A single average cell is not enough to draw a line across all categories. Repeat the value for each category and add the full helper range.
- Selecting the wrong series range. Including the heading cell among the values or selecting only part of the helper column can produce an error or a partial line. Use C1 as the series name and C2:C7 as the values in the example.
- Using a different set of records for the chart and average. If the chart omits a row but the AVERAGE range includes it, the reference line will not represent the visible data. Match the formula range to the observations displayed, and revisit both ranges when adding or removing records.
- Putting the line on a secondary axis unnecessarily. A secondary axis has its own scale, so the line can appear at a misleading height even when its value is the correct average. Use the primary axis when the plotted values share units and scale.
- Using a stacked chart without considering what it means. In a stacked chart, series are added together visually. A line set to the same axis may not provide a clear comparison with individual category values. A regular clustered column chart or line chart is usually easier for this purpose.
- Assuming the average ignores filtered or hidden records. AVERAGE includes numeric values in its referenced range even when rows are filtered out or hidden. If the line should reflect only visible records, use a formula designed for that behavior and check its result against the visible data before charting it.
Tips
- Use a table when the dataset will grow. Converting the source range to a table with Insert > Table can make new rows easier to include. Confirm that the chart and helper formula expand as intended after adding rows; do not assume every existing chart range will adjust automatically.
- Use a clear visual hierarchy. Keep the data series prominent and make the average line a contrasting but not overpowering color. A dashed line is often useful when color alone is not enough to distinguish the reference line.
- Choose the average that answers the question. A simple average gives each numeric observation equal weight. If the categories represent groups of different sizes, an unweighted average of group values may not equal the average of all underlying records. Decide whether you need an overall mean or a weighted result before building the line.
- Keep the axis honest. A line is easiest to interpret when it shares the data series’ axis. If the series use different units, a shared scale may not be meaningful; explain the scale clearly rather than placing the reference line on a second axis without a reason.
- Make the line update with the data. The AVERAGE formula recalculates when a value in its referenced range changes. The chart will update as well if its source ranges include the relevant cells. If the line stops short after you add categories, check and extend the chart’s data source.
- Consider a dynamic visible-record average only when needed. If the chart is filtered and the average must respond to visible rows, Excel’s SUBTOTAL function can calculate an average that responds to filtering. Test the behavior with your filters and hidden rows, because different SUBTOTAL function numbers treat manually hidden rows differently.
Troubleshooting
The average series appears as columns instead of a line: Select the chart and go to Chart Design > Change Chart Type > Combo. Set the Average series to Line, while leaving the original series as columns. If Combo is unavailable for that chart, change the chart to a compatible standard type first, then add or convert the series.
The line is missing or appears to stop early: Open Chart Design > Select Data and inspect the Average series values. Make sure the series range includes one numeric helper value for every category. Also check that C2:C7 contains numbers and not blanks or text, and confirm the category range has the same number of points.
The line is at the wrong height: Check the formula’s average range and the chart’s axis settings. Confirm that the line is on the primary axis and that the chart is using the expected scale. If a secondary axis was selected, edit the combo settings and clear its checkbox for Average when the units match.
The line does not change when data changes: Confirm that the formula references the correct value cells, then check Formulas > Calculation Options and choose Automatic if the workbook is set to manual calculation. If you added new records, extend the formula range and chart series to cover them.
The average seems wrong after filtering: AVERAGE still counts numeric values in rows hidden by a filter. Decide whether the reference should include all source records or only visible ones. If it should follow the filter, replace the calculation with an appropriate SUBTOTAL-based approach, then verify the displayed average against the visible values.
I cannot select the line to format it: Click the chart, then use the chart element drop-down on the Format tab to choose the Average series. You can also click its legend entry, which selects the series, and then open Format Data Series. When two series overlap, temporarily hide or recolor the other series to make selection easier.
Frequently Asked Questions
Can I add an average line to a chart without changing the original data?
Yes. Put the formula and repeated average values in a helper column outside the original data range, then add that column as a series through Chart Design > Select Data. The original values remain unchanged.
Will the average line update automatically?
It will recalculate when values in the formula range change, and the chart will refresh if its series range includes the helper cells. When you add categories, extend the formula and chart range or use a table-based setup that expands appropriately.
Can I show an average line on a line chart?
Yes. Add the repeated average values as another series. Excel will generally display it as a second line; format its color and style so viewers can tell it apart from the data.
How do I show the average value as a label?
One straightforward option is to add a text box to the chart that says, for example, Average: 14. You can also select the average series, use Chart Design > Add Chart Element > Data Labels, and then adjust the label position. If the data changes, remember to keep a manually typed text box current.
Does this method work in Excel for Mac?
The helper-column approach works in Excel for Mac. Select the chart and look for Chart Design > Select Data and Change Chart Type; dialog layouts can differ slightly from Windows. For web Excel, use the desktop app if the available chart controls do not let you add or convert the series.
Should the average line use a secondary axis?
Usually not when the average and plotted values use the same units. Put the line on the primary axis so its height represents the same scale as the columns or data line. A secondary axis is only appropriate when comparing genuinely different units, and it should be clearly labeled.
Related
- How to Find Exact Duplicate Names in Excel
- F Statistic in Excel: How to Find It
- Find Duplicates in Google Sheets: A Step-by-Step Guide
Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.
More in Charts & pivot tables
- How to Add a Formula to an Excel GraphTo add a formula’s results to an Excel graph, enter the formula in a worksheet column, fill it…
- Portfolio Slicer in Excel: Build an Interactive DashboardA portfolio slicer in Excel is a clickable filter for a PivotTable or Excel table, letting you narrow…
- Combo Chart Icon in Excel: Where to Find and Use ItIn Excel for Microsoft 365 on Windows, find the combo chart icon on the Insert tab, in the…
- Excel Pivot Table Percent of SubtotalTo show a value as a percent of its subtotal in an Excel pivot table, add the field…
- Slope of 0 Graph: Meaning, Examples, and How to Spot ItThe slope of a graph is 0 when the graph is a horizontal line: as x changes, y…
- Google Sheets Pivot Table Auto Refresh ExplainedYes. Google Sheets pivot tables update automatically when the cells in their source range change; there is no…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.