Scatter Plots and XY Charts
Reach for Scatter as soon as both columns hold measurements rather than labels — it is the only common Excel chart with a numeric horizontal axis. Every other family puts the first column on a category axis, which spaces the entries evenly and throws the values away: three readings dated 1 March, 2 March and 15 May come out as three equal steps, and a ten-week gap disappears with nothing on screen to say so. That is worth knowing before you draw anything, because a chart that looks misleading on a Line usually needs a different chart type rather than a different axis setting, and no amount of formatting will recover the spacing. Past that choice the decisions are narrower: which of the five sub-types to draw, whether the relationship has earned a fitted line and an R-squared figure, and whether a third measurement should become bubble size rather than the third axis Excel has never offered.
The formula
=SLOPE(B2:B9,A2:A9) the gradient of the linear trendline — Y range first, then X
=INTERCEPT(B2:B9,A2:A9) where that fitted line meets the vertical axis
=RSQ(B2:B9,A2:A9) R-squared, the same figure the chart label prints
=CORREL(A2:A9,B2:B9) r itself; squaring it gives RSQ back
=FORECAST.LINEAR(45,B2:B9,A2:A9) read the fitted line at an X you never measuredA worked example
A2:A9 holds irrigation applied to eight test plots in millimetres (12, 15, 20, 22, 28, 35, 40 and 50) and B2:B9 the yield each plot returned in bushels per acre (30, 34, 41, 40, 52, 60, 63 and 78).
Select A1:B9, Insert > Scatter (X Y) > Scatter, then right-click any marker > Add Trendline > Linear and tick the two boxes at the foot of the pane that print the fitted equation and the R-squared figure.
The label reads y = 1.2497x + 15.071 with R-squared = 0.9910, and =SLOPE(B2:B9,A2:A9) and =RSQ(B2:B9,A2:A9) hand back those same two numbers in cells. Each extra millimetre goes with about 1.25 more bushels, and 99% of the variation in yield is accounted for by that straight line — across eight plots, which is a description of these points and not proof of a mechanism. The horizontal spacing is the part a category axis would have flattened: the 50 mm plot sits more than four times as far from the origin as the 12 mm one, and the step from 40 to 50 is twice the step from 12 to 15.
Which one do I need?
| If you want to… | Use |
|---|---|
| Two measured quantities, and the question is whether they move together | Insert > Scatter (X Y) with markers only — remember the leftmost selected column becomes X |
| The horizontal values are dates or labels running in an even sequence | A line chart, which is built around a category axis and handles a month-by-month series better |
| The points should be joined, but X is numeric and unevenly spaced | Scatter with Straight Lines — and sort the table by X first, because the line follows the row order it was given |
| A third measurement should control how large each point is drawn | A bubble chart, which takes a size column as well; Excel scales bubbles by area by default, so a doubled value is not a doubled radius |
| You want the fitted line drawn on the picture with its equation | Right-click the series > Add Trendline, then use the two print options at the foot of the pane |
| Those same figures are needed in cells rather than on the chart | =SLOPE(ys,xs), =INTERCEPT(ys,xs) and =RSQ(ys,xs) — every one of them takes the Y range as its first argument |
| Standard errors, p-values and confidence intervals are needed, not just a line | Data > Data Analysis > Regression from the Analysis ToolPak; a trendline reports no significance whatsoever |
| You only want a value read off between two points you already have | That is interpolation between neighbours, not a line fitted through the whole set |
| A third numeric axis is genuinely required | Excel has no 3-D XY type at all — the 3-D entries in the gallery are category charts. Encode the third quantity as bubble size, or as separate coloured series |
| There is only one variable and its distribution is the question | A histogram, which cuts one column into ranges instead of pairing two columns |
Frequently asked questions
Why is Excel plotting the wrong column on the X axis?
Excel takes the leftmost selected column as X, so a sheet where the measured input sits to the right of the output plots them the wrong way round. Either move the columns, or open Select Data > Edit and swap the Series X values and Series Y values ranges by hand. If only one column was selected, there is no X data at all and Excel numbers the points 1, 2, 3 along the bottom.
What does the R-squared figure on a trendline actually tell me?
It is the share of the up-and-down movement in your Y values that the fitted curve accounts for, on a scale from 0 to 1. It is not a test of anything: it carries no p-value, it says nothing about whether the pattern would survive more data, and raising the polynomial order always raises it, so a 0.99 from a sixth-order curve describes the curve rather than the measurements. Treat it as a description of these points and use Data Analysis > Regression when you need to defend a claim.
How do I plot several series on one scatter chart?
Select Data > Add, and give each series its own Series X values and Series Y values ranges. Series may share one X range or use completely different ones, which is the advantage over a line chart. Give each a distinct marker shape as well as colour under Format Data Series > Marker Options, since colour alone disappears in print and for colour-blind readers.
Can Excel draw a 3-D scatter plot?
No. There is no three-axis XY chart type in any version of Excel; the 3-D column and 3-D line entries in the gallery still use a category axis and merely look dimensional. The workable substitutes are a bubble chart, where the third variable becomes marker size, or splitting the third variable into bands and plotting each band as its own coloured series.
New guides and tools, once a month
DE + EN · double opt-in · no spam