How to add a horizontal line to the chart

  • Excel
  • 2016
Sometimes you need to add a horizontal line to your chart. E.g., this will be useful to show data with some goal line or limits:
Goal as horizontal line in Excel 2016 Limits as horizontal line in Excel 2016

To add a horizontal line to your chart, do the following:

    1.    Add the cell or cells with the goal or limit (limits) to your data, for example:

Additional data for the chart in Excel 2016

    2.    Add a new data series to your chart by doing one of the following:

  • On the Design tab, in the Data group, choose Select Data:
    Data in Excel 2016
  • Right-click in the chart area and choose Select Data... in the popup menu:
    popup in Excel 2016

In the Select Data Source dialog box, click the Add button and in the Edit Series dialog box, type:

  • In the Series name box - the name of this line ( Goal ),
  • In the Series values box - the cell with the goal ( $C$17 ):
Edit Series in Excel 2016

    3.    If it is necessary, change data series:

    3.1.    Right-click in the new data series and choose Change Series Chart Type... in the popup menu:

Change Series Chart Type in Excel 2016

    3.2.    In the Change Chart Type dialog box, choose the Scatter type:

Change Chart Type in Excel 2016

    3.3.    Repeat step 2 to change the data series:

In the Select Data Source dialog box, select the added data series and click the Edit button, then in the Edit Series dialog box:

  • In the Series X values box - add any cell from the horizontal (category) axis (for example, $A$8 ):
Series X values in Excel 2016

You will see the correct data for the same horizontal (category) axis:

New data Series in Excel 2016

    4.    On the Design tab, in the Chart Layouts group, click the Add Chart Element icon and choose Error Bars list:

Add Chart Element in Excel 2016

Excel proposes several error bars, also you can use More Error Bars Options.... If necessary, you can fine-tune the error bar settings from the Format Error Bars task pane:

More Error Bars Options in Excel 2016

To specify the length of the error line, click on the Specify Value button and type the appropriate value for negative and positive values. For this example:

Custom Error Bars in Excel 2016

You can then make any other adjustments to get the expected look.

Thank you for visiting OfficeToolTips

We are glad to help you in your work. However, you are using ad blocker and our efforts will not be rewarded.

Please consider disabling ad blocker before continuing the reading.