Scatter plots in Excel - Step-by-Step Guide

Introduction

Scatter plots help you see the relationship between two sets of numbers. They show points on a grid so you can spot patterns or trends easily.

When you want to compare sales numbers and advertising spend to see if more ads lead to more sales.

When you track students' study hours and their test scores to find if studying more helps improve scores.

When you want to check if temperature changes affect ice cream sales over days.

When you analyze the connection between hours worked and money earned for freelancers.

When you want to visualize how two measurements relate, like height and weight of people.

Steps

Step 1: Select

- the two columns of data you want to compare

The data cells are highlighted on the worksheet

Step 2: Click

- Insert tab on the Ribbon

The Insert tab options appear

Step 3: Click

- Scatter chart icon in the Charts group

A menu with scatter chart styles appears

Step 4: Select

- the first Scatter chart style (Scatter with only Markers)

Excel inserts a scatter plot chart on the worksheet showing points for your data

Step 5: Click

- Chart Title on the chart

The title becomes editable

💡 Type a descriptive title like 'Sales vs Advertising Spend'

Step 6: Click

- Chart Elements button (plus icon) next to the chart

A list of chart elements appears

Step 7: Check

- Axis Titles option

Axis titles appear on the chart for X and Y axes

💡 Click each axis title to rename it to something meaningful like 'Advertising Spend' and 'Sales'

Before vs After

Before

Two columns of numbers with no visual summary

After

A scatter plot chart appears showing dots representing each pair of numbers, making it easy to see patterns

Settings Reference

Chart Type

📍 Insert tab > Charts group > Scatter chart icon

Choose how the points and lines appear on the scatter plot

Default: Scatter with only Markers

Chart Title

📍 Click on the chart title text box on the chart

Add a clear title to explain what the chart shows

Default: Chart Title

Axis Titles

📍 Chart Elements button (plus icon) next to the chart

Show or hide labels for the X and Y axes

Default: Off

Data Labels

📍 Chart Elements button (plus icon)

Show values next to each point on the chart

Default: Off

Common Mistakes

Selecting only one column of data before inserting the scatter plot

Scatter plots need two sets of numbers to compare; one column is not enough

Select both columns of related data before creating the scatter plot

Using a line chart instead of a scatter plot for two numeric data sets

Line charts connect points in order, which can mislead the relationship between two variables

Use the Scatter chart type to show the true relationship between two numeric variables

Not adding axis titles

Without axis titles, it is hard to understand what each axis represents

Add clear axis titles to explain what each axis measures

Summary

Scatter plots show the relationship between two sets of numbers using dots on a grid.

You create them by selecting two columns of data and choosing Scatter chart from the Insert tab.

Always add clear titles and axis labels to make the chart easy to understand.

Practice

1. What is the main purpose of a scatter plot in Excel?

easy

A. To show the relationship between two sets of numbers using dots

B. To display data as a pie chart

C. To calculate the sum of numbers in a column

D. To sort data alphabetically

Solution

1. Step 1: Understand scatter plot basics

   A scatter plot uses dots to show how two sets of numbers relate to each other.

2. Step 2: Compare options to definition

   Only To show the relationship between two sets of numbers using dots describes this correctly; others describe different chart types or functions.

3. Final Answer:

   To show the relationship between two sets of numbers using dots -> Option A

4. Quick Check:

   Scatter plot = relationship with dots [OK]

Hint: Scatter plots show dots for two number sets [OK]

Common Mistakes:

• Confusing scatter plots with pie charts
• Thinking scatter plots sort data
• Believing scatter plots calculate sums

2. Which of these is the correct way to insert a scatter plot in Excel?

easy

A. Select data, then go to Insert > Scatter Chart

B. Select data, then press Ctrl + S

C. Select data, then go to Home > Conditional Formatting

D. Select data, then right-click and choose Sort

Solution

1. Step 1: Recall Excel menu for charts

   Scatter plots are inserted via the Insert tab under Scatter Chart options.

2. Step 2: Eliminate incorrect options

   Ctrl + S saves, Conditional Formatting colors cells, Sort arranges data; none insert charts.

3. Final Answer:

   Select data, then go to Insert > Scatter Chart -> Option A

4. Quick Check:

   Insert > Scatter Chart = correct way [OK]

Hint: Use Insert tab to add scatter plots [OK]

Common Mistakes:

• Using save shortcut instead of chart insert
• Confusing formatting with chart insertion
• Trying to sort data to create charts

3. Given data in columns A and B: A1=1, A2=2, A3=3; B1=2, B2=4, B3=6. What pattern will the scatter plot show?

medium

A. A curve bending downward

B. A straight line showing a positive linear relationship

C. Random dots with no clear pattern

D. Dots clustered at zero

Solution

1. Step 1: Analyze the data pairs

   Pairs are (1,2), (2,4), (3,6). B values are double A values, showing a linear pattern.

2. Step 2: Understand scatter plot pattern

   Since B increases proportionally with A, dots form a straight line with positive slope.

3. Final Answer:

   A straight line showing a positive linear relationship -> Option B

4. Quick Check:

   Doubling values = straight positive line [OK]

Hint: Check if one column doubles the other for linear line [OK]

Common Mistakes:

• Assuming random scatter without checking values
• Thinking the line curves without data reason
• Ignoring proportional increase in values

4. You created a scatter plot but the dots are missing. What is the most likely cause?

medium

A. You forgot to save the file

B. Chart type chosen was Pie Chart instead of Scatter

C. Excel file is corrupted

D. Data selected includes empty or non-numeric cells

Solution

1. Step 1: Identify common scatter plot issues

   Scatter plots require numeric data; empty or text cells cause no dots to appear.

2. Step 2: Evaluate other options

   Pie chart would show a different chart, file corruption or saving issues don't cause missing dots specifically.

3. Final Answer:

   Data selected includes empty or non-numeric cells -> Option D

4. Quick Check:

   Non-numeric data = no dots [OK]

Hint: Ensure all selected data cells have numbers [OK]

Common Mistakes:

• Choosing wrong chart type but expecting scatter dots
• Blaming file corruption without checking data
• Thinking saving affects chart display immediately

5. You have monthly sales data in column A and advertising spend in column B. How can you use a scatter plot to find if higher advertising spend increases sales?

hard

A. Plot sales and advertising spend both on Y-axis

B. Create a pie chart of sales percentages

C. Plot sales on Y-axis and advertising spend on X-axis to see correlation

D. Sort the data by sales before plotting

Solution

1. Step 1: Understand axes roles in scatter plots

   Independent variable (advertising spend) goes on X-axis; dependent (sales) on Y-axis to check influence.

2. Step 2: Evaluate options for correct plotting

   Plotting both on Y-axis or pie chart won't show relationship; sorting data doesn't create scatter plot.

3. Final Answer:

   Plot sales on Y-axis and advertising spend on X-axis to see correlation -> Option C

4. Quick Check:

   Independent X, dependent Y shows correlation [OK]

Hint: Put cause on X-axis, effect on Y-axis [OK]

Common Mistakes:

• Plotting both variables on same axis
• Using pie chart instead of scatter plot
• Sorting data instead of plotting relationship