Do you have columns of quantitative data in your Excel sheet that only consist of a list of figures? How can you visualize and analyze the relationship between this quantitative data at a glance? Through a scatter plot.
Scatter plots are the best when it comes to comparison between two sets of quantitative data. The article that follows will help you learn all about scatter plots in Excel.
Table of Contents
What is a Scatter Plot?
A scatter plot is a two-dimensional graph that pictures the relationship between two variables.
It has two axes; the horizontal axis is known as the X-axis, whereas the vertical axis is known as the Y-axis. For the reason of having these two axes, it is also known as the XY graph.
One of the variables (mostly the independent variable) is plotted against the X-axis and the other variable (mostly the dependent variable) is plotted against the Y-axis.
After the data points are plotted, the relationship between both the variables is depicted through the intersection points of the x and y-axis.
Before we move forward, here’s a sneak peek into what a scatter plot looks like.
When to Use a Scatter Plot in Excel?
Scatter plots are designed to visualize the relationship between two variables. It is best used to show how strongly two variables are related or correlated to each other.
Visually, the closer the data points lie to each other on a scatter plot, the stronger the relationship between them.
A scatter plot will be of great use when two variables are to be compared and the relationship between them is to be studied.
For a quick example, if you want to study the relationship between the price and demand of a product, you may plot a scatter plot with data points for both variables.
This will let you visualize how the demand for a product (the dependent variable) fluctuates in the market as the price of the product (the independent variable) is increased or decreased.
The above image shows the relationship between five data points for both the variables, price, and demand. As the price increases from $100 to $150, the demand for the product falls from 70 units to 60 units. This shows that there exists a negative correlation between both variables.
Scatter plots can be used for many other similar comparisons where quick visual analysis of the data is sought.
How to Create a Scatter Plot in Excel?
Surprising to know, but scatter plots are one of the easiest graphs of Excel in terms of creation. Creating a scatter plot in Excel is all about two steps, both of which are explained hereunder through an example.
The above data represents the promotional expense of a Company along with the resultant sales won by the Company over 5 months. How can this be plotted in a scatter diagram?
Step 1:
Scatter plot creation in Excel begins with organized source data. It is pertinent to note that scatter plots are designed for two-dimensional data sets, only.
As the first step, select the two columns of your quantitative data set i.e. the column for promotional expense and sales.
Step 2:
With both the columns selected, follow the path below to insert a scatter plot.
Insert Tab > Charts > Scatter Plot > Scatter with Smooth Lines and Markers
This inserts a scatter plot to your Excel, as shown below.
In the image above, Excel has converted the underlying dataset for promotional expense and sales into a scatter plot. The plot shows a right-upward trend line that demonstrates a direct relationship between both variables.
As the independent variable on X-axis (the promotional expense) continues to increase, the sales must also increase.
Must know! You can replace the scatter plot type ‘Scatter with Smooth Lines and Markers’ selected above with any other type of scatter chart by selecting a different type of scatter chart. Excel offers 5 different types of scatter charts that are discussed in detail below!
Pro-tip:
It is advisable to plot the independent variable on the X-axis and the dependent variable on the Y-axis for a better analysis of the relationship between them both.
For excel to plot so, the independent variable should be placed as the first column in the data set, and the dependent variable should come next. The example above demonstrates a similar positioning of the numerical columns in the source data.
Types of Scatter Plot
In addition to the smooth line scatter plot in the example above, Excel has 4 other types of scatters plot to offer.
Even with different scatter plot types, the purpose for each type of scatter plot remains the same i.e. to visualize the relationship between two variables. However, you may choose different scatter plot types for different presentation styles.
The 5 types of scatter plots offered by Excel are listed below.
i. Scatter
The first and the most basic type of scatter plot is a simple scatter plot with markers and no lines. This plot only adds markers (dots) to each intersection point of both variables.
ii. Scatter with smooth lines and markers
This type of scatter chart adds a smooth line (also a slightly curved line) that traces the markers placed at each intersection point of both the variables.
iii. Scatter with smooth lines
Similar to ‘Scatter with smooth lines and markers’, this scatter plot type removes the markers from the plot, and only a smooth line draws the relationship between both the variables.
iv. Scatter with straight lines and markers
This type of scatter plot is very similar to the scatter plot with smooth lines and markers, with the only exception that the relationship line in this plot is not smooth or curved but straight and sharp.
v. Scatter with straight lines
Under this type of scatter plot type, the straight line of the relationship between both the variables is not accompanied by markers at the intersection points of both the variables.
Pro Tip:
When deciding about the type of scatter plot that best suits your data set, do not waste time inserting and deleting each different scatter plot type.
Instead, select the data and launch the ‘See all charts’ option by clicking on the small arrow to the right of the Chats section on the Insert tab.
This would open up the chart menu bar. From the chart types in the left pane, select XY (scatter).
Consequently, from all 5 different scatter chart types that appear on the top, click on different types to preview how the chart would look like when plotted.
Adding a Trendline
After we have learned to construct a scatter plot in Excel, it’s time we learn how a trendline can be added to a scatter plot.
A trendline is added to scatter plots to enhance the visualization and analysis of the relationship between the two variables. It connects a series of data points and is also known as the ‘Line of best fist’.
Here’s how you can add it to a scatter plot in Excel.
To add a trendline to the scatter plot above, right-click on any of the markers (the blue little crosses) on the plot. This should open the drop-down menu as shown below. Select the option ‘Add trendline’ from the dropdown menu.
This adds a dotted blue trendline to your scatter plot.
This also opens up the ‘Format Trendline’ window on the right, from where you can choose different formatting options for the trend line.
Under these options, you can customize how the line looks through the color, thickness, and transparency of the line.
You may also add a 3D effect to it through shadows, glow effects, and much more.
Visualization Options
Excel offers 5 different visualization options for scatter plots. These range from Classic Scatter plots to scatter plots with smooth to straight lines with or without markers.
In addition to these, there are further different presentation styles for each chart type.
For example, you may remove the gridlines from your chart, if desired. From the ‘Chart Elements’ button, uncheck the option ‘Gridlines’ to have a blank background for your chart.
Similarly, click anywhere on the chart to launch the ‘Design’ tab in the Ribbon and go to:
Design Tab > Chart Layouts > Quick Layouts
Excel offers 11 different layout options for scatter plots. Here’s how Layout 10 looks when applied to the scatter plot created in the example above.
Customization Options
It’s not over after you have plotted a scatter diagram in Excel. In fact, Excel offers a number of options to users for customizing the scatter plot once created. Some of these customization options are discussed below.
1. Chart Labels
A chart without labels? Even sounds boring.
Once you have constructed a scatter plot, adding chart labels to it is only a matter of a few keystrokes.
- Select the scatter plot by clicking anywhere on it to launch the three icons on the right of the plot.
- Click on the Chart Elements button as shown above. From the list of Chart elements that open up, check the box for ‘Chart Title’. This would bring a small text box to the top of your chart. Click on it to edit it and add the chart title as desired.
- Clicking on the text box for ‘Chart Title’ launches the ‘Format Chart Title’ pane on the right of the worksheet, where you can edit the Chart Title in several ways.
In addition to Chart Title, you may also want to add labels to both the axes.
- To do so, go to the ‘Chart Elements’ button and from the list of ‘Chart elements’ that opens up, check the box for ‘Axes Titles’. This would bring two small text boxes parallel to each of the axes. Click on each box to edit it and add the axis title as desired.
- To launch the ‘Format Axes Titles’ pane, you may launch ‘More options’ by clicking on the small arrow that appears to the right of ‘Axes Titles’ in the Chart Elements drop-down menu.
2. Data Labels
With a few data points, it is often feasible to label the data points for a more appealing presentation of the relationship between both variables.
Here is how you can add data labels to your scatter plot.
- Select the scatter plot by clicking anywhere on it. Upon being selected, click on the Chart Elements button (the plus icon) as shown below.
- From the list of ‘Chart elements’ that opens up, check the box for ‘Data Labels’ and click on the small arrow that appears to the right for more options.
- This would take you to the Format Data Labels pane, wherefrom you can select the labels along with other formatting positions.
- For instance, we want each intersection point in the scatter plot of our example above to be labeled by the month name. We have selected the option ‘value from cells’ and with the cell range containing the month names (A2:A6 in our example) defined.
3. Chart Design
Who says charts are only supposed to be like old monochrome pictures? In Excel, you can customize your scatter plot to different designs and colors.
Simply select the chart and go to the ‘Design’ tab on the Ribbon.
This offers a wide variety of chart designing options to Excel users, where you change the overall chart style, chart colors, chart layout, and much more.
For instance, here’s what the scatter plot looks like after we’ve applied the dark chart style to it.
Bottom Line:
Creating a simple scatter plot in Excel is super easy and practicing the above-explained steps, and pro tips can help you master the creation of scatter plots in Excel in a relatively shorter time. Keep practicing!