Scatter plots in Excel - Deep Dive

Overview - Scatter plots

What is it?

A scatter plot is a type of chart that shows the relationship between two sets of numbers. Each point on the chart represents one pair of values, with one value on the horizontal axis and the other on the vertical axis. This helps you see patterns, trends, or clusters in your data. Scatter plots are useful for comparing two variables to understand how they might be connected.

Why it matters

Without scatter plots, it would be hard to quickly see how two things relate to each other in a large set of numbers. For example, if you want to know if more hours studied lead to higher test scores, a scatter plot shows this clearly. Without it, you might miss important patterns or make wrong guesses. Scatter plots make data visual and easy to understand, helping you make better decisions.

Where it fits

Before learning scatter plots, you should know how to enter data in Excel and create basic charts like bar or line charts. After mastering scatter plots, you can learn about trendlines, correlation, and regression analysis to deepen your understanding of data relationships.

Mental Model

Core Idea

A scatter plot places pairs of numbers as dots on a grid to reveal how two things relate to each other visually.

Think of it like...

Imagine throwing small balls onto a large grid on the floor, where each ball's position depends on two measurements, like height and weight. The pattern of balls shows if taller people tend to weigh more or less.

  Y-axis (Variable 2)
    ↑
    │       ●       ●
    │   ●       ●
    │       ●
    │  ●
    │
    └────────────────→ X-axis (Variable 1)

Build-Up - 7 Steps

1

Foundation

Understanding data pairs for plotting

Concept: Scatter plots need pairs of numbers, each pair representing one point.

To create a scatter plot, you first need two columns of numbers in Excel. Each row has one pair: the first number goes on the horizontal axis, and the second on the vertical axis. For example, column A could be hours studied, and column B could be test scores.

Result

You have a clear set of paired data ready to plot points on a chart.

Knowing that each point comes from a pair of numbers helps you organize data correctly before plotting.

2

Foundation

Creating a basic scatter plot chart

Concept: Excel can turn paired data into a scatter plot with a few clicks.

Select your two columns of data. Then go to the Insert tab, choose 'Scatter' from the Charts group, and pick the first scatter plot option. Excel will place dots on the chart representing each pair of numbers.

Result

A scatter plot appears showing dots for each data pair.

Seeing your data as points on a chart makes relationships easier to spot than just looking at numbers.

3

Intermediate

Interpreting scatter plot patterns

🤔Before reading on: do you think points close together mean a strong relationship or no relationship? Commit to your answer.

Concept: The pattern of dots shows how two variables relate: clustered, spread out, or following a line.

If points form a clear line going up, it means as one value increases, the other does too (positive correlation). If the line goes down, one increases while the other decreases (negative correlation). If points are scattered randomly, there may be no clear relationship.

Result

You can tell if two variables are related by looking at the shape and direction of the dots.

Understanding patterns in scatter plots helps you quickly judge if variables influence each other.

4

Intermediate

Adding and using trendlines

🤔Before reading on: do you think a trendline always fits all points exactly or shows an average trend? Commit to your answer.

Concept: A trendline summarizes the overall direction of data points, showing the average relationship.

In Excel, right-click a data point on the scatter plot and choose 'Add Trendline.' You can select linear or other types. The trendline helps you see the general pattern even if points don’t line up perfectly.

Result

A line appears on the chart showing the average trend of the data points.

Trendlines help simplify complex data by highlighting the main direction of the relationship.

5

Intermediate

Customizing scatter plot appearance

Concept: You can change colors, point shapes, and axis labels to make your scatter plot clearer.

Use the Chart Tools in Excel to add titles, label axes, change dot colors, or adjust gridlines. Clear labels help others understand what the chart shows.

Result

A well-labeled, visually clear scatter plot that communicates data effectively.

Good design choices make your data story easier to understand and more convincing.

6

Advanced

Using scatter plots for outlier detection

🤔Before reading on: do you think outliers are points close to the trendline or far away? Commit to your answer.

Concept: Outliers are points that don’t follow the general pattern and can indicate errors or special cases.

Look for dots far away from the trendline or cluster. These may be mistakes in data or important exceptions. Identifying outliers helps improve data quality or reveals unique insights.

Result

You can spot unusual data points that might need further investigation.

Recognizing outliers prevents wrong conclusions and highlights interesting data stories.

7

Expert

Limitations and pitfalls of scatter plots

🤔Before reading on: do you think scatter plots can show cause and effect or only relationships? Commit to your answer.

Concept: Scatter plots show relationships but do not prove one thing causes another.

Even if points form a clear pattern, it doesn’t mean one variable causes the other to change. Other factors might be involved. Also, scatter plots can be misleading if data is too sparse or axes are scaled oddly.

Result

You understand that scatter plots are tools for exploration, not proof.

Knowing the limits of scatter plots helps avoid overinterpreting data and making wrong decisions.

Under the Hood

Excel stores your data in cells and uses the chart engine to map each pair of numbers to a coordinate on the chart grid. The horizontal axis corresponds to the first number, and the vertical axis to the second. Each point is drawn as a dot at that coordinate. When you add a trendline, Excel calculates the best-fit line using mathematical formulas like least squares to summarize the data pattern.

