Add vertical line to Excel chart: scatter plot, bar and line graph

This tutorial demonstrates how to add vertical lines to Excel charts, including scatter plots, bar charts, and line graphs. You'll also learn to create an interactive vertical line controlled by a scroll bar. While Excel easily adds horizontal lines, vertical lines require a workaround.

• Adding Vertical Lines to Scatter Charts
• Inserting Vertical Lines into Bar Charts
• Adding Vertical Lines to Line Charts
• Creating an Interactive Vertical Line with a Scroll Bar

Adding a Vertical Line to a Scatter Plot

To highlight a data point on a scatter chart, create a vertical line marking its x-axis (or x and y axes) position. The line will dynamically adjust to data changes.

Here's how:

1. Create a scatter plot from your data.
2. In separate cells, calculate the vertical line's data. For an average line, use the AVERAGE function for x and y values (example shown below):

3. Right-click the chart and select "Select Data...".
4. Click "Add" under "Legend Entries (Series)".
5. In "Edit Series," name the series (e.g., "Average"), select the x-value and y-value cells for your data point. Click "OK" twice.

6. Select the new data point and add percentage error bars ("Chart Elements" > "Error Bars" > "Percentage").
7. Right-click the vertical error bar, select "Format Error Bars...", set "Percentage" to 100, and choose the direction ("Both," "Minus," etc.).

8. Adjust the horizontal error bar (set percentage to 0 to hide, or 100 to show).
9. Customize the line's color, dash type, and width ("Fill & Line" tab).

The resulting vertical line will appear as configured.

Adding a Vertical Line to a Bar Chart

To compare values against an average or target, add a vertical line to a bar chart:

Follow these steps:

1. Create a bar chart from your data.
2. In empty cells, input data for the vertical line (example below, using AVERAGE for the x-value):

X	Y
=AVERAGE($B$2:$B$7)	0
=AVERAGE($B$2:$B$7)	1

3. Right-click the chart, select "Select Data...", and click "Add".
4. Name the series, select the x-values, and click "OK" twice.
5. Change the series chart type to "Combo" (Excel 2013 ) or "X Y (Scatter)" > "Scatter with Straight Lines" (Excel 2010 and earlier).
6. Re-select data, edit the series, selecting both X and Y values.
7. Format the secondary y-axis (set maximum to 1, hide labels).

Adding a Vertical Line to a Line Chart

Use either method above to add a vertical line to a line chart.

Creating an Interactive Vertical Line with a Scroll Bar

To make the vertical line interactive, use a scroll bar:

1. Enable the Developer tab.
2. Insert a scroll bar ("Developer" > "Insert" > "Scroll Bar").
3. Format the scroll bar, linking it to a cell (e.g., D5), setting the maximum value to the number of data points.
4. Update the x-value cells for the vertical line to reference the scroll bar's linked cell (=$D$5). Optionally, use =IFERROR(INDEX($A$2:$A$7, $D$5, 1), "") to display the corresponding data label.

Download the sample workbook for hands-on practice.

The above is the detailed content of Add vertical line to Excel chart: scatter plot, bar and line graph. For more information, please follow other related articles on the PHP Chinese website!