Plots in Excel

Plots in Excel are charts that show the relationships between two sets of numeric data. It has the horizontal (x) and vertical (y) axis along which the numeric data are represented. Usually, plots are scatter charts showing a relationship between two variables, where the horizontal axis holds the independent variable while the vertical axis houses the dependent variable.

It differs from the line graph in that the line graph has only one value axis, while in a scatter plot, the point plotted is the combined value of values from the x- and y-axis. For instance, we have some gym patrons’ height and weight charts. They were measured a month after joining the gym. As observed below, the independent variable, height, is along the x-axis, and the dependent variable, weight, is along the y-axis. The graph certainly shows that weights are reducing.

Plots in Excel

Plots in Excel have an independent and a dependent variable. We use scatter plots to study the relationship between the two. The x-axis indicates the independent variable, while the y-axis indicates the dependent variable.
Some of the available Excel plots include simple scatter plots, scatter with smooth lines and markers, scatter with straight lines and markers, 3d plots in Excel, etc.
They can show different relationships between the variables, such as strong-weak, linear-non-linear, etc. A straight line shows a strong correlation between them.

How to Make Plots in Excel?

Making plots in Excel is easy and can be done in a few clicks. For example, let us plot a scatter graph to compare the salary vs. expenses of some company employees. First, choose the two columns containing the salary and expenses in the below table.

Step 1: Select the dataset from B3 to C8. Go to

Insert > Charts > Scatter

Step 2: You get a relationship between the salary received and the expenses of the employees. You get an almost linear relationship.

Step 3: To understand the chart, add the axis titles. Right-click on the + sign on the top right corner of the chart. Check the box for Axis Titles.

Step 4: Now, click on the “Axis-Title” boxes and add the appropriate titles.

Step 5: Thus, you get a scatter plot in Excel indicating the relationship between salary and expenses.

Examples

Let us look at Excel plot examples to find the relationship between two variables. Then, we will learn to make scatter plots for different scenarios.

Example #1

Let us look at an example of a negative correlation. We have statistics on the number of hours children spend on social media per week and the subsequent effect on their GPAs. Below is a table representing the same.

Simple Scatter Plot

Step 1: Let us try to plot a scatter chart for the same. For this, select the data in Columns B and C and go to the following path:

Insert tab – Charts – Insert Scatter (X, Y) or Bubble Chart – Scatter.

Step 2: You get a simple scatter plot showing partial linear variables with a negative correlation. Here, as the hours of social media increases, the GPA decreases, but the graph is not precisely linear, showing students’ different abilities.

Step 3: We can change the chart title by double-clicking on it. Next, to get darker markers, click on one of the markers. All of them get highlighted.

On the right side of the worksheet, you get a “Format Data Series” pane.

Step 4: You can change the color under the “Fill” option. Here, we have opted for a darker shade of blue.

Step 5: You obtain a graph as shown.

Thus, the dot plots in Excel are valuable in investigating the relationship between the variables in your data.

Example #2

Scatter Plot with Smooth Lines and Markers

Now that we have seen a simple scatter chart let us look at a plot with smooth lines and markers. Below is a table containing the amount of protein in 100 gms of some protein-rich foods. Let us plot a graph to see their protein content.

Step 1: First, let us select the data on the protein content (Column C).

Go to Insert tab – Charts group – Scatter (X, Y) or Bubble Chart – finally, Scatter with Smooth Lines and Markers.

Step 2: You get a simple graph where the data points are plotted and joined by a smooth line.

Step 3: The curve is plotted along the x-axis representing the different foods. However, we have only eight foods, while the axis has a maximum value of 10.

Click on the axis. You get the “Format Axis” pane on the right.

Adjust the maximum value to eight.

Step 4: Now, the x-axis has a maximum value of 8.

Step 5: To get a plotted chart with a different look, you can go to the Chart Design tab.

Choose any of the styles you like under the Chart Styles group.
You can also change the chart title by double-clicking and typing the text.

Example #3

Scatter Plot with Straight Lines and Markers

Below is a table containing the revenue details of a retail store for 2020-2021 and 2021-2022.

Step 1: Select the data in the table to plot straight lines and markers.

Go to Insert > Charts > Scatter (X, Y) or Bubble Chart > Scatter with Smooth Lines and Markers.

Step 2: The x-axis doesn’t look even, as we need only four options. So, let us adjust the x-axis values. Click on the axis.

In the “Format Axis” pane, set the Minimum and Maximum values under “Axis Options” as zero and five, respectively.

Step 3: Observe the graph. You get plots in Excel joined by straight lines and markers.

Thus, Plots in Excel are an easy choice for displaying and studying trends, forecasts, etc. A scatter with straight lines and markers is used to compare at least two sets of values.

Application

Plots in Excel, unlike line charts, use only markers or dots. Usually, we have an independent and a dependent variable. The primary use of scatter plots is to plot the values of the two variables and compare them.
We have different types of plots like box plots in Excel, plots with straight lines and markers, plots with smooth lines and markers, etc.
A scatter plot helps identify patterns in the plots. For example, the relationship between the variables can be described as strong or weak, linear or non-linear, etc.
If the dots form a straight line, they are highly correlated.
If they form a pattern from the lower left to the upper right, it shows a positive correlation and vice versa.
It is helpful to show the non-linear relationship in data.
Sometimes, it can also be used to study patterns by introducing a third variable. For instance, when looking at the height vs. weight of a group of children, you can also distinguish them according to gender by giving a different color plot for boys and girls.
Adding a trendline shows how strong the relationship between the variables is and if it could increase or decrease.

Important Things to Remember

Scatter charts help find the correlation between large amounts of data.
This graph is easy to plot and helps show non-linear relationships.
The charts’ plots represent not just individual data points but the data pattern as a whole.
A trendline can be added to predict or forecast the relationship between variables.
The flow of the data points can be observed with ease.
A third categorical variable can segregate by gender, geographical location, etc, by using different colored markers.

Frequently Asked Questions (FAQs)

1. How to combine two scatter plots in Excel?

To analyze similar sets of data side by side, we need to combine two graphs. You can create and combine two data sets using the Combo Charts option. It can be found under the following location.

Insert > Charts > Combo.

You can choose to create your own Custom Combo Chart.

2. How to plot multiple scatter plots in Excel?

You should first enter the two series of data in the worksheet. Then, using the first data set, create a scatter plot under Insert > Charts. Now, right-click on the scatter plot, and choose “Select Data.” You can add both series of data here.

3. Can you merge scatter plots in Excel?

There are a few ways to merge two scatter plots in Excel. One is using the Combo Chart options under Insert. Another way to do this is the simple copy-paste method. Copy the first scatter plots into the second graph to merge the scatter plots.

Download Template

This article must help understand the Plots in Excel’s formula and examples. You can download the template here to use it instantly.

Plots-in-Excel Download

Plots in Excel