Scatter Chart in Excel (All Things You Need to Know)

Get FREE Advanced Excel Exercises with Solutions!

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.

scatter chart in excel


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.

Scatter option from Insert tab

  • This command will insert a scatter chart based on the selected data range.

scatter chart in excel


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.

Scatter with smooth lines and markers

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.

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.

X Y (Scatter)


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.

Positive correlation

  • Negative Correlation: The negative correlation means the disproportional relationship between the variables. Here, if one variable increases, the other does the opposite.

Negative correlation

  • No Correlation: In this type, there is no definite relation between the variables. The variables are just scattered through the chart area.

No correlation


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.

Select 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.

Format Axis 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.

More options for Data Labels

  • 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.

Data label range

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.

Data labels in scatter plot


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.

Add trendline

  • Hence Excel will insert a trendline to your scattered data.

Trendline in Scatter plot


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.

Select Data option

  • Here, the Select Data Source dialog box will appear.
  • Click on Edit under Legend Entries (Series).

Select Data Source dialog box

  • Now Edit Series dialog box will appear.
  • Exchange the values in the Series X values and Series Y values fields.
  • Click OK.

Edit Series dialog box

As a result, Excel will switch the X and U axes in the scatter plot.

Swapped X and Y axes


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 ChartsLearn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.

Tags:

Rafiul Hasan
Rafiul Hasan

Rafiul Hasan, holding a BSc in Naval Architecture and Marine Engineering from Bangladesh University of Engineering & Technology, contributes significantly to the ExcelDemy project with almost 1.6 years of dedicated work. Currently an Excel and VBA Content Developer, he has a passion for problem-solving. Authoring over 100 articles for ExcelDemy showcases expertise in Microsoft Office Suites and Data Analysis. In addition to content development, Rafiul actively engages with the ExcelDemy forum, offering valuable solutions to user queries and... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo