Mastering Scatter Charts in Excel

General
Written by: Renee Chambers
Mastering Scatter Charts in Excel

If you just landed on this article, you probably already know what a scatter chart means. However, creating one is more challenging than it seems, partly due to the numerous and often confusing options packed into the software. Getting started can be a daunting task for people who are new to Microsoft Excel. The first step to using Excel’s vast toolset and functionality is to know what you want to achieve. This article explains a scatter chart and how you can create one to help you get started on your Excel journey.

What is a scatter plot?

A scatter chart or plot also referred to as an XY chart, is a two-dimensional chart type available in Excel used to determine whether two variables are correlated. These variables can be either dependent variables or independent variables. In this type of chart, two different variables are plotted on the X and Y axis of the graph (horizontal axis and vertical axis), and the resulting pattern can be interpreted to determine the relationship between the two variables if any.

The scatter plot can be read as follows:

  • If the data points appear to fall on the same line in the graph, there is a strong correlation between the two variables.
  • If the plotted dots are sloping from the bottom left to the top right, then there is a positive correlation between the two variables.
  • Plotted points slopping from the top left to bottom right indicate a negative relationship between the variables.
  • Data points spread wide apart signify a weak relationship between the variables.
  • If the data labels on the graph seem random, then there is no relationship between the two variables.

Some scatter charts use a trendline to make patterns more apparent. Although you can create a scatter plot with connected lines, a scatter plot and a line chart/line graph are two different tools

How do you create a scatter plot in excel?

These are the simple steps to making your own Excel chart.


1. Select the numeric values that you are investigating.


2. Find the Insert tab and select the XY chart command function.


3. Choose the Chart type that does not have lines. Excel will display the data in an XY chart.


4. Check the graph data organization.

Make sure that the data series you are working with is sorted correctly in the chart. If the data is not arranged properly, you can open the Chart Tools Design context menu and use the Switch Row/Column function to organize the data correctly.

5. Add notes if necessary.

Add more details to your chart to make it more presentable. Make use of the Axis Title and Chart Title functionality to add horizontal/vertical axis titles and descriptions of chart elements in your scatter graph.

6. Add a trendline.

A trendline can help you to interpret the results more easily. Select the Design tab on Excel, then open the Add Chart Element menu to choose the trendline or linear regression graph calculation you want to use. For example, use the Linear button to calculate the linear regression and plot area.

7. Add the Regression Equation to your scatter diagram.

Go to the More Trendline Options function in the Trendline menu to display the mathematical equation for the trendline in your scatter chart. Then pick both Display Equation and Display R-Squared Value on Chart in the checkboxes. This selection commands Excel to add the regression equation relevant to the trendline to your chart. You can use elements in the Format Trendline menu to manipulate the calculation of the regression analysis trendline. You can adjust the trendline forwards or backward by pressing the Forecast Forward and Forecast Backward buttons.

Scatter plot uses

Learning how to make scatter plots in Excel. Some of the primary uses of a scatter plot are:

  • Data presentation. A scatter plot makes it easy to arrange, display, and explain data. Better data visualization makes it easier to interpret the data correctly.
  • Discover data clusters. A scatter chart can show you new data clusters you were unaware of. A data cluster refers to a group of data that is related to each other.
  • Easy chart making. Excel makes it easier to generate charts than manually creating them. It is also easier to share your data with people on your team.
  • Better decision-making. An Excel scatter plot can help you improve data analysis, leading to better business decisions