Add a Target or Average Line to an Excel Chart

To add a target line to an Excel chart, you add a helper column that repeats the target value, chart it alongside your data, and change that series to a line. Excel has no dedicated "target line" button, but this method takes about a minute and updates itself when the target changes. The same trick draws an average line with AVERAGE. This guide is for anyone building sales, KPI or performance charts on Windows or Mac.

How a target line works#

A target line is just a second data series where every value is the same. If the target is 50, the helper column holds 50 in every row, so when Excel draws it as a line, the line is flat at 50. Your real data stays as columns or bars, and the flat line shows at a glance which months beat the goal.

All examples use this data in A1:B7, with the target in E1:

Step 1: build the helper column#

Put the target in one input cell, then point every row of the helper column at it. With 50 in E1, type this in C2:

=$E$1

Fill it down to C7 (Windows: Ctrl+D; Mac: ⌘D). The $ signs make it an absolute reference, so every row still points at E1 instead of drifting to E2, E3 and so on. Now when the target changes, you edit one cell and the whole line moves. The absolute references lesson lets you practise why the dollar signs matter.

Step 2: add the target line to the Excel chart#

Chart both columns, then change the target series into a line:

1. Select A1:C7.
2. Go to Insert > Insert Column or Bar Chart > Clustered Column. You'll see two sets of columns: Sales and Target.
3. Right-click any Target column and choose Change Series Chart Type.
4. In the dialog, set Target to Line and keep Sales as Clustered Column.
5. Click OK.

You can do it in one go with Insert > Combo chart > Clustered Column - Line, which, with two series, shows the first as columns and the second as a line. On a Mac, the steps are the same; you can also find Change Chart Type on the Chart Design tab. For more on mixing chart types, see the guide to combo charts and secondary axes.

The same works on a bar chart, but bars run sideways, so a "line" series won't sit across them neatly. For horizontal bars, a vertical target line usually needs a scatter series instead, which is more advanced. Most people switch to a column chart. The bar chart guide explains when bars beat columns.

Step 3: format the line so it reads as a target#

A target should look different from the data. Click the line to select it, then press Ctrl+1 (Mac: ⌘1) to open the Format pane:

• Dash type: choose a dashed line so it doesn't look like another data series.
• Colour: a strong contrast, like dark red or black.
• Width: about 2 pt.
• Markers: set to None so the dots disappear.
• Label: click the last point on its own, right-click, Add Data Label, and edit it to read "Target".

If the legend now shows "Target", you can keep it or delete the legend entry and rely on the label. The guide to Excel chart basics covers titles, legends and labels.

How to add an average line instead#

An average line uses the same method, with AVERAGE in the helper column instead of a fixed target. In C2:

=AVERAGE($B$2:$B$7)

Fill it down to C7. Every row returns 49, the average of 42, 55, 38, 61, 47 and 51. Lock the whole range with $: without it, filling down would shift the range to B3:B8, B4:B9 and so on, and the "flat" line would wobble. Then chart it and change the series to a line exactly as above.

You can use any summary the same way: MEDIAN for a typical value that ignores extremes, or MAX and MIN for best and worst months. The AVERAGE function guide and the article on AVERAGE, MIN and MAX explain each one, and the AVERAGE, MIN and MAX lesson lets you practise them.

Make the line span the full chart width#

On a column chart, the line starts in the middle of the first column and stops in the middle of the last. That's often fine. If you want it edge to edge:

1. Right-click the line, choose Format Data Series and set Plot Series On to Secondary Axis.
2. With the chart selected, go to Chart Design > Add Chart Element > Axes > Secondary Horizontal.
3. Click the new top axis, open its format pane, and under Axis position choose On tick marks.
4. Format the right-hand secondary vertical axis so its minimum and maximum match the left axis; otherwise the line sits at the wrong height.
5. Hide both secondary axes by setting Label Position to None and the line to No line.

This is extra work, so only do it when the chart is going into a report or dashboard where looks matter.

Targets on line charts and changing targets#

On a line chart, the target is simply a second line: select the data plus the helper column and insert a line chart. Format it dashed as above. The guide to line charts in Excel covers the rest.

If the target changes month to month, type each month's value into the helper column instead of =$E$1. The line steps up and down to match. To report how many months hit the goal, add a simple count next to the chart: =COUNTIF(B2:B7,">="&E1) returns 3 (Feb, Apr and Jun).

Practise it#

The formulas behind a target line are short: a locked reference and an AVERAGE. Practise AVERAGE, MIN and MAX in a live spreadsheet: you summarise real data and each formula is checked instantly. Then practise absolute references so your helper columns fill down correctly, and try the exercise on this page to build an average line's helper column.