A scatter plot or an XY graph is a type of graph used to display and analyze the relationship between two different variables.
The horizontal (x-axis) and vertical (y-axis) axes represent numeric data in a scatter plot. Usually, the independent variable is on the x-axis, while the dependent variable is on the y-axis. The chart shows values at the intersection of the x and y axes, represented as individual data points.
Below is an example scatter plot showing the correlation between sales and profits in a particular organization:
In this tutorial, I will show you how to create, customize, and analyze a scatter plot in Excel and describe the five main scatter plot types supported by Excel.
How to Generate a Scatter Plot in Excel
Make sure to organize your source data correctly before creating a scatter plot in Excel.
Enter the two variables you want to base the graph on in two separate columns, with the independent variable in the left column and the dependent variable in the right column.
Suppose you have created the sales report below and want to generate a scatter plot based on the data:
You can create the scatter plot using the steps below:
- Select the two columns with numeric data, columns B and C, as shown below:
Note: The selection should include the column titles.
- Click the Insert tab, open the Insert Scatter (X, Y) or Bubble Chart drop-down, and click the first icon representing the primary Scatter chart type.
On the Insert Scatter (X, Y) or Bubble Chart drop-down, you will notice that Excel supports five scatter chart types. I will describe the first type (basic Scatter plot) in this section and the other four in the next section.
The basic scatter plot is inserted into the worksheet:
The independent Sales variable is plotted on the horizontal x-axis, and the dependent Profit variable is plotted on the vertical y-axis.
Also read: Calculate Coefficient of Determination in Excel
Types of Scatter Plots in Excel
In addition to the basic scatter plot I have described in the previous section, Excel supports the following four scatter plot types:
- Scatter Plot with Smooth Lines and Markers, suitable for visualizing a small number of data points.
- Scatter with Smooth Lines, ideal for visualizing many data points.
- Scatter with Straight Lines and Markers, suitable for visualizing a small number of data points.
- Scatter with Straight Lines, ideal for visualizing many data points.
The process of creating these scatter chart types is the same as the one shown in the previous section. For instance, suppose you have the sales report below and want to use it to generate a scatter plot with smooth lines and markers:
You can use the steps below to achieve that:
- Select the two columns with numeric data, columns B and C, as shown below:
Note: The selection should include the column titles.
- Click the Insert tab, open the Insert Scatter (X, Y) or Bubble Chart drop-down, and click the second icon representing the Scatter with Smooth Lines and Markers chart type.
The chart is inserted into the worksheet:
Also read: How to Make Box Plot (Box and Whisker Chart) in Excel?
How to Customize a Scatter Plot in Excel
Once you have created the scatter plot, you can customize it by adding or removing chart elements and formatting them to make it more readable and presentable.
When you select a scatter graph and click the Chart Elements button with a cross icon at the top right corner of the chart, a list of all the eight chart elements you can add or remove will be displayed.
You will notice that some elements have a checkmark next to them, indicating that Excel added them to the scatter plot by default. You can remove the elements by unchecking them.
All the chart elements options have options on the submenu that you can use to modify the elements. For instance, if you click the right arrow next to the Axes option, you will see a submenu with options you can use to modify it:
In this section, I will show you how to customize each chart element.
Customize the Axes
Axes are the lines that define the boundaries of the chart’s data or plot area and are used to plot and label the data points. An Excel chart has two main axes: The bottom horizontal axis (x-axis) and the left vertical axis (y-axis).
One helpful customization you can do on the axes is to change their minimum and maximum bounds and their major and minor units.
The minimum bound is the smallest value you can show on the axis, and the maximum bound is the largest value you can display on the axis.
The major and minor units refer to the intervals for labeling and marking the axes. Major units are the primary intervals, while minor units divide the major units into smaller intervals.
Although Excel automatically sets the minimum and maximum bounds and major and minor units depending on the source data, you can modify them. For instance, you can use the steps below to change the minimum and maximum bounds and major and minor units of a scatter plot’s horizontal axis:
- Right-click the values on the x-axis and click Format Axis on the shortcut menu.
The above step will open the Format Axis pane on the right of the Excel window.
- On the Format Axis pane, manually enter the desired minimum and maximum values in the corresponding boxes. Additionally, input the desired major and minor units in the corresponding boxes.
The changes take effect immediately.
You can use the same process above to change the minimum and maximum bounds and major and minor units of the scatter plot’s vertical axis.
You can use the other options on the Format Axis task pane to add other formatting to the axes, such as changing the text direction and line color.
Add and Customize the Axis Titles
The axis titles are the descriptive labels for the chart’s axes. They provide context for the data displayed and help readers understand what the axes represent.
Suppose you have the scatter plot below showing the correlation between sales and profits:
The sales are displayed on the horizontal axis, and the profits on the vertical axis.
You can add titles to the axes using the steps below:
- Select the chart, click the Chart Elements button at the top right of the chart, and select the Axis Titles option.
Notice that the above step adds placeholder ‘Axis Title’ labels to the axes.
Note: If you want to remove the axis titles, uncheck the Axis Titles option.
- Click inside the labels, delete the placeholder titles, and replace them with ‘Sales’ and ‘Profits’ as shown below:
You can make the Axis Titles dynamic by linking them to cells. For instance, if you want the value in cell B1 displayed as the title of the x-axis, do the following:
- Select the x-axis title box.
- Click in the formula bar, type in the equal (=) sign, and select cell B1 (Make sure to mouse-select cell B1 and do not type B1 in the formula bar).
- Press Enter.
You can follow the same process for the y-axis and link its title box to a particular cell.
The axis titles become dynamic, meaning they change according to the values in the linked cells.
You can add a fill color, border, and other formatting to the axis titles using the steps below:
- Right-click the axis title and click Format Axis Title on the shortcut menu.
- You can use the options on the Format Axis Title pane to change the title’s fill color, border, text alignment, etc.
Customize the Chart Title
We usually use the chart title to describe what the chart is all about.
Suppose you have the scatter plot below and want to change the default title to ‘Sales vs. Profit.’
You can change the title by double-clicking the default chart title, deleting it, and replacing it with the new title, in this case, ‘Sales vs. Profit’ as shown below:
You can also make the title dynamic by linking it to a cell.
For instance, you can use the steps below to link the title to cell D10:
- Select the chart title box.
- Click in the formula bar, type in the equal (=) sign, and select cell D10 (Make sure to select cell D10 and do not type D10 in the formula bar).
- Press Enter.
The chart title becomes dynamic, meaning it changes according to the value in the linked cell.
You can add a fill color, border, and other formatting to the chart title using the steps below:
- Right-click the title and click Format Chart Title on the shortcut menu.
- You can use the options on the Format Chart Title pane to change the title’s fill color, border, text alignment, etc.
If your scatter plot does not have a chart title, you can turn it on by selecting the chart, clicking the Chart Elements button at the top right corner, and selecting the Chart Title option.
If you do not want the title, you can deselect the Chart Title option on the Chart Elements list or mouse-select the chart title box and delete it.
Add and Customize Data Labels
Data labels are annotations that offer additional information about specific data points on the chart. By default, data labels do not appear on scatter graphs created in Excel, but you can add them if desired.
To turn on data labels, select the scatter plot, click the Chart Elements button at the top right, and select the Data Labels option:
The data labels display the y-axis value for each data point.
To format the data labels, right-click any of the labels and choose Format Data Labels on the shortcut menu:
The above step will open the Format Data Labels pane on the right of the Excel window.
The pane offers options for applying formatting, such as text color, fill, border, and alignment.
The Format Data Labels pane offers additional options for showing the x-value, series name, etc., on the data labels. For instance, in the ‘Label Contains’ options section, you can select the ‘X Value’ option to display x-values on the data labels. The x-values will be displayed alongside the initial y-values.
You can also choose the ‘Value From Cells’ option to display values from a range of cells in the chart’s data source on the labels. When you select the option, a dialog box opens, allowing you to select the cell range whose values you want shown on the data labels.
For instance, you can choose to show the names of the salespersons on the data labels:
Add and Customize Error Bars
Error bars are visual representations that depict data variability and indicate possible measurement errors.
To add error bars to a scatter plot, select the chart, click the Chart Elements button at the top right corner of the graph, and select the Error Bars option:
The above step displays each data point’s vertical and horizontal error bars.
You can format the error bars by right-clicking one of them and choosing the Format Error Bars option:
The above step opens the Format Error Bars pane on the right of the Excel window.
The pane offers options to customize the error bars’ color, direction, end style, etc.
If you wish to remove the error bars, select the chart, click the Chart Elements button at the top right corner of the chart, and uncheck the Error Bars option.
Add and Format Gridlines
Gridlines are lines that run horizontally and vertically across the graph, intersecting at the major and minor tick marks on the axes. They serve as a reference framework to help interpret the position of data points more easily.
When you generate a scatter plot, Excel adds the major gridlines by default. However, you can customize them by clicking on any of them and choosing the Format Gridlines option:
The above step opens the ‘Format Major Gridlines’ pane on the right of the Excel window.
The pane offers options to change the major gridlines’ formatting, such as thickness and color.
You can also use the pane’s options to display the chart’s minor vertical and horizontal gridlines. The minor gridlines enhance the chart’s readability and precision.
Here’s how to add the minor gridlines:
- Select the scatter plot.
- Click the Chart Elements button at the top right.
- Click the right arrow next to the Gridlines option.
- Select the submenu’s ‘Primary Minor Horizontal’ and ‘Primary Minor Vertical’ options.
To format the minor gridlines, right-click any of them and click Format Gridlines on the shortcut menu.
The above step opens the Format Minor Gridlines pane on the right of the Excel window:
The pane offers options to change the minor gridlines’ formatting, such as thickness and color.
Add and Customize the Legend
The legend on a chart is a key or guide that explains the symbols, colors, or patterns used in the chart to represent different data series, categories, or variables.
By default, Excel does not include a legend when generating a scatter plot.
To add a legend to a scatter plot, select the chart, click the Chart Elements button at the top right, and select the Legend option.
To change the legend’s position on the chart, click the arrow next to the Legend option and choose an option on the submenu.
To format the legend, right-click it and choose Format Legend on the shortcut menu.
The above step will open the Format Legend pane on the right of the Excel window.
Notice that you can also use the options on the pane to change the legend’s position on the chart. Additionally, you can use other options on the pane to format the legend’s text color, fill color, text orientation, and other features.
Add and Customize a Trendline
A trendline represents the general direction of the chart’s data points. It helps visualize the relationship between two variables on a chart, making it easier to analyze and interpret the data.
Suppose you have generated the scatter graph below and want to add a trendline:
To add a trendline to the scatter plot, select the chart, click the Chart Elements button at the top right of the chart, and select the Trendline option:
The above step adds a linear trendline to the graph.
Note: If you want to remove a trendline from the chart, uncheck the Trendline option.
To format the trendline, right-click it and choose Format Trendline on the shortcut menu.
The above step opens the Format Trendline pane on the right of the Excel window.
You can use the options on the pane to format the trendline’s color, weight, and other features.
One of the helpful features you can add to the trendline is an equation. You can add an equation to the chart by selecting the ‘Display Equation on chart’ at the bottom of the pane.
The equation is displayed on the chart:
Types of Trendlines in Excel
Notice that Excel supports six types of trendlines:
Here is the explanation of each type of trendline:
- The Exponential trendline is appropriate for data that increases or decreases exponentially, but it is unsuitable for data with negative or zero values.
- The Linear trendline is most suitable for datasets with a linear relationship, where the data points create a pattern that closely resembles a straight line. Excel also offers the Linear Forecast trendline, which extends the Linear trendline beyond the actual data points to predict future values.
- The Logarithmic trendline is suitable when the data change rate increases rapidly and then levels out.
- The Polynomial trendline is ideal for data with multiple fluctuations.
- The Power trendline is suitable for data that follows a pattern of increasing or decreasing at a multiplicative rate.
- The Moving Average trendline smooths out fluctuations in data to show a pattern or trend more clearly. It helps smooth out short-term fluctuations and highlight longer-term trends or cycles.
Also read: How to Interpolate in Excel
How to Analyze and Interpret a Scatter Plot
Interpreting a scatter plot involves analyzing the relationship between two variables represented on the x-axis and y-axis.
If the values on the x-axis and those on the y-axis increase, the variables have a positive correlation. The variables have a negative correlation if the values on the x-axis increase and those on the y-axis decrease.
The variables have no correlation if the data points are scattered randomly and show no discernible pattern.
If the data points form a tight cluster around a line, it indicates a strong relationship between the variables. If the points are widely scattered or show no clear trend, it suggests a weak relationship between the variables.
Example Analysis and Interpretation of a Scatter Plot
Suppose you have the following scatter plot with the data points representing sales on the x-axis and profits on the y-axis.
The data points trend upwards, indicating that higher sales are associated with higher profits, while lower sales are associated with lower profits. The variables have a strong positive correlation.
You cannot tell how much the profits will increase when sales go up by, for instance, 80% just by looking at the trendline. However, you can use the CORREL function to calculate the correlation coefficient between two data sets.
You can use the following formula to calculate the correlation coefficient between sales and profits shown in the scatter plot:
=CORREL(B2:B11,C2:C11)
The correlation coefficient ranges from -1 to +1, with +1 indicating a perfect positive correlation and -1 indicating a perfect negative correlation.
In our example, the correlation coefficient is 0.911526, meaning there is a high positive correlation between the sales and profits variables.
In this tutorial, I showed you how to create and customize a scatter plot in Excel and how to interpret and analyze it. I hope you found the tutorial helpful.
Other Excel articles you may also like:
