Excel: Add a Reference Line to a Chart

To add a reference line in Excel for Microsoft 365 on Windows, add a helper data series containing the target value for each category, then display that series as a line on the chart. For a horizontal line on a category chart, this works for targets such as a sales goal, budget, or average; an XY scatter chart needs a two-point series instead.

What a reference line shows

A reference line marks a useful level across a chart so readers can compare the plotted data with a benchmark. It might show a monthly target, a maximum acceptable reading, a zero line, or the mean of a set of values. The line is part of the chart’s data, not a separate drawing placed over it. That distinction matters: a data series can move when its source values change and stay attached to the chart when the chart is resized.

Excel does not offer one universal Add Reference Line command for every chart type. The reliable approach is to add a series to the chart. In a line or column chart, a helper column can repeat one value for each category. In a scatter chart, the line is defined by two X,Y points. The right setup depends on whether the chart’s horizontal axis contains categories, such as months, or numeric positions, such as measurements.

Choose the right method for your chart

For a chart with categories on the horizontal axis—such as months, departments, or product names—use a helper column with the same benchmark repeated down the rows. Add that column as a new series. A line chart will show it as another line; for a column chart, use a combo chart so the benchmark series is a line over the columns.

For an XY scatter chart, add two points at the left and right ends of the plotted X range, both with the same Y value. Excel connects those points to make a horizontal line. The same idea works for a vertical reference line: use the same X value for both points and place the Y values at the bottom and top of the chart’s range. Do not treat category labels in a line chart as numeric X values; Excel handles those axes differently.

Category chart: a repeated helper value, one per category.
XY scatter chart: two endpoint coordinates for a horizontal or vertical line.
One-time visual annotation: a drawn shape can be quick, but it will not be tied to the data and may not stay aligned as the chart changes.

Add a horizontal line to a category chart

Suppose a worksheet has months in A2:A7 and actual sales in B2:B7. To mark a target of 12,000, enter 12,000 in C2:C7 and use a heading such as Target in C1. If the chart is already made, select it and use Chart Design > Select Data. In the Select Data Source window, choose Add, set the series name to the Target heading, and set the series values to C2:C7. If the chart is created after the helper column is filled, select the full data range, including the new column, when inserting the chart.

Click the chart and open Chart Design > Select Data.
Choose Add under Legend Entries (Series), then select the target heading and helper values in the worksheet.
For a column chart, choose Chart Design > Change Chart Type, select Combo, and set the Target series to Line. Keep it on the primary axis when the target uses the same units and scale as the columns.
For an existing line chart, the added series will normally appear as another line. If it does not, use Change Chart Type to set that series to Line.
Click OK, then format the series so it is easy to distinguish from the measured data.

For example, a target of 12,000 repeated for six months creates six points at the same height. Excel connects the points across the plot area. If the target column has fewer values than the categories, the line may stop early; if the values are blank or text, the chart may show gaps or omit the series.

Make the line show a live average or changing target

A repeated typed value is suitable for a fixed threshold, but a formula is useful when the benchmark should update as the data changes. Assume actual measurements are in B2:B7 and their values are 10, 12, 9, 15, 14, and 12. Enter a label such as Average in F1. With those measurements in B2:B7, this formula in F2 calculates their mean:

=AVERAGE(B2:B7)

The result is 12, because the six values total 72 and 72 divided by 6 is 12. With the average in F2, enter this in C2 and fill it down through C7 to make a chart series that follows the calculated benchmark:

=$F$2

The dollar signs keep the reference fixed on F2 as the formula is filled down. Add C2:C7 to the chart as described above. If values in B2:B7 change, the average and reference line update. If new observations are added below row 7, expand the average formula and the chart’s data range, or use an Excel Table so its structured data range can expand as rows are added. Check the selected data range after adding data; a formula updating by itself does not guarantee that the chart includes new rows.

Add a reference line to an XY scatter chart

Scatter charts plot numeric X and Y values, so a category-style helper column is not enough to define a continuous line. Create two rows in unused columns: put the minimum and maximum X values in one column, and put the same benchmark Y value beside each in another. For instance, if the chart’s X values run from 1 to 10 and the desired horizontal benchmark is Y = 5, the added points are (1, 5) and (10, 5).