Why designed this way?

Scatter plots were designed to visually represent two-variable data because humans understand pictures faster than tables of numbers. The dot placement directly maps data values to positions, making patterns obvious. Trendlines were added to help summarize noisy data. Alternatives like bar charts don’t show relationships between two continuous variables as clearly.

Data Table
┌───────────────┐
│ X │ Y       │
│ 5 │ 10      │
│ 7 │ 14      │
│ 6 │ 12      │
└───────────────┘
     ↓
Excel Chart Engine
┌─────────────────────────────┐
│ Maps X to horizontal axis   │
│ Maps Y to vertical axis     │
│ Draws dots at (X,Y) points  │
│ Calculates trendline line   │
└─────────────────────────────┘
     ↓
Scatter Plot Display
┌─────────────────────────────┐
│ ●   ●                      │
│    ●                       │
│                           │
│ X-axis →                   │
└─────────────────────────────┘

Myth Busters - 4 Common Misconceptions

Quick: Does a clear upward pattern in a scatter plot prove one variable causes the other? Commit yes or no.

Common Belief: If points go up together, one variable must cause the other to increase.

Reality: Scatter plots show correlation, not causation. Other factors or coincidence can cause patterns.

Why it matters: Mistaking correlation for causation can lead to wrong decisions, like assuming studying more always causes better scores without considering other factors.

Quick: Do you think scatter plots can only be made with numbers? Commit yes or no.

Common Belief: Scatter plots only work with numeric data.

Reality: Scatter plots require numeric data for both axes, but categorical data can be converted to numbers or used in other chart types.

Why it matters: Trying to plot text or categories directly causes errors or meaningless charts.

Quick: Do you think adding a trendline means all points lie exactly on that line? Commit yes or no.

Common Belief: A trendline fits all points perfectly.

Reality: Trendlines show the average trend and rarely pass through all points exactly.

Why it matters: Expecting perfect fit can cause confusion or mistrust in data analysis.

Quick: Can scatter plots show relationships when data points overlap exactly? Commit yes or no.

Common Belief: Overlapping points don’t affect scatter plot usefulness.

Reality: Overlapping points can hide data density and mislead interpretation.

Why it matters: Ignoring overlaps can cause underestimating how many data points share values.

Expert Zone

1

Scatter plots can be enhanced with marker size or color to add a third or fourth variable dimension, revealing more complex relationships.

2

The choice of axis scale (linear vs logarithmic) can drastically change the visual pattern and interpretation of data.

3

Excel’s default scatter plot settings may hide data details; customizing axis limits and gridlines is crucial for accurate analysis.

When NOT to use

Scatter plots are not suitable when you have only one variable or categorical data without numeric encoding. For categorical comparisons, bar or column charts are better. Also, if data points are too dense and overlap heavily, heatmaps or hexbin plots are more effective alternatives.

Production Patterns

Professionals use scatter plots to explore data before modeling, detect outliers, and communicate findings visually. In finance, scatter plots show risk vs return. In science, they reveal correlations between measurements. Adding regression lines and confidence intervals is common to support statistical analysis.

Connections

Correlation coefficient

Scatter plots visually show relationships that correlation coefficients measure numerically.

Understanding scatter plots helps grasp what correlation numbers mean and when they might be misleading.

Data visualization principles

Scatter plots follow core visualization rules like clear axes, labels, and avoiding clutter.

Knowing good visualization practices improves how you design scatter plots for clear communication.

Physics: plotting experimental data

Scientists use scatter plots to compare measurements and find physical laws.

Seeing scatter plots as a universal tool for exploring relationships connects spreadsheet skills to real-world science.

Common Pitfalls

#1 Plotting data with mismatched ranges on axes causing misleading visuals.

Wrong approach:

Creating a scatter plot without adjusting axis scales, e.g., X axis from 0 to 1000 and Y axis from 0 to 10.

Correct approach:

Adjust both axes to appropriate ranges or use consistent scales to fairly represent data.

Root cause:

Not realizing axis scale affects how patterns appear, leading to distorted impressions.

#2 Using non-numeric data directly in scatter plots causing errors.

Wrong approach:

Selecting text labels as X or Y values in the scatter plot data range.

Correct approach:

Convert categories to numbers or use different chart types for non-numeric data.

Root cause:

Misunderstanding that scatter plots require numeric inputs for both axes.

#3 Ignoring outliers and treating all points equally in analysis.

Wrong approach:

Not checking for points far from the cluster or trendline.

Correct approach:

Identify and investigate outliers to decide if they are errors or important data.

Root cause:

Assuming all data points are equally valid without verification.

Key Takeaways

Scatter plots visually map pairs of numbers as dots to reveal relationships between two variables.

They help identify patterns like positive or negative correlations but do not prove cause and effect.

Adding trendlines summarizes data trends and helps interpret noisy data.

Proper axis scaling and labeling are essential for accurate and clear scatter plots.

Recognizing outliers in scatter plots is key to trustworthy data analysis.