To add an average line to a bar graph in Excel, calculate the average in a worksheet cell, repeat that value in a helper column, then add the helper column to a column chart as a line series. This gives viewers a quick reference for comparing each bar with the overall average. The steps below use Excel for Microsoft 365 on Windows and a vertical column chart; notes explain what to do with horizontal bars.
What You’ll Need
- A table with category names and the numeric values you want to compare.
- An Excel workbook with the data arranged in columns, with one row per category.
- A 2-D clustered column chart. If you already have a chart, you can use it, but the chart needs to plot the same categories and values as your table.
The example uses five categories and values of 12, 18, 20, 25, and 25. Their average is 20, so the reference line should cross the chart at the value 20. Your own data can have more or fewer rows; adjust the formula range to match.
Step-by-Step
- Set up the data table. Put category names in column A and the values in column B, with headers in row 1. For example, label A1 “Category” and B1 “Value”; enter your five category names in A2:A6 and the corresponding numbers in B2:B6. Add a header called “Average” in C1. Keep the data rows continuous, with no blank rows in the middle, so Excel can identify the chart series cleanly.
- Calculate the average. Choose an unused cell, such as E2, and enter the average formula. With numeric values in B2:B6, enter
=AVERAGE(B2:B6)in E2. The example returns 20: (12 + 18 + 20 + 25 + 25) divided by 5. If your data extends to row 30, for example, change the range to B2:B30. Make sure the range includes the intended values and not a total row that would distort the average. - Make a helper series. Excel needs one average value for every category to draw a line across the full chart. With the average in E2 and the table’s category rows in 2 through 6, enter
=$E$2in C2. Copy or drag the fill handle from C2 down through C6. Every cell in the helper column will now show 20. The dollar signs keep the reference fixed on E2 when you fill the formula down. If you add categories later, fill the helper formula into their rows as well. - Insert the column chart. Select A1:C6, including the headers, category names, values, and helper series. On the ribbon, choose Insert > Insert Column or Bar Chart > 2-D Clustered Column. Excel creates a chart with a column for the original value and another for the average in each category. The average may initially look like a second set of bars; that is expected and you will change its chart type next.
- Open the combination-chart settings. Click the chart, then choose Chart Design > Change Chart Type. In the dialog, select Combo. The list should show one row for the Value series and one for the Average series. If you do not see both, close the dialog and check that you selected the helper column when creating the chart.
- Change only the Average series to a line. In the Combo settings, leave the Value series as Clustered Column. Set the Average series to Line. Leave Secondary Axis unchecked when both series use the same units and scale. Click OK. The columns should remain, with a horizontal line at the average value running across the categories.
- Check the axes and line position. Read the vertical value axis and confirm that the line crosses at the calculated average. In the example, it should align with 20. If the chart’s vertical axis does not show 20 clearly, you can adjust its bounds: right-click the value axis, choose Format Axis, and set suitable minimum and maximum bounds under Axis Options. Do not set bounds that crop or exaggerate the differences without a clear reason.
- Format the line for clarity. Click the average line and open Format Data Series if needed. Choose a contrasting line color and a width that is visible without overpowering the bars. You can remove markers if Excel added them, or keep small markers if you want to emphasize category positions. Give the series a clear legend name such as “Average,” and add a chart title that identifies what the numbers measure.
- Verify the result after edits. Change one source value temporarily and check that the average cell, helper values, and line update. Undo the temporary edit, then save the workbook. If you regularly add rows, consider converting the source range to an Excel table using Home > Format as Table; when a table expands, extend the average calculation and chart series as needed, then check that the new row is included in the chart.
In Excel for Mac, the chart design and change-chart-type commands are available from the chart ribbon, though the dialog layout can differ slightly. Excel for the web may offer fewer chart-formatting options than the desktop app. If you cannot choose a Combo chart or assign a line type to one series, open the workbook in Excel for Microsoft 365 on Windows or Mac and make the change there.
Common Mistakes
Using the average as just one data point. A line series needs a value at each category. If you chart only the single average cell, Excel may plot one point rather than draw a line across the graph. Repeat the average down the helper column so the chart has one value for every category.
Including the wrong cells. Selecting only the category and value columns leaves the average series out; selecting an extra blank column can create an unwanted series. Check the chart data selection and confirm the series names come from the header row. Also check that the formula range excludes labels, blank cells, and totals that are not part of the data being averaged.
Putting the average on a secondary axis without checking the scale. A secondary axis uses a different scale and can make the line appear at a misleading height. For a comparison of the same measure, keep both series on the primary axis. Use a secondary axis only when the plotted series genuinely have different units, and label both axes clearly.
Expecting a horizontal bar chart to show the same kind of line. A vertical column chart uses a vertical value axis, so its average appears as a horizontal line. A horizontal bar chart has a horizontal value axis; its average reference should be vertical. A line chart series may not create that reference in the intended direction, so use a vertical reference line made with a chart shape or use an XY scatter-based method if it must update automatically.
Tips
Use a series name that makes sense to someone seeing the chart without the worksheet. “Average value” is more informative than “Series 2.” A short note in the chart title or a nearby text box can also specify whether the line is an average across all categories, an average for a selected period, or another benchmark.
Think about what should count in the average. The AVERAGE function ignores blank cells and text, but includes numeric zeroes. If a zero means “no result” rather than a real measured value, decide whether it belongs in the calculation before making the chart. For filtered data, be aware that a normal average formula does not automatically recalculate to include only visible rows.
Keep the line visually distinct using color, thickness, or a dashed style, and make sure the legend explains it. If the bars and line are close in color, readers may confuse the average with another data series. Avoid using a very thick line or a secondary axis simply to make the line easier to see.
For a chart that will be reused, keep the average formula and helper series next to the source data and label them. When you add categories, extend the table, fill the helper formula down, and confirm the new rows appear in the chart. A quick check of the formula range and chart data range prevents a chart from quietly showing an outdated average.
Troubleshooting
The average series is still bars. Select the chart, open Chart Design > Change Chart Type > Combo, and assign Line specifically to the Average series. Do not change the Value series. If the series names are unclear, cancel the dialog and check the headers in row 1.
The line is missing or appears as a dot. Check that the Average helper column contains a value in every category row and that those cells are included in the chart. Confirm the cells are numeric results, not text that looks like a number. If only one row was included, edit the chart data range to include the complete helper series.
The line is far above or below the bars. Open Format Data Series and verify the Average series is plotted on the primary axis. Then check that the value axis has sensible bounds and that your formula points to the intended data range. If the average is correct but the line is outside the visible plot area, the axis limits may exclude it.
The chart does not update when values change. Check that calculation is set to automatic under Formulas > Calculation Options > Automatic. Then inspect the average formula and the chart’s data range. If you added rows beyond the original range, copy the helper formula into those rows and expand the chart selection or use an Excel table as the data source.
You have a horizontal bar chart. For a quick visual marker, select the chart first, choose Insert > Shapes, and draw a vertical line through the plot area at the average’s value-axis position. This shape is not linked to the average calculation and will need repositioning if values or axis bounds change. If you need a line that updates dynamically, use a scatter-series reference line or change the chart to a vertical column chart and follow the steps above.
Frequently Asked Questions
Can I add an average line without changing the chart type?
For a vertical column chart, the average must be plotted as a line series in a combo chart, or added as a manually drawn shape. The combo-series method is linked to the worksheet data and updates when the average changes; a shape does not.
Will the line update when I change a number?
Yes, if the average formula and helper series are included in the chart. Excel recalculates the average and redraws the line when a source value changes. New rows still need to be included in the formula, helper column, and chart range.
Should the average line use a secondary axis?
Usually not. When the bars and average represent the same measure, use the same value axis so the comparison is meaningful. A secondary axis is appropriate only when series use different units or scales, and it should be labeled clearly.
Can I show the median instead of the average?
Yes. Calculate the median in a worksheet cell and repeat that result down the helper column, then chart the helper series as a line. Label it “Median” so readers do not mistake it for the average.
Can I add an average line to a horizontal bar chart?
Yes, but the reference should be vertical because the values run along the horizontal axis. A manually drawn shape is quick but static; for an automatically updating marker, build a scatter-series reference or use a vertical column chart.
Related
- Excel Yellow Color Code: RGB, Hex, and Theme Values
- ISBLANK in Excel: How It Works and When to Use It
- Excel Forecasting: How to Predict Future Values
Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.
More in Charts & pivot tables
- Excel Pie Chart Labels: Add, Format, and TroubleshootTo add labels to an Excel pie chart, select the chart and choose Chart Design > Add Chart…
- Excel Dashboard Graphs: Choose, Build, and Format ThemExcel dashboard graphs are charts arranged to show important measures, comparisons, and trends at a glance. In Microsoft…
- Excel Column Number to Letter Chart and Formula GuideIn Excel, column 1 is A, 26 is Z, 27 is AA, and 16,384 is XFD. Use the…
- Excel Dashboard Software Tutorial: Build One Step by StepTo build an Excel dashboard, organize your source data in a clean Excel Table, summarize it with PivotTables,…
- How to Draw a Pareto Chart in ExcelTo draw a Pareto chart in Excel for Microsoft 365 on Windows, select a table with category names…
- Excel Box Plot X-Axis: Categories, Labels, and FixesIn an Excel box plot, the x-axis normally shows the categories or data groups being compared; the y-axis…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.