A Scatter (XY) Plot has points that show the relationship between two sets of data.

In this example, each dot shows one person's weight versus their height.

Scatter plot maker. Create xy graph online. This website uses cookies to improve your experience, analyze traffic and display ads. For a single-plot, the XY graph expects an input of a bundled X array (the top input) and Y array (the bottom input). The Bundle function (ProgrammingCluster & Variant palette) combines the X and Y. We will make a Bar-XY chart type, using an XY chart type (a/k/a Scatter chart type) to position markers. Here is the new data needed for our Bar-XY combination chart. The factor labels and Target values will be used by the Bar chart series, and the Actual values and Heights for the XY series. Create charts and graphs online with Excel, CSV, or SQL data. Make bar charts, histograms, box plots, scatter plots, line graphs, dot plots, and more. Free to get started! Format Graph dialog - XY graphs. Use this dialog to change the look of all the symbols in a data set. If you want to change the look of a single data point, use the right-click menu. You can also change all the symbols in selected data sets. Double-click on any graph symbol or bar to open the Format Graph dialog. To change the settings for one.

(The data is plotted on the graph as 'Cartesian (x,y) Coordinates')

Example:

The local ice cream shop keeps track of how much ice cream they sell versus the noon temperature on that day. Here are their figures for the last 12 days:

And here is the same data as a Scatter Plot:

It is now easy to see that warmer weather leads to more sales, but the relationship is not perfect.

Line of Best Fit

We can also draw a 'Line of Best Fit' (also called a 'Trend Line') on our scatter plot:

Try to have the line as close as possible to all points, and as many points above the line as below.

But for better accuracy we can calculate the line using Least Squares Regression and the Least Squares Calculator.

Example: Sea Level Rise

Interpolation and Extrapolation

Interpolation is where we find a value inside our set of data points.

Here we use linear interpolation to estimate the sales at 21 °C.

Extrapolation is where we find a value outside our set of data points.

Here we use linear extrapolation to estimate the sales at 29 °C (which is higher than any value we have).

Careful: Extrapolation can give misleading results because we are in 'uncharted territory'.

As well as using a graph (like above) we can create a formula to help us.

Example: Straight Line Equation

We can estimate a straight line equation from two points from the graph above

Let's estimate two points on the line near actual values: (12°, $180) and (25°, $610)

First, find the slope:

Now put the slope and the point (12°, $180) into the 'point-slope' formula:

y − y₁ = m(x − x₁)

y − 180 = 33(x − 12)

y = 33(x − 12) + 180

y = 33x− 396 + 180

y = 33x− 216

INTERpolating

Now we can use that equation to interpolate a sales value at 21°:

Plot Xy Graph Excel

y = 33×21° − 216 = $477

EXTRApolating

And to extrapolate a sales value at 29°:

y = 33×29°− 216 = $741

The values are close to what we got on the graph. But that doesn't mean they are more (or less) accurate. They are all just estimates.

Don't use extrapolation too far! What sales would you expect at 0° ?

y = 33×0° − 216 = −$216

Hmmm... Minus $216? We extrapolated too far!

Note: we used linear (based on a line) interpolation and extrapolation, but there are many other types, for example we could use polynomials to make curvy lines, etc.

Correlation

When the two sets of data are strongly linked together we say they have a High Correlation.

The word Correlation is made of Co- (meaning 'together'), and Relation

• Correlation is Positive when the values increase together, and
• Correlation is Negative when one value decreases as the other increases

Like this:

(Learn More About Correlation)

Negative Correlation

Correlations can be negative, which means there is a correlation but one value goes down as the other value increases.

It has a negative correlation (the line slopes down)

Note: I tried to fit a straight line to the data, but maybe a curve would work better, what do you think?

In this tutorial, you will learn how to do a scatter plot in Excel to create a graphical representation of two correlated data sets.

When looking at two columns of quantitative data in your Excel spreadsheet, what do you see? Just two sets of numbers. Do you want to see how the two sets are related to each other? The scatter plot is the ideal graph choice for this.

• Customizing scatter plot

Scatter plot in Excel

A scatter plot (also called an XY graph, or scatter diagram) is a two-dimensional chart that shows the relationship between two variables.

In a scatter graph, both horizontal and vertical axes are value axes that plot numeric data. Typically, the independent variable is on the x-axis, and the dependent variable on the y-axis. The chart displays values at the intersection of an x and y axis, combined into single data points.

The main purpose of a scatter plot is to show how strong the relationship, or correlation, between the two variables is. The tighter the data points fall along a straight line, the higher the correlation.

How to arrange data for a scatter chart

With a variety of inbuilt chart templates provided by Excel, creating a scatter diagram turns into a couple-of-clicks job. But first, you need to arrange your source data properly.

As already mentioned, a scatter graph displays two interrelated quantitative variables. So, you enter two sets of numeric data into two separate columns.

For ease of use, the independent variable should be in the left column as this column is going to be plotted on the x axis. The dependent variable (the one affected by the independent variable) should be in the right column, and it will be plotted on the y axis.

In our example, we are going to visualize the relationship between the advertising budget for a certain month (independent variable) and the number of items sold (dependent variable), so we arrange the data accordingly:

How to create a scatter plot in Excel

With the source data correctly organized, making a scatter plot in Excel takes these two quick steps:

1. Select two columns with numeric data, including the column headers. In our case, it is the range C1:D13. Do not select any other columns to avoid confusing Excel.
2. Go to the Inset tab > Chats group, click the Scatter chart icon, and select the desired template. To insert a classic scatter graph, click the first thumbnail:

The scatter diagram will be immediately inserted in your worksheet:

