How to add a vertical line to the line or bar chart

Excel 2016
To show progress, highlight current or any date, or demonstrate the difference between the target value and the actual one, you can add bullets to the chart, horizontal or vertical lines:
Chart with vertical line in Excel 365

See also how to add a vertical line to the scatter chart.

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

Line and bar chart in Excel 365

   I.   Add new data for the vertical line

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

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

  • For the bar chart, cell $C$2 contains the goal value:
    Data for bar chart in Excel 365
  • For the line chart, cell $D$2 contains the date:
    Data for line chart 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 values box, type or choose the cell with the current date ($C$2 for the bar chart, $D$2 for the bar chart):
Edit Series dialog box in Excel 365

   2.3.   Additionally (for the line chart), select the new data series under Legend Entries (Series).

Then under Horizontal (Category) Axis Labels, click the Edit button and type or choose the same values as for the main data series:

Axis Labels dialog box in Excel 365

   2.4.   Click the OK button twice to close dialog boxes.

Excel rebuilds the chart - don't worry about messy visualization. You will fix it later:

Charts in Excel 365

   II.   Change the series chart type

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

Change Series Chart Type in Excel 365

Notes:

  1. Don't confuse it with the Change Chart Type... If you see Change Chart Type... instead of Change Series Chart Type..., you don't select the data series.

    It is important! You need to change the chart type for the selected data series, not the whole chart.

  2. If you don't see the newly added data series (as shown in the line chart in the example above), do one of the following:
    • Click the arrow next to Format Plot Area (or any other item) and choose the Series {data series name} in the dropdown list:
      Select chart element in Format pane in Excel 365
    • 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} for the “Current date” data series:
      Select chart element in Format Chart tab in Excel 365

    In the Change Chart Type dialog box, for the selected data series, choose the Scatter type and select the Secondary Axis checkbox (if it doesn't select automatically):

    Change Series Chart Type in Excel 365

    Note: You can see the previous formatting for the vertical axis after selecting the Secondary Axis for the new data series. The horizontal axis formatting will fix itself after customizing the secondary vertical axis (see below).


   III.   Correct the new data series

   4.   Open the Select Data Source dialog box again (see step 2), select the new data series, then under Legend Entries (Series), click the Edit button.

In the Edit Series dialog box, type:

  • In the Series X values box - the cell with the appropriate data ($C$2 for the bar chart, $D$2 for the line chart):
    Edit Series dialog box in Excel 365
  • In the Series Y values box - constant value (1):
New Data Series in Excel 365

   IV.   Correct the secondary vertical axis

   5.   Right-click on the secondary vertical axis and choose Format Axis... in the popup menu:

Format Axis in popup menu Excel 365

On the Format Axis pane, on the Axis Options tab, in the Axis Options group, in the Bounds section:

  • In the Minimum field, type 0,
  • In the Maximum field, type 1:
Format Axis pane in Excel 365

Note: You can also type minimum as 1, maximum as 2, or even minimum as 0 and maximum as 2. The difference will be visible when creating an Error bar (see below) and adding a vertical line label (see how to add the vertical line label).


   V.   Add the vertical line

   6.   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.

On the Format Error Bars pane:

  • In the Direction section, select:
    • Both, if you selected the Minimum and Maximum for the secondary axis as 0 and 2,
    • Minus, if you selected the Minimum and Maximum for the secondary axis as 0 and 1,
    • Plus, if you selected the Minimum and Maximum for the secondary axis as 1 and 2,
  • In the End Style section, select No Cap,
  • In the Error Amount section, choose the Percentage option and type 100 % in the appropriate field:
Format Error Bars Options in Excel 365

   VI.   Customize the chart

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

   8.   Add the vertical line label and hide the secondary vertical axis.

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

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

Charts with vertical line in Excel 365

We use cookies to personalise content and ads, to provide social media features and to analyse our traffic. We also share information about your use of our site with our social media, advertising and analytics partners who may combine it with other information you’ve provided to them or they’ve collected from your use of their services.

Learn more