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):

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!