Line Chart vs Scatter Plot in Excel: Which to Use

Line chart vs scatter plot comes down to one question: is the X axis a list of labels, or a number that matters? A line chart shows how one value changes across ordered steps, like months. A scatter plot shows how two numbers relate, like ad spend and sales. They can look alike in Excel, but they space the X axis differently, and picking the wrong one can quietly distort your data. This guide is for anyone who builds charts for reports or job tests.

Line chart vs scatter plot at a glance#

The table sums up the differences that matter in Excel:

The real difference: how Excel spaces the X axis#

A line chart puts its X values on a category axis. Excel treats each X value as a label and gives every label the same width, whatever the label says. So 1, 2, 5 and 10 sit at equal distances, as if they were the words "one", "two", "five" and "ten".

A scatter plot puts its X values on a value axis, just like the Y axis. A point at X = 10 sits twice as far along as a point at X = 5. Gaps in the data show up as gaps on the chart.

There is one exception. If a line chart's X values are real Excel dates, Excel switches to a date axis and spaces the points by date. A missing week then shows as a gap. Date axes work in whole days, though. If your times include hours and minutes, a scatter plot is the safer choice.

When to use a line chart#

Use a line chart when the X axis is a regular sequence and you want to show a trend. Good fits:

• Regular time periods: monthly revenue, weekly sign-ups, daily stock levels.
• Several series over the same periods: this year against last year, or three regions by month.
• Ordered steps that aren't numbers: stages of a process, such as Q1, Q2, Q3, Q4.

To make one, select the labels and values, then go to Insert > Insert Line or Area Chart > Line (on a Mac, the Line button on the Insert tab). If your data is a long list of transactions, summarise it into one row per month first with SUMIFS or a PivotTable. The guide to making a line chart in Excel covers each step, and a moving average can smooth out a noisy trend.

If the labels have no natural order, such as product names, a line suggests a trend that doesn't exist. Use a bar chart instead.

When to use a scatter plot#

Use a scatter plot when both X and Y are numbers and you want to see whether they are related. Good fits:

• Cause and effect questions: does more ad spend go with more sales?
• Measurements at uneven intervals: readings taken on days 0, 1, 2, 5 and 10.
• Spotting outliers: one point far from the rest stands out at once.
• Series with different X values: two experiments measured at different times.

To make one, select the two number columns with the X column on the left, then go to Insert > Insert Scatter (X, Y) or Bubble Chart > Scatter. Put the value you think drives the other on the X axis. The article on making a scatter plot in Excel shows how to add a trendline and R².

The same data in both charts#

The same table can answer two different questions. Here are six weeks of ad spend and sales in A1:C7:

Question 1: how did sales change week by week? That is a line chart of Week against Sales. The line goes up, down, up, down, up. Sales jump around from week to week, and there's no steady trend.

Question 2: do weeks with more ad spend have more sales? That is a scatter plot of Ad spend (X) against Sales (Y). Sorted by spend, the points rise almost in a straight line, from 200 and 4,700 up to 800 and 8,300. The pattern that looked random over time is clear once you look at the right pair of numbers.

You can back the chart up with a formula. The average sales in weeks with spend of 500 or more is:

=AVERAGEIF(B2:B7,">=500",C2:C7)

That returns 7,600 (7,400, 8,300 and 7,100). For weeks under 500 it's 5,333, from =AVERAGEIF(B2:B7,"<500",C2:C7). MIN and MAX on each column also tell you where to start and end the axes.

Now imagine the line chart with Ad spend as the X labels instead. Excel would place 300, 650, 450, 800, 200 and 550 at equal steps, in sheet order, and join them with a line. The result zigzags and suggests nothing. The scatter plot shows the real relationship.

Common mistakes#

These are the slips that most often give a misleading chart:

1. Two number columns in a line chart. Excel treats both columns as series and uses 1 to 6 on the X axis. You get two lines instead of X against Y. Switch to a scatter plot.
2. Uneven intervals on a line chart. Readings on days 0, 1, 2, 5 and 10 look evenly spaced, which bends the shape of the curve. Use Scatter with Straight Lines to keep the line but fix the spacing.
3. Unsorted data in a scatter with lines. Excel joins points in sheet order, not X order, so the line doubles back on itself. Sort by the X column first.
4. Text in the X column of a scatter plot. If the X cells hold text, Excel can't use them as numbers and plots the points at 1, 2, 3 instead. Convert them to numbers.
5. Reading a trendline equation from a line chart. On a text axis, the trendline uses 1, 2, 3 as X, so the slope is per step, not per unit of your data.
6. Very different scales on one chart. If one series is in thousands and another in single digits, the small one looks flat. Put it on a second axis, as shown in the guide to combo charts with a secondary axis.

Our recommendation#

Start from the question, not the chart menu. If you're tracking one or more values across regular periods, use a line chart. If you're asking whether two measured numbers are related, or your X values are unevenly spaced, use a scatter plot. When you want both, an uneven series joined by a line, choose Scatter with Straight Lines: it looks like a line chart but keeps the true spacing.

In a job test, read the task carefully. "Show the trend in monthly sales" means a line chart. "Show the relationship between price and units sold" means a scatter plot. The guide to Excel chart basics has a quick table for choosing the right chart for other questions.

Practise it#

Good charts start with the right summary numbers. Try the AVERAGEIF exercise on this page, then practise AVERAGE, MIN and MAX in a live spreadsheet to describe a column before you chart it. Next, build a pivot-style summary with formulas to turn long lists into the monthly totals a line chart needs.