Part of the free Module 8: Basic Charts · Lesson 11 of 12 · Full Excel course
Updated on 5 September 2026 · Works in Excel 365, 2021, 2019 and 2016 unless noted. FORECAST.LINEAR needs Excel 2016 or later.
A scatter chart in Excel (also called an XY scatter plot) shows the relationship between two numeric variables by placing one dot per row at its X and Y coordinates. It is the chart to use when you want to see whether one measure moves with another, such as advertising spend against sales, and it is the only basic chart type that treats the horizontal axis as real numbers rather than categories.
Scatter chart versus line chart
Beginners often draw a line chart when they need a scatter chart. The difference is the horizontal axis. A line chart treats every X value as a category and spaces them evenly, whatever the numbers are. A scatter chart plots X on a true numeric scale, so 10, 20 and 100 sit where they belong. The rule is simple: if the X values are numbers with meaning (temperature, price, hours, age), use a scatter chart; if they are labels or evenly spaced dates, use a line chart.
| Feature | Scatter chart | Line chart |
|---|---|---|
| Horizontal axis | Numeric value axis | Category or date axis |
| Uneven X spacing | Plotted correctly | Forced to equal spacing |
| Two numeric variables | Yes, X and Y | No, Y only against categories |
| Trendline with equation | Meaningful | Only if categories are really numbers |
| Typical use | Correlation, calibration, distributions | Trend over time |
Scatter chart sub-types
The Insert Scatter (X, Y) or Bubble Chart button offers five scatter layouts and two bubble layouts.
| Sub-type | What it draws | Use it when |
|---|---|---|
| Scatter | Markers only | Looking for a pattern or correlation (the default choice) |
| Scatter with Smooth Lines and Markers | Curved line through the points | Calibration curves, sorted X values |
| Scatter with Smooth Lines | Curve without markers | Many points, smooth relationship |
| Scatter with Straight Lines and Markers | Points joined by straight segments | Measured series in X order, such as a load test |
| Scatter with Straight Lines | Straight segments, no markers | Dense measured series |
| Bubble | Marker size shows a third value | X, Y and a size measure such as revenue |
Only join the dots with lines when the X values are sorted and the sequence means something. For a correlation chart use plain markers and add a trendline instead.
How to create a scatter chart in Excel
Put the X variable in the left column and the Y variable in the right column. Excel uses the first column as X automatically.
- Select both columns including the headers, for example A1:B11.
- Go to Insert > Charts > Insert Scatter (X, Y) or Bubble Chart and click Scatter.
- Click the chart title and type a sentence that states the relationship, such as “Sales rise with advertising spend”.
- Click the Chart Elements (+) button, tick Axis Titles and name the horizontal axis after the X column and the vertical axis after the Y column.
- Untick Legend; a single-series scatter does not need one.
- Double-click the horizontal axis and set Bounds so the points fill the plot area rather than crowding one corner.
If the chart comes out with two series and no X axis values, Excel has read both columns as Y. Right-click the chart, choose Select Data, remove the extra series and edit the remaining one so Series X values points at the first column and Series Y values at the second.
Formatting the chart
- Markers: double-click a point, open Marker > Marker Options and choose a built-in circle at size 7 to 9. Reduce the fill transparency to about 30% when points overlap, so dense clusters show as darker areas.
- Trendline: click Chart Elements > Trendline > Linear. In Format Trendline tick Display Equation on chart and Display R-squared value on chart. R-squared close to 1 means a strong linear relationship; close to 0 means none.
- Labels from a third column: add Data Labels, then in Format Data Labels > Label Options tick Value From Cells and select the column of names. Untick Y Value. This is how you label each dot with a product or region name (Excel 2013 and later).
- Axis number formats: apply
$#,##0or0%in Format Axis > Number so readers know the units. - Gridlines: keep light major gridlines on both axes; they help readers place a point.
- Highlight one point: click the series, click the point again, and give it a different fill. Add a single data label to it.
- Bubble size: for a bubble chart, double-click a bubble and set Scale bubble size to 50 to 75 if the bubbles overlap.
Worked example: advertising spend versus sales
Ten weeks of data in A1:B11:
| Ad spend (USD) | Sales (USD) |
|---|---|
| 1,200 | 18,400 |
| 1,800 | 22,100 |
| 2,500 | 27,900 |
| 900 | 15,200 |
| 3,100 | 31,800 |
| 2,000 | 24,600 |
| 1,500 | 19,900 |
| 2,800 | 29,300 |
| 3,500 | 34,700 |
| 2,300 | 26,200 |
- Select A1:B11 and insert a Scatter chart.
- Add a Linear trendline with the equation and R-squared displayed.
- Set the horizontal axis bounds to 500 and 4,000 and the vertical axis to 10,000 and 40,000.
- In D1 enter
=CORREL(A2:A11,B2:B11)and in D2 enter=FORECAST.LINEAR(4000,B2:B11,A2:A11).
Result: the dots rise from bottom-left to top-right in a tight band, the trendline equation is approximately y = 7.35x + 9,143 with R-squared 0.995, CORREL returns 0.998, and the forecast for USD 4,000 of spend is about USD 38,500 of sales. The chart and the formulas agree, which is the check you should always make.
When to use a scatter chart and when not to
Use it when both variables are numeric and you want to see correlation, outliers or clusters; when X values are unevenly spaced measurements; when you need a fitted trendline with an equation; or when you are comparing actual results against a calibration curve.
Do not use it when the X axis is text (regions, products), when the data is a time series with regular intervals (a line chart reads better), when you have fewer than about six points (a table is clearer), or when a third variable is categorical rather than numeric (use colour by series instead of a bubble size).
A common mistake is to treat correlation as cause. A scatter chart shows that two measures move together; it does not show that one drives the other. Say “sales rise with ad spend”, not “ad spend increases sales”, unless you have evidence beyond the chart.
Tips and common mistakes
- X on the left, Y on the right. Excel assigns the first selected column to X. Swap columns or edit the series if the axes come out reversed.
- Do not join the dots on a correlation chart. Lines imply sequence; markers plus a trendline show relationship.
- Start axes where the data lives. Unlike bar charts, a scatter axis need not start at zero; set bounds so the cloud fills the plot.
- Check R-squared before quoting the equation. A trendline through random dots still produces an equation.
- Label with Value From Cells, not by typing labels one at a time; typed labels break when the data changes.
- Use one series per group to colour points by category: put each group in its own Y column with blank cells elsewhere.
- Verify with CORREL. A correlation coefficient in a cell stops arguments about what the picture shows.
Errors and how to fix them
| Problem | Cause | Fix |
|---|---|---|
| X axis shows 1, 2, 3 instead of the values | Line chart inserted, or X column read as a series | Change to Scatter; in Select Data set Series X values to the first column |
| Two series appear, one flat | Header row not selected, so Excel guessed | Include headers, or remove the extra series in Select Data |
| Points stack on the left edge | Axis bounds start at 0 with large X values | Set the horizontal axis Minimum near the smallest X |
| Trendline equation shows rounded coefficients | Default label number format | Select the equation label, Format > Number > Scientific or more decimals |
| Labels show numbers, not names | Value From Cells not ticked | Format Data Labels > tick Value From Cells, untick Y Value |
| Blank rows plotted as zero | Empty strings from formulas | Return NA() instead of “” and set Hidden and Empty Cells to Gaps |
Practice exercise
- Build the ad-spend scatter chart above and add a linear trendline with the equation and R-squared.
- Add a third column of week names and label each point with them using Value From Cells.
- Insert the same data as a line chart and write one sentence on why the X axis differs.
- Add a fourth column of profit and create a bubble chart with profit as the bubble size.
- Split the rows into two groups (online, offline) as separate Y columns and colour them as two series.
Key takeaways
- A scatter chart plots two numeric variables; the X axis is a real value axis.
- Insert it from Insert > Charts > Insert Scatter (X, Y) or Bubble Chart > Scatter with X in the left column.
- Use markers plus a linear trendline, with equation and R-squared, to show a relationship.
- Label points with names using Value From Cells.
- Check the picture with CORREL and FORECAST.LINEAR.
- Correlation is not causation; describe what the chart shows, not more.
Related lessons
- Course hub: Excel Charts course
- Line chart in Excel
- Chart elements: titles, labels, legend and axes
- How to choose the right chart type
- CORREL function and FORECAST function
- Advanced Charts course
- Microsoft Support: Present your data in a scatter chart or a line chart
Frequently asked questions
What is the difference between a scatter chart and a line chart in Excel?
A scatter chart uses a numeric horizontal axis, so X values are placed by their size and can be unevenly spaced. A line chart treats X values as categories and spaces them evenly, whatever they are. Use scatter for two numeric variables and line for a series over time or across labelled categories.
How do I add a trendline and equation to a scatter chart?
Select the chart, click the Chart Elements (+) button and tick Trendline. Then click the arrow next to Trendline, choose More Options, pick Linear (or another fit) and tick Display Equation on chart and Display R-squared value on chart. The equation label can be formatted for more decimal places.
How do I label points in a scatter chart with names?
Add data labels, then open Format Data Labels, tick Value From Cells and select the column holding the names. Untick Y Value so only the name shows. This option exists in Excel 2013 and later. In older versions, each label has to be edited by hand or added with a macro.
Why does my scatter chart show the wrong data on the X axis?
Excel takes the first selected column as X. If the chart plots row numbers, the data was inserted as a line chart, or both columns were read as Y series. Right-click the chart, choose Select Data, edit the series and set Series X values to the X column and Series Y values to the Y column.
Can a scatter chart have more than one series?
Yes. Add each group as a separate series in Select Data, each with its own X and Y ranges, and Excel colours them differently. This is the usual way to show categories on a scatter chart, for example online and offline sales as two colours, with a legend to identify them.
Want the finished version? Ready-made Excel dashboards, trackers and VBA systems are available at NextGenTemplates.com.