Select the scatter chart, choose Chart Design > Select Data, and select Add. In the Edit Series window, assign the two X cells to Series X values and the two benchmark cells to Series Y values. Set the new series to a scatter type with straight lines and no markers if needed, using Chart Design > Change Chart Type. To make a vertical line at X = 4, instead use X values of 4 and 4, with Y values at the chart’s lower and upper limits. If the axis limits change later, update the endpoint values so the line still spans the plot area.

Format the line so it reads as a benchmark

A reference line should be visible without being mistaken for another measured series. Select the line and use Format Data Series to adjust its color, width, dash style, and markers. A contrasting dashed line is a common choice, but use a solid line if dash patterns are hard to distinguish at the chart’s display size. Remove point markers when they add clutter to a long series.

Add a clear legend name such as Target: 12,000 or Average if the chart needs a key. You can also add a data label to one point and edit the label text to explain the benchmark, but avoid labeling every repeated point. Keep the line on the primary axis when its units match the data. A secondary axis can make a line appear at a misleading height if that axis has a different scale; use one only when the series genuinely has different units, and label both axes clearly.

Common problems and how to fix them

The line stops before the chart ends: check that the helper series has one valid value for every category and that its series range includes all the rows.
The benchmark appears as columns or points: select the reference series and change its chart type to Line, or for a scatter chart choose a straight-line scatter series.
The target line is off the chart: compare the target’s value and units with the vertical axis. A target in dollars will not make sense on an axis measured in percentages.
The line is flat but seems sloped or uneven: check that the repeated helper values are identical and that the chart is not using a different axis for the reference series.
The chart does not change after editing the helper value: confirm that the series points to the cells you edited. Use Chart Design > Select Data to inspect the series range.
The line moves out of alignment after changing axis limits: for a scatter chart, revise the endpoint coordinates to match the visible X or Y bounds.

A drawn line is sometimes mistaken for a data-driven reference line. A shape can be convenient for a static slide or screenshot, but it may remain in the wrong place after the chart’s scale, size, or data changes. A helper series is usually better when accuracy and ongoing updates matter.

Excel for Mac and Excel for the web

In Excel for Mac, the same basic method applies: add the helper values to the chart as a series, then change that series to a line where the chart type requires it. Ribbon labels and dialog layouts can vary slightly between Mac releases, so look for the chart design controls for selecting data and changing chart type. The Windows instructions above use Microsoft 365 for Windows.

Excel for the web can handle many routine chart edits, but available chart-type and formatting controls may differ from the desktop application. If the web interface does not offer the needed combo or scatter-series settings, open the workbook in the desktop Excel app to finish the chart. The workbook’s helper data remains ordinary worksheet data, so it can be maintained in either environment.

Frequently Asked Questions

Can I add a target line to a column chart in Excel?
Yes. Add a helper series with the target repeated for each category, then use Chart Design > Change Chart Type and set that series to Line in a Combo chart. Use the primary axis if the target and columns share units.

Can the reference line show the average automatically?
Yes. Calculate the average in a worksheet cell, link each helper-series row to that cell, and add the helper series to the chart. When the source data changes, the average and line recalculate; expand the formula and chart range when you add new data outside the original range.

How do I add a vertical reference line?
For a scatter chart, add two points with the same X value and different Y values at the chart’s lower and upper bounds. For a category chart, a vertical marker is not the same kind of continuous X,Y series; a shape or a separate data-series approach may be more suitable depending on the chart.

Can I draw a line instead of adding a data series?
Yes. Insert a line shape and position it over the chart, but it is a visual annotation rather than chart data. It can lose alignment when the chart is resized or its axis scale changes, so use a series for a benchmark that must stay accurate.

Why can’t I select Combo in Change Chart Type?
Some chart types do not support the same combo options, and the controls can vary by Excel version or platform. Check that the chart type supports a line series over the selected chart, or use a compatible column or line chart in desktop Excel.

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.