While working with Microsoft Excel, you may need to visualize your data using scatter plots. In fact, Excel allows you to do this effortlessly. Granted this, the following article shows how to connect dots in Scatter Plot in Excel. In addition, it also shows how to plot a trendline in a Scatter Plot.
What Is a Scatter Plot and Its Uses?
A Scatter Plot is a graph that represents the relationship between two variables. Generally speaking, the independent variable is plotted on the horizontal (x) axis, while the dependent variable is plotted on the vertical (y) axis.
Scatter Plots are especially handy when it comes to data analysis since:
- It helps explain the trend of the dataset.
- It allows us to see the maximum and the minimum values of the dataset.
- It helps us understand if the relationship between the variables is linear or non-linear.
In this illustration, the scatter plot shows that Temperature and Speed of air have a linear relationship.
How to Connect Dots in Scatter Plot in Excel: with Easy Steps
Connecting dots in a Scatter Plot involves some easy steps. In the following steps, I will show you how to connect dots in a Scatter Plot in Excel. So, without further delay, let’s dive in!
Let’s say we have the following dataset shown in the B4:D13 cells below. The dataset shows the Month number, the Marketing Expense, and the Revenue in USD respectively.
📌 Step 01: Add a Series to the Scatter Plot
- Firstly, select the D4:D13 cells which is the Revenue column.
- Next, hold down the CTRL key and select the B4:B13 cells which refer to the Month column.
- Then, go to the Insert > Insert Scatter (X,Y) or Bubble Chart.
- Now, choose the Scatter chart from the drop-down.
Read More: How to Add Multiple Series Labels in Scatter Plot in Excel
📌 Step 02: Add a Second Series in the Scatter Plot
- Secondly, select the chart and right-click on the mouse.
- Then, choose the Select Data option.
- Now, press the Add button to enter a new series.
- Next, in the Edit Series dialog box, click the Select Range button.
- Following, select the C4 cell which indicates the Marketing Expense.
- Likewise, choose the values for the X and Y axes and click OK.
That’s it you’ve added a second series to your Scatter Plot.
- Finally, click OK to close the Select Data Source wizard.
📌 Step 03: Add a Legend to the Scatter Plot
- Thirdly, select the chart and go to Chart Element > Legend.
- Now, from these options, select a location for the Legend. In this case, we choose Top.
📌 Step 04: Connect the Dots and Add Data Labels
- Fourthly, select the chart and right-click on the mouse.
- In turn, choose the Change Chart Type option.
- Next, click on the Scatter with Straight Lines and Markers option and press OK to close the dialog box.
📌 Step 05: Add Data Labels
- In the fifth step, go to Chart Element > Data Labels.
- Now, from this list choose the position for the Data Labels, for instance, we chose Above.
Consequently, your results should look like the picture shown below.
How to Add a Trendline in Scatter Plot in ExcelÂ
As an alternative, you may add a Trendline instead of connecting the dots. Fortunately, the process is remarkably similar to the previous method. So, let’s see it in action.
Here, we want to understand the relationship between Marketing Expenses and the Revenue it generates. So, we can insert a trendline to see how well it fits our dataset.
📌 Step 01: Insert a Scatter Plot
- Firstly, select the C4:D13 cells which are the Marketing Expense and Revenue columns respectively.
- Then, go to the Insert tab and select Insert Scatter (X,Y) or Bubble Chart.
📌 Step 02: Format the Axes of the Scatter Plot
- Secondly, select the chart and go to Chart Element > Axes > More Options.
- Now, you can specify the Minimum value for the X-axis, for instance, we chose 40.
📌 Step 03: Add the Trendline to the Scatter Plot
- Thirdly, go to Chart Element > Trendline > More Options.
- Now, our data looks approximately linear so we chose the Linear trendline option.
Admittedly, we have skipped the application of other types of trendlines which you may explore in this article if you wish.
- Following, you can enter a name identifying the trendline as shown below.
- Additionally, we can also change the Color and the Dash type of the trendline.
- Finally, after completing all the steps, you will get your results as shown in the below screenshot.
Download Practice Workbook
You can download the practice workbook from the link below.
Conclusion
I hope this article helped you understand how to connect dots in Scatter Plot in Excel. If you have any queries, please leave a comment below. Also, if you want to read more articles like this, you can visit our website ExcelDemy.
Related Articles
- How to Make a Categorical Scatter Plot in Excel
- How to Create Scatter Plot Matrix in Excel
- How to Create Multiple Regression Scatter Plots in Excel
- How to Create Dynamic Scatter Plot in Excel
- How to Combine Two Scatter Plots in Excel
- How to Create a 3D Scatter Plot in Excel
- How to Create Heat Map Scatter Plot in Excel
- How to Create Clustered Scatter Plot in Excel
<< Go Back To Scatter Chart in Excel | Excel Charts |Â Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