Basically, you may consider the work done. Or, you can customize some elements of your graph to make it look more beautiful and to convey the correlation between the two variables clearer.

Scatter chart types

Besides the classic scatter plot shown in the above example, a few more templates are available:

• Scatter with smooth lines and markers
• Scatter with smooth lines
• Scatter with straight lines and markers
• Scatter with straight lines

Scatter with lines is best to be used when you have few data points. For example, here's how you can represent the data for the first four months by using the scatter graph with smooth lines and markers:

The Excel XY plot templates can also draw each variable separately, presenting the same relationships in a different way. For this, you should select 3 columns with data - the leftmost column with text values (labels), and the two columns with numbers.

In our example, the blue dots represent advertising cost, and orange dots represent the items sold:

To view all available scatter types in one place, select your data, click the Scatter (X, Y) icon on the ribbon, and then click More Scatter Charts… This will open the Inset Chart dialog box with the XY (Scatter) type selected, and you switch between the different templates at the top to see which one provides the best graphic representation of your data:

3D scatter plot

Unlike a classic XY scatter chart, a 3D scatter plot displays data points on three axes (x, y, and z) in order to show the relationship between three variables. Therefore, it is often called an XYZ plot.

Regrettably, there is no way to create a 3D scatter plot in Excel, even in the new version of Excel 2019. If you strongly need this chart type for your data analysis, consider using some third-party tool, like plot.ly. The screenshot below shows what kind of a 3D scatter graph this tool can draw:

Scatter graph and correlation

To interpret the scatter plot correctly, you need to understand how the variables can relate to each other. Overall, there exist three types of correlation:

Positive Correlation - as the x variable increases, so does the y variable. An example of a strong positive correlation is the amount of time the students spend studying and their grades.

Negative Correlation - as the x variable increase, the y variable decreases. Ditching classes and grades are negatively correlated - as the number of absences increases, the exam scores decrease.

No Correlation - there is no evident relationship between the two variables; the dots are scattered around the entire chart area. For example, students' height and grades appear to have no correlation as the former does not affect the latter in any way.

Customizing XY scatter plot in Excel

As with other chart types, nearly each element of a scatter graph in Excel is customizable. You can easily change the chart title, add axis titles, hide the gridlines, choose your own chart colors, and more.

Below we will focus on a few customizations specific to a scatter plot.

Adjust the axis scale (reduce white space)

Plot My Graph Online

In case your data points are clustered at the top, bottom, right, or left side of the graph, you may want to clean up the extra white space.

To reduce the space between the first data point and the vertical axis and/or between the last data point and the right edge of the graph, perform these steps:

Plot Xy Graph

1. Right-click the x axis, and click Format Axis…
2. On the Format Axis pane, set the desired Minimum and Maximum bounds as appropriate.
3. Additionally, you can change the Major units that control the spacing between the gridlines.

The below screenshot shows my settings:

To remove space between the data points and the top/bottom edges of the plot area, format the vertical y axis in a similar manner.

Add labels to scatter plot data points

When creating a scatter graph with a relatively small number of data points, you may wish to label the points by name to make your visual better understandable. Here's how you can do this:

1. Select the plot and click the Chart Elements button.
2. Tick off the Data Labels box, click the little black arrow next to it, and then click More Options…
   
3. On the Format Data Labels pane, switch to the Label Options tab (the last one), and configure your data labels in this way:

• Select the Value From Cells box, and then select the range from which you want to pull data labels (B2:B6 in our case).
• If you'd like to display only the names, clear the X Value and/or Y Value box to remove the numeric values from the labels.
• Specify the labels position, Above data points in our example.
  

That's it! All data points in our Excel scatter plot are now labeled by name:

When two or more data points are very close to each other, their labels may overlap, as is the case with the Jan and Mar labels in our scatter diagram. To fix this, click on the labels, and then click on the overlapping one so that only that label gets selected. Point your mouse cursor to the selected label until the cursor changes to the four-sided arrow, and then drag the label to the desired position.

As the result, you will have a nice Excel scatter plot with perfectly legible labels:

Add a trendline and equation

To better visualize the relationship between the two variables, you can draw a trendline in your Excel scatter graph, also called a line of best fit.

To have it done, right click on any data point and choose Add Trendline… from the context menu.

Excel will draw a line as close as possible to all data points so that there are as many points above the line as below.

Additionally, you can show the equation for the trendline that mathematically describes the relationship between the two variables. For this, check the Display Equation on Chart box on the Format Trendline pane that should appear in the right part of your Excel window immediately after you've added a trendline. The result of these manipulations will look similar to this:

What you see in the screenshot above is often called the linear regression graph, and you can find the detailed guidelines on how to create it here: How to make a linear regression graph in Excel.

How to switch X and Y axes in a scatter chart

As already mentioned, a scatter plot usually displays the independent variable on the horizonal axis and the dependent variable on the vertical axis. If your graph is plotted differently, the easiest fix is to swap the source columns in your worksheet, and then draw the chart anew.

If for some reason rearranging the columns is not possible, you can switch the X and Y data series directly on a chart. Here's how:

1. Right-click any axis and click Select Data… in the context menu.
   
2. In the Select Data Sourcedialog window, click the Edit button.
   
3. Copy Series X valuesto the Series Y values box and vice versa.
   
   Tip. To safely edit the contents of the Series boxes, put the mouse pointer in the box, and press F2.
4. Click OK twice to close both windows.

As the result, your Excel scatter plot will undergo this transformation:

That's how you create a scatter plot in Excel. In our next tutorial, we will continue with this topic and show how to quickly find and highlight a certain data point in a scatter graph. Please stay tuned!

You may also be interested in