How to add horizontal benchmark/target/base line in an Excel chart?

If you have Kutools for Excel installed, you can apply its "Add Line to Chart" feature to quickly add average line, benchmark line, target line, based line, etc. to the currently selected chart in Excel.

1. Select the chart you will add benchmark line for.

2. Click "Kutools" > "Charts" > "Add Line to Chart" to enable this feature.

3. In the Add line to chart dialog, please check the "Other values" option, refer the cell containing the specified value or enter the specified value directly, and click the Ok button. See screenshot:

Now the specified benchmark line is added in the selected chart.

Note: You can easily format the benchmark line as you need:
(1) Double click the benchmark line to enable the "Format Error Bars pane".
(2) Enable the "Fill & Line" tab, and you can change the parameters to format the benchmark line freely, says line color, dash type, line width, etc.

This method will guide you to copy the benchmark/target/baseline data to the destination chart as a new data series, and then change the chart type of the new series to Scatter with Straight Line in Excel.

1. Enter your benchmark data in the worksheet as below screenshot shown.

Note: In the screenshot, 12 indicates there are 12 records in our source data; 85 means the benchmark sales, and you can change them as your need.

2. Select the benchmark data (the Range D2:E4 in my example), and press Ctrl + C keys simultaneously to copy it.

3. Click to activate the original chart, and then click "Home" > "Paste" > "Paste Special". See screenshot:

4. In the opening "Paste Special" dialog, please check the "New Series" option, the "Columns" option, the "Series Names in First Row" checkbox, the "Categories (X Labels) in First Column" checkbox, and then click the OK button. See screenshot:

Now the benchmark data has been added as a new data series in the activated chart.

5. In the chart, right click the new Benchmark series, and select "Change Series Chart Type" from the context menu. See screenshot:

6. Now the Change Chart Type dialog comes out. Please specify the Chart Type of Benchmark series as "Scatter with Straight Lines", uncheck the "Secondary Axis" option behind it, and then click the OK button. See screenshot:

So far, the benchmark line has been added in the chart. However, the benchmark line neither begins at the outside border of the first column, nor reaches the outside border of the last column.

7. Right click the horizontal X axis in the chart, and select "Format Axis" from the context menu. See screenshot:

8. In the "Format Axis" pane, under the "Axis Options" tab, please check the "On tick marks" option in the "Axis Position" section. See screenshot:

Now you will see the benchmark line comes across all column data in the chart. See screenshot:

Notes:

(1) This method can also add horizontal benchmark line in an area chart.

(2) This method can also add horizontal benchmark line in a line chart.

Add horizontal benchmark/target/base line by Paste Special in Excel chart