Locked lesson.
About this lesson
Create a scatter plot chart in Excel to display a graphical representation of the relationship between two sets of data.
Exercise files
Download this lesson’s related exercise files.
4.06 scatter-plots - Exercise.docx43.9 KB 4.06 scatter-plots - Exercise solution.docx
5.8 MB Exercise - Scatter Plot Chart.xlsx
10.1 KB Exercise - Scatter Plot Chart - Solution.xlsx
14.1 KB Workbook - Scatter Plot Chart.xlsx
10.9 KB Workbook - Scatter Plot Chart - Complete.xlsx
17.3 KB
Quick reference
Scatter Plots
Create a scatter plot chart to show the relationship between two sets of data.
When to use
We use a scatter plot chart as a graphical representation to display the relationship between two sets of data. It helps to visualize how one variable is affected by another.
Instructions
A scatter plot chart is made up of x-axis values and y-axis values. The x-axis represents one variable and the y-axis represents another. Scatter plots are particularly useful for identifying correlations, trends, clusters, and outliers within the dataset.
Create a Scatter Plot Chart
In this example, we are going to use a scatter plot chart to find out if there is a correlation between the amount of time a patient spends with their healthcare provider and their overall satisfaction levels.
- Select the data.
- From the Insert tab, in the Charts group, click the Scatter Plot drop-down.
- Select the first one.
It's clear from this chart that there is definitely a relationship between these two sets of values.
Format the Scatter Plot Chart
We can add the usual formatting to our scatter plot chart such as changing the chart colors and adding a chart title.
Add a Trendline
We could add a trendline to our chart to show the general trend of our data.
- Click the plus to the right of the chart.
- Click Trendline.
- Choose the series you want to add the trendline for.
- Click OK.
Change Axis Scale
- Select the horizontal axis and press CTRL+1.
- Expand Axis Options.
- In the Bounds area, change the Minimum value to 0 and the Maximum value to 32.
- In the Units area change the Major units to 2.
This will position the data on the chart without as much white space at the top or at the side.
Add a Key
We can add a separate key using a text box so readers know that patients were scoring their satisfaction levels on a scale of 1 - 5.
- From the Insert tab, in the Text group, click Text box.
- Add the text '1 = Dissatisfied - 5 = Very Satisfied'.
- Click on the text box and from the Shape Format tab, in the Shape Outline drop-down, select No Outline.
Hints & tips
- Consider removing the outline around the outside of the chart so it blends in more with the spreadsheet.
Sorry, we don’t have a transcript for this lesson yet.
Lesson notes are only available for subscribers.