In this tutorial, I am going to share with you 3 practical examples to show how to add lines to an Excel scatter plot. Lines may be needed to show any threshold or limit within your data. They are also very useful in statistical analysis. Using lines properly can be very helpful in representing data.
How to Add Line to Scatter Plot in Excel: 3 Practical Examples
To begin with, generate a scatter plot with the data set I have provided you. Once you have your scatter plot ready, you can now follow along.
1. Add a Vertical Line to the Scatter Plot in Excel
We will add a vertical line to the following data set. This data set has an X column and a Y column.
- First of all, right-click on the scatter chart and click on Select Data.
- Next, in the Select Data Source window, click on Add.
- Then, in the Edit Series window, set Vertical Line as the Series name.
- Furthermore, select cell B8 as Series X values and cell C8 as Series Y values.
- Press OK and this will generate a new data called Vertical Line.
- Now, keeping the orange data point selected, go to Chart Design > Add Chart Element > Error Bars > Percentage.
- Here, you will get a horizontal and a vertical line around the selected data point.
- Moreover, right-click on the horizontal line and select Format Error Bars.
- After that, in the new window on the right side, set the horizontal line Percentage value to 0.
- As shown previously, right-click on the vertical line of the orange point.
- In the Format Error Bars window, set the direction to Minus.
- Then, in the error amount option, set the Percentage value to 100%.
- Finally, you have your vertical line through your desired data point.
2. Insert a Horizontal Line to Scatter the Plot
The first few steps of this method are exactly the same as the previous method. This time we will add a horizontal line to the scatter plot, again using the error bars option. I will also show you how to format your line to make it more visible.
- Just as we did previously, this time right-click on the vertical line and select Format Error Bars.
- Again, set the Percentage value to 0 and press Enter.
- Now, right-click on the horizontal line and go to Format Error Bars.
- Then, set the direction to Minus.
- After that, set the Percentage value to 100%.
- Now, go to the Fill and Line options.
- Then, set the color to Red and the width to 1 pt.
- Finally, you will have a formatted horizontal line.
3. Adding Slope Line to Scatter Plot in Excel
In the previous two methods, we used only one point from our dataset to add a line to the scatter plot. In our third method, we are going to use two points to add a sloping line. The slope lines are very important for regression analysis. Let us see how we can add a slope line.
- In the beginning, right-click on the scatter plot and choose Select Data.
- Now, in the new Select Data Source window, click on Add.
- In the Edit Series window, enter the series name Slope Line.
- Then, for the series X values, hold Ctrl and select cells B5 and B8.
- After that, for series Y values, hold Ctrl and select cells C5 and C8.
- Now, simply press OK.
- Now, we have a new data named Slope Line.
- Now, select the two data points and right-click.
- Here, select Change Series Chart Type.
- After that, a new window opens, go to the Combo option.
- Click the dropdown arrow beside the Slope Line option.
- From the dropdown options, select the icon with the name Scatter with Straight Lines.
- Now, press OK.
- Finally, a slope line through the selected data points will be added.
Download Practice Workbook
You can download the practice workbook from here.
I hope that you followed all the above steps with me and were able to add different types of lines to the scatter plot in Excel. You can take these examples that I showed you and modify them to suit your own dataset. Try changing the orientation of the lines and adding more data points. To learn more Excel techniques, follow our ExcelDemy website. If you have any queries, please let me know in the comments.