In this article, you will get a complete overview of the Scatter chart in Excel.
A scatter chart/plot is a graphical representation of data points plotted on a Cartesian plane. So, a scatter plot provides a visual representation of the relationship between two variables.
Scatter plot enables quick identification of patterns and trends. So, it becomes easy to highlight data points that deviate significantly from the overall pattern. Scatter plots help assess the strength and direction of correlations.
Some common applications of it include analyzing sales data to identify relationships, studying the correlation between variables, and investigating the impact of variables on business performance.
Here, we will learn to create a scatter plot and ways to customize the chart and its axes of it.
Download Practice Workbook
How to Make Scatter Chart in Excel
To make a scatter chart/plot in Excel:
- Select your data range > go to the Insert tab > click the drop-down of Insert Scatter (X, Y or Bubble Chart icon under Charts group > choose Scatter.
- This command will insert a scatter chart based on the selected data range.
Types of Scatter Chart in Excel
We have shown the way to insert a normal scatter chart. There are some other types of scatter charts available in Excel:
- Scatter with smooth lines and markers
- Scatter with smooth lines
- Scatter with straight lines and markers
- Scatter with straight lines
When you have a few points of data in Excel, then a scatter chart with lines is the most user-friendly option.
For each type of scatter plot, there are two presentation styles available.
To unlock the styles:
- Go to the Insert tab > click the drop-down of Insert Scatter (X, Y or Bubble Chart icon under Charts group > select More Scatter Charts.
- The Insert Chart dialog box will appear.
- In the X Y (Scatter) tab > you will find two presentation styles for each chart type. You can choose your preferred one from here.
Correlation in Excel Scatter Chart
Correlation between the variables helps to understand how the data are related.
- Positive Correlation: The positive correlation means the proportional relationship between the variables. So, if one of the variables increases here, the other also does.
- Negative Correlation: The negative correlation means the disproportional relationship between the variables. Here, if one variable increases, the other does the opposite.
- No Correlation: In this type, there is no definite relation between the variables. The variables are just scattered through the chart area.
How to Edit Scatter Chart in Excel
1. Change Axis Scale
To change the axis scale:
- Right-click on the Axis title > select the Format Axis option.
- On the right corner of the worksheet window, the Format Axis pane will appear.
- Under the Axis options here, you can change the axis scale by changing the Bounds and Units options.
2. Add Labels to Scatter Chart/Plot
To add labels to the scatter plot:
- Click on the chart area > click Chart Elements button > click the right arrow of the Data labels option > select More options.
- Here, the Format Data Labels pane will appear on the right side of the worksheet window.
- Under the Label Options, put a checkmark on the Value From Cells option in the Label Contains group.
- In the Data Label dialog box, assign the range of labels in the Select Data Label Range field > click OK.
This will insert labels to your data in the scatter plot. You can also replace the position of the labels if they appear so close to one another. Just put your mouse on the label, hold and drag it to replace it according to your desire.
3. Add Trendline to Scatter Plot
To add a trend line to the scatter plot:
- Right-click on any of the dots > select Add Trendline options from the appeared menu.
- Hence Excel will insert a trendline to your scattered data.
How to Switch Axes in Excel Scatter Plot
To switch X and Y axes in a scatter chart/plot:
- Right-click on any axis and click on Select Data from the options.
- Here, the Select Data Source dialog box will appear.
- Click on Edit under Legend Entries (Series).
- Now Edit Series dialog box will appear.
- Exchange the values in the Series X values and Series Y values fields.
- Click OK.
As a result, Excel will switch the X and U axes in the scatter plot.
Frequently Asked Questions
1. What data is suitable for creating a scatter plot in Excel?
Scatter plots are ideal for visualizing numerical data sets with two variables. The variables should be continuous and measured on a scale.
2. Can I create a scatter plot with more than two variables in Excel?
An Excel scatter plot typically shows the relationship between two variables. However, you can use additional visual elements like color or size to represent a third variable. This technique, known as bubble charts, allows for the inclusion of an extra dimension of data in the scatter plot.
3. Can I analyze data using regression analysis with scatter plots in Excel?
Yes, Excel provides regression analysis tools that can be applied to scatter plots. Regression analysis allows you to estimate and model the relationship between variables, including determining the equation of the best-fit line. This feature enables you to make predictions and draw conclusions based on the scatter plot data.
Takeaways from the Article
- Scatter plots help identify patterns, trends, and correlations in data.
- Scatter plots enhance communication by presenting complex information in an easily understandable format.
- You can format the axes in a scatter plot, and add labels and trendlines to data.
- The axes of the scatter plot can also be interchanged.
Conclusion
Throughout the article, we have tried to give you a complete overview of the Excel scatter chart. Hope now you have learned to create a scatter plot and ways to customize the chart and its axes. By utilizing scatter plots, analysts, researchers, and individuals across diverse fields can unlock valuable insights and make necessary decisions.
Scatter Chart in Excel: Knowledge Hub
<< Go Back To Excel Charts |Â Learn Excel
Get FREE Advanced Excel Exercises with Solutions!