A scatter plot in MS Excel is also known as an XY chart. A Scatter plot represents the relationship between two separate sets of numbers.
The horizontal and vertical axis comprises numerical data only, but can also represent date values. The data in the left column appears on the X-axis and the data in the right column appears on the Y-axis.
When to use a scatter plot:
- With two sets of numerical data.
- To determine the relation between the two variables.
- To include date and hour values in your source data.
- To show a non-linear pattern.
This tutorial will guide you through the steps to create and customize a scatter plot in Microsoft Excel.
Method: How to Create a Scatter Plot in MS Excel
In this example exercise, we will create a scatter plot to display the relationship between cost and profit.
Step #1: Create a dataset
Open Microsoft Excel.
Open an existing spreadsheet or create a new sheet.
In our example, the illustration above displays data in four columns.
Enter the data as shown in the image above.
This will be our source data to create a Scatter Chart.
Select the values in columns C and D:
Click with your mouse on C3. While holding the mouse button down, drag your cursor to D3 and then to the bottom of the column (Cell range C3:D12).
Step #2: Create a scatter chart
In the main menu bar, click on the Insert tab.
On the Insert ribbon, navigate to the Charts group.
Click the Insert Scatter (X, Y) icon to display the many graph options.
Tip: Hover with your mouse over the icons to see their names.
In our example, we will select the first option -Scatter.
When you click on the selected option, Excel creates the scatter chart as shown below.
Excel plots the left column values (Cost in column C) on the X-axis and right column values (Profit in column D) on the Y-axis.
The blue dots on the chart are the points where the values of Cost and Profit intersect on the X-axis and the Y-axis.
The variables in column C are independent values. The variables in column D are dependent values. It means the profit is dependent on the cost.
Step #3: Interpret the chart
Let us interpret the scatter chart.
The illustration above shows the cost-profit relationship for the product – Orchids. The cost value is 2700 and the profit value is 900.
The chart also helps interpret the correlation between the values.
- A positive correlation is when the Y variable and X variable increase together.
- A negative correlation is when the Y variable decreases and the X variable increases.
- When there is no clear relationship between two variables, there is no correlation.
The plot is dynamic. It means when you edit a value in the data table, it is reflected in the chart.
Step #4: Re-create the chart with a new value
Let us change the cost of the product – Orchid from 2700 to 1900.
Create the scatter plot with the new value. Your chart will look like the illustration below.
Notice the change in profit value (dependent value) in column D and the corresponding change in the position of the dot in the chart. You can edit the values anytime to meet your needs.
Now that the chart is ready, let us customize it to make it easier to read and understand.
Customizing the Scatter Plot
In this section, we will focus on the customizations specific to a scatter chart. Each element in the scatter plot in MS Excel is customizable. The following are all optional enhancements.
Option #1: Reposition the chart
Click anywhere in the empty space of the chart area, hold the left mouse button, and drag the whole chart to a new location on the sheet.
In this example, the chart is re-positioned below the data table.
Option #2: Move the chart
Click on the chart. You will see the menu ribbon changes to Chart Design.
In the Chart Design ribbon, and click on Move Chart.
A Move Chart dialog box will open.
Click the radio button:
- New sheet to move the chart to a new sheet.
- Object in to move the chart to an existing sheet in the workbook.
Specify the location.
Click on the OK button.
In this example, the chart is moved to a new tab – Chart 1.
Option #3: Change the chart view
You can Zoom in or Zoom out the chart to match your needs.
To Zoom in or out of the chart, click the View tab in the main menu.
From the Zoom group click the option to match your needs.
Or, you can move the slider at the bottom right corner of the screen to increase or decrease the zoom level.
Option #4: Change the chart size
Click on any small circle in the corners of the chart area.
Adjust the size by dragging the small empty circle to the desired size.
Note: You can change the frame size, as well as the chart size in the frame using the same method.
Option #5: Reduce the white space in the chart
To make the chart easier to read, we can reduce the white space by adjusting the axis scale.
Before we proceed, check the data table and the chart.
In column C, the smallest value is 250 (C8).
In the chart below the data table, check the position (space) of the first data point and the vertical axis.
To reduce the space between the first data point and the vertical axis, click right on the X-axis.
A right click context menu will open.
Select Format Axis…
The Format Axis pane will open.
Change the Minimum and Maximum bounds. By default, Excel sets these values.
For this example, we will change the smallest value to 200.
When you add a new minimum value, Excel simultaneously updates the maximum value.
Notice the change in the location of the first data point. The white space between the data point and the vertical axis reduces.
You can edit the Major and Minor units to control the spacing between the gridlines. For this example, we will keep the default space.
Option #6: Add chart elements
To add chart elements, click on the chart.
Blocks will appear in the upper corner.
Click the 
Or
Click the Chart Design tab in the main menu.
Click the Add Chart Element icon on the left of the ribbon.
The illustration below displays the chart with the elements selected.
Option #7: Change chart design and color
To change the chart design, click on the chart.
Blocks will appear in the upper corner.
Click the 
Click the Style tab to scroll up or down and select a chart style from the dropdown list.
Click the Colors tab to change the chart theme color.
Or
Click the Chart Design tab in the main menu.
Click on the Change Colors icons on the ribbon to select the color palette.
To further customize these colors, click on ‘How do I change these colors?’ at the bottom of the color pane.
This will display the corresponding tooltip.
Read the contents, and change the theme colors as per your needs.
The following illustration displays the change in color and style.
Option #8: Add a trendline
Adding a trendline to a scatter chart is optional.
But it will help you understand the trend, including ups and downs in data.
Select the scatter chart by clicking on it.
In the top menu bar, click on the Chart Design tab.
At the top left corner of the Charts Layout group, click the Add Chart Elements icon.
From the drop-down options, hover your mouse over Trendline. This will display the trendline options.
Select Linear.
Excel adds a line close to all the data points so that there are an equal number of points above and below the line.
For details on Trendline and how to format it, refer to the article, How to Add a Trendline in MS Excel.
Option #9: Switch X and Y axis
If your plot seems different from what you expected, you can switch the X and Y data series on the chart.
For example, you copy data from a different source. You realize the dependent data is in the left column and the independent data is in the right column. You can always switch the X-axis or the Y-axis to plot the correct chart.
Click on the scatter chart.
Right-click with your mouse on the X-axis or the Y-axis.
From the pop-up list, choose Select Data...
The Select Data Source dialog box displays the vertical values (Series), it is the Y-axis (Profit).
The right side displays horizontal values (Category), it is the X-axis (Cost).
Click the Edit tab on the left.
In the Edit Series box, you can see:
- Series X values range “=Sheet2!$C$3:$C$12”
- Series Y values range “=Sheet2!$D$3:$C$12”
To switch the X-axis and the Y-axis, you must swap the series as under:
- Series X values range “=Sheet2!$D$3:$D$12”
- Series Y values range “=Sheet2!$C$3:$C$12”
Once done, in the Edit Series dialog box, click the OK button.
In the Edit Data Source dialog box, click the OK button.
Excel plots the chart with the new X-axis and the Y-axis.
For more details, refer to the article How to Switch X and Y Axis in MS Excel.
Conclusion
Use a scatter plot to visually represent the relationship between sets of data. It is easy to plot and observe the patterns. It aids in tracking trends and patterns of different measures.
Before starting, identify the dependent and independent variables, and the correlation that you wish to show.
There are many options to change the scatter plot. Make it visually appealing and easy to understand.