To add an overall average line to an Excel chart, calculate the mean in a worksheet cell, repeat that value in a helper column for every chart category, then add the helper column to the chart as a new series. Excel plots those repeated values as a horizontal line, which updates when the average formula recalculates.
Add a constant overall average line
This method works by plotting the same average value once for each category on the chart. First make sure the average formula covers exactly the observations represented in the chart.
As an Amazon Associate I earn from qualifying purchases.
- Calculate the mean. In an unused cell, enter
=AVERAGE(B2:B13), replacingB2:B13with the cells containing the plotted numeric values. Excel’s charting guidance covers creating charts from worksheet data. - Create a helper series. Add a column beside the chart’s source data and name it
Average. In the first data row, refer to the average cell. For example, if the result is in E1, enter=$E$1, then fill the formula down once for each category. The dollar signs keep the reference fixed when you copy it. - Add the helper column to the chart. Select the chart, choose Chart Design > Select Data, and add the helper column as a series. Include the category labels and the repeated average values. The exact controls can vary by Excel version and platform.
- Make the line easy to read. Format the new series with a contrasting line color. Remove its markers if they make the chart cluttered, and keep or add a clear label such as “Average” so readers know what the line represents.
- Check the value axis. The average series must use the same vertical scale as the data it summarizes. If Excel assigns it to a secondary axis, change the series or axis settings so the line is plotted on the relevant scale.
Because the helper cells refer to the average formula, the line can reflect changed source values as the formula recalculates, provided the chart still includes the helper range. If you add categories, extend the helper series and chart data range to cover them.
Choose the line that matches your question
| Chart element | What it shows | How it behaves | How it is added |
|---|---|---|---|
| Overall average | One mean calculated from the selected observations | A constant horizontal reference | Calculate the mean and add it as a repeated-value helper series |
| Trendline | A fitted line or curve describing a data pattern | Changes across the chart to represent the fitted pattern | Use Chart Design > Add Chart Element > Trendline, then choose a type |
| Moving average | A smoothed view of changing data | Varies across the chart as it smooths successive points | Choose the moving-average trendline option where available |
A moving average is not the same as one overall mean: Microsoft says it “smooths out fluctuations in data and shows the pattern or trend more clearly.” See Microsoft’s explanation of predicting data trends. For trendline controls and available types, consult Microsoft’s trend and moving-average instructions. Microsoft notes that the trendline option may appear only when a chart with multiple data series is selected without selecting an individual series.
Quick Recap
Rank #4
#1 Best Overall
- The Microsoft Office 365 Bible: The Most Updated and Complete Guide to Excel, Word, PowerPoint, Outlook, OneNote, OneDrive, Teams, Access, and Publisher from Beginners to Advanced
- ABIS BOOK
Fix a missing or misplaced average line
- The line does not appear: Check that the helper series was added and that its values are numeric and included for the chart’s categories.
- The line appears at the wrong height: Confirm that the average formula uses the intended plotted values and that the average series shares the data’s vertical axis.
- The line stops short: Extend the helper formulas and chart data range to include every category.
- You cannot find the expected chart-line option: Built-in lines and bars are limited to certain chart types; they are distinct from a custom average-value series. Microsoft lists chart contexts for series lines, drop lines, high-low lines, and up-down bars. Menu names and controls can also differ across Excel versions and platforms. Microsoft’s chart guidance includes Excel for Microsoft 365, Excel 2024, and Excel 2021, as well as chart editing in Excel for the web; see Create a chart from start to finish.
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
