How to add a vertical line to the scatter chart

Excel 2016 2013 2010 2007
Vertical lines are usually added to the chart to highlight the current date, a critical date, or a deadline. There are many ways to add a vertical line to the existing chart. Errors bars are the easiest way to add a vertical line to a scatter plot. See how to add a vertical line to a line or bar chart or a horizontal line to a scatter plot, line, or column chart:
Scatter chart with vertical line in Excel 365

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

I.   Add new data for the vertical line

   1.   Add the date with the current date to your data.

For example, cell $C$2 contains the data that should be displayed as the vertical line:

Data in Excel 365

   2.   To add a new data series to your chart, do one of the following:

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

In the Select Data Source dialog box:

   2.1.   Under Legend Entries (Series), click the Add button:

New Data Series in Excel 365

   2.2.   In the Edit Series dialog box, type:

  • In the Series X values box - the cell with the current date ($C$2)
  • In the Series Y values box - any visible value, for example, the maximum of the vertical axis (150):
Edit Series dialog box in Excel 365

Note: You can use any visible value, but it is more convenient to use the maximum value.

   2.3.   Click the OK button twice to close the Select Data Source dialog box.

II.   Add the vertical line

   3.   On the chart, select the new data series, then do one of the following:

  • Click on the Chart Elements button, select the Error Bars list, then choose More Options...:
    Chart Elements - Error Bars in Excel 365
  • On the Chart Design tab, in the Chart Layouts group, click the Add Chart Element dropdown list:
    Add Chart Element button in Excel 365

    In the Add Chart Element dropdown list, choose the Error Bars list, then click More Error Bars Options...:

    More Error Bars Options in Excel 365

See more about other options in Adding Error bars.

In the Format Error Bars pane for the Vertical Error bar (see below how to select the Vertical Error bar), on the Error Bar Options tab:

  • In the Direction group, select Minus (in the example, we selected the maximum value, so we don't need the Plus error line),
  • In the End Style group, select No Cap,
  • In the Error Amount group, select the Percentage option, then type 100% in the text field:
    Format Error Bars pane in Excel 365

    Note: To select the Vertical Error bar for some data series, do one of the following:

    • On the Chart Format tab, in the Current Selection group, click the arrow of the Chart Elements dropdown list, then select Series {data series name}: Y Error Bars for the needed data series:
      Select in the Chart Format in Excel 365
    • Click the arrow next to Error Bar Options and choose the Series {data series name}: Y Error Bars in the dropdown list:
      Select in the Format Error Bars pane in Excel 365

III.   Add the vertical line label

   4.   Right-click the data series for the vertical line, then choose Add Data Labels -> Add Data Labels in the popup:

Add Data Labels in Excel 365

   5.   Right-click on the added data series labels and choose Format Data Labels... in the popup menu:

Format Data Labels in popup menu Excel 365

   6.   On the Format Data Labels pane, on the Label Options tab:

  • In the Label Options section:
    • In the Label Contains group, select the X Value checkbox and unselect all other checkboxes,
    • In the Label Position group, select the Above option:
    Format Data Labels in Excel 365
  • In the Number section, select the appropriate data format. For example:
    Format Data Labels in Excel 365

Note: If you have formatted the data on the spreadsheet, you can select the Linked to source checkbox to format the label in the same way as the source data:

Format data label in Excel 365

IV.   Customize the chart

   7.   Select and delete the Horizontal Error bars (X Error Bars).

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

If you change the date, the chart will be changed too:

Chart with vertical line in Excel 365

See also this tip in French: Comment ajouter une ligne verticale au graphique.

Please, disable AdBlock and reload the page to continue

Today, 30% of our visitors use Ad-Block to block ads.We understand your pain with ads, but without ads, we won't be able to provide you with free content soon. If you need our content for work or study, please support our efforts and disable AdBlock for our site. As you will see, we have a lot of helpful information to share.