Make a Scatter Plot in Excel to Show a Relationship

A scatter plot in Excel shows whether two sets of numbers move together, such as ad spend and sales, or hours studied and test scores. Each row of data becomes one dot: its position across is one value and its height is the other. To make one, put the X values in the left column and the Y values in the right column, select both and choose Insert > Scatter. This guide shows the steps, how to add a trendline, and how to back the picture up with numbers.

When should you use a scatter plot?#

Use a scatter plot when you have two numeric measurements for each item and want to see if they are related. Typical questions are "Do bigger discounts bring more orders?" or "Do longer calls get better ratings?"

Don't use it for a single value over time, such as monthly sales. A line chart is better there, because months are categories in order, not measurements. The guide to Excel chart basics shows which chart fits which question.

All the examples below use seven weeks of ad spend and sales:

Ad spend is the X value (what you change) and Sales is the Y value (what you measure).

How to make a scatter plot in Excel#

Excel builds the chart from the columns you select, with the left one on the X axis:

1. Select B1:C8: the Ad spend and Sales columns with their headers. Leave out the Week column.
2. Go to the Insert tab.
3. In the Charts group, click the Insert Scatter (X, Y) or Bubble Chart button (on a Mac, the X Y (Scatter) button).
4. Choose the first option, Scatter, with dots only.

You get seven dots rising from the bottom left to the top right. That upward slope means the weeks with more spend also had more sales.

Give the chart a clear title and label both axes. Click the chart, then use the + button next to it (Windows) or Chart Design > Add Chart Element (Mac) to add Axis Titles. Name them "Ad spend" and "Sales", so nobody has to guess which is which.

How to choose which column goes on the X axis#

Excel always treats the left column of the selection as X. If your Y values are on the left, swap the columns, or set the ranges by hand:

1. Right-click the chart and choose Select Data.
2. Select the series and click Edit.
3. Set Series X values to the column you want across the bottom and Series Y values to the column you want up the side.

A common mistake is selecting A1:C8, including the Week column. Excel then uses Week as X and draws two series, Ad spend and Sales, against it. That chart shows each value over time, not the relationship between them.

How to add a trendline and R²#

A trendline is the straight line that best fits the dots. It shows the direction of the relationship at a glance.

1. Click any dot, right-click and choose Add Trendline.
2. In the Format Trendline pane, keep Linear selected.
3. Tick Display Equation on chart and Display R-squared value on chart.

For this data, the equation is about y = 6.39x + 1145 and R² is about 0.96. The equation says each extra 1 of ad spend went with about 6.39 more in sales in these seven weeks. R² runs from 0 to 1. A value near 1 means the dots sit close to the line, and a value near 0 means the line explains very little.

You can use the line to estimate, with care: at an ad spend of 400, it predicts sales of about 3,700. In a cell, =FORECAST.LINEAR(400,C2:C8,B2:B8) gives the same estimate. Only predict inside the range you measured (150 to 600 here). Outside it, the pattern may not hold.

How to measure the relationship with formulas#

The chart shows the pattern. A formula puts a number on it, which is what a report or job test usually asks for.

=CORREL(B2:B8,C2:C8)

CORREL returns the correlation coefficient, a number from -1 to 1. Here it is about 0.98: a strong positive relationship. Near 0 means no straight-line relationship, and a negative value means one goes up as the other goes down. R² on the chart is this number squared.

A simpler check uses averages. Split the weeks into high-spend and low-spend weeks, then compare their average sales:

=AVERAGEIF(B2:B8,">"&AVERAGE(B2:B8),C2:C8)

The average spend is about 364, so the high-spend weeks are 2, 4 and 6. Their average sales are 4,366.67. Swap ">" for "<=" and the low-spend weeks average 2,800. A big gap between the two averages tells the same story as the upward dots. The AVERAGE function guide covers the basics.

What to watch for when you read a scatter plot#

A scatter plot is easy to over-read. Keep these points in mind:

• Correlation is not causation. Sales and ad spend may both rise in a busy season. The chart shows that they move together, not why.
• Outliers pull the line. One unusual week can tilt the trendline and change R². Check it before you trust the slope. The article on average vs median shows how one extreme value distorts an average, and the same thing happens here.
• Spread matters. Two data sets can share a trendline but have very different scatter around it. Standard deviation in Excel measures that spread.
• Different scales. If you want two measures on one chart over time, a combo chart with a secondary axis is often clearer than a scatter plot.

Practise it#

Every check in this guide starts with averages and the middle of the data. Practise AVERAGE, MIN and MAX in a live spreadsheet: you type the formula and it checks it instantly. Then practise MEDIAN and MODE to see how one outlier changes the picture. Or try the high-spend average exercise on this page first.