Scatter plots in Google Sheets - Real Business Scenario

Scenario Mode

👤 Your Role: You are a sales analyst at a retail company.

📋 Request: Your manager wants to understand if there is a relationship between advertising spend and sales revenue across different stores.

📊 Data: You have monthly data for 10 stores showing the amount spent on advertising and the sales revenue generated.

🎯 Deliverable: Create a scatter plot in Google Sheets to visualize the relationship between advertising spend and sales revenue.

Sample Data

Store	Advertising Spend ($)	Sales Revenue ($)
Store A	5000	20000
Store B	7000	25000
Store C	3000	15000
Store D	8000	27000
Store E	2000	12000
Store F	6000	22000
Store G	4000	18000
Store H	9000	30000
Store I	1000	10000
Store J	7500	26000

1

Step 1: Enter the sample data into Google Sheets with columns: Store, Advertising Spend ($), Sales Revenue ($).

No formula needed. Just type the data as shown in the sample_data table.

Expected Result

Data is organized in three columns with 10 rows of store data.

2

Step 2: Select the range covering Advertising Spend and Sales Revenue columns (B1:C11).

Select cells B1 to C11.

Expected Result

The two columns with headers and data are highlighted.

3

Step 3: Insert a scatter chart to visualize the relationship.

Go to Insert > Chart. In Chart editor, under Chart type, select 'Scatter chart'.

Expected Result

A scatter plot appears showing points for each store with Advertising Spend on the X-axis and Sales Revenue on the Y-axis.

4

Step 4: Add chart title and axis labels for clarity.

In Chart editor, under Customize > Chart & axis titles, set Chart title to 'Advertising Spend vs Sales Revenue'. Set Horizontal axis title to 'Advertising Spend ($)'. Set Vertical axis title to 'Sales Revenue ($)'.

Expected Result

The chart has a clear title and labeled axes.

5

Step 5: Interpret the scatter plot to see if higher advertising spend relates to higher sales revenue.

No formula needed. Observe the chart points.

Expected Result

You see that points trend upward, indicating a positive relationship between advertising spend and sales revenue.

Final Result

Scatter Plot: Advertising Spend vs Sales Revenue

Y-axis (Sales Revenue $)
|
|       *
|      *  *
|     *    *
|    *      *
|   *        *
|  *          *
| *            *
|________________________
          X-axis (Advertising Spend $)

✓Stores that spend more on advertising tend to have higher sales revenue.

✓There is a positive relationship between advertising spend and sales revenue.

✓This suggests investing in advertising can help increase sales.

Bonus Challenge

Add a trendline to the scatter plot to quantify the relationship between advertising spend and sales revenue.

Show Hint

In the Chart editor, under Customize > Series, check the box for 'Trendline' to add a line showing the general trend.

Practice

1. What does a scatter plot primarily show in Google Sheets?

easy

A. The relationship between two sets of numbers

B. The total sum of a data column

C. The average value of a dataset

D. The frequency of a single number

Solution

1. Step 1: Understand scatter plot purpose

   A scatter plot displays points representing pairs of values from two data sets.

2. Step 2: Identify what it shows

   It shows how two variables relate or if there's a pattern between them.

3. Final Answer:

   The relationship between two sets of numbers -> Option A

4. Quick Check:

   Scatter plot = relationship between two data sets [OK]

Hint: Scatter plots compare two data sets visually [OK]

Common Mistakes:

• Thinking it shows totals or averages
• Confusing with bar or pie charts
• Assuming it shows frequency counts

2. Which menu option do you use in Google Sheets to insert a scatter plot?

easy

A. Tools > Script editor

B. Data > Pivot table

C. Format > Conditional formatting

D. Insert > Chart

Solution

1. Step 1: Locate chart insertion

   Scatter plots are created by inserting a chart from the Insert menu.

2. Step 2: Choose correct menu path

   Insert > Chart opens chart options including scatter plot type.

3. Final Answer:

   Insert > Chart -> Option D

4. Quick Check:

   Insert menu > Chart for scatter plots [OK]

Hint: Insert menu always has chart options [OK]

Common Mistakes:

• Choosing Data or Format menus instead
• Looking for scatter plot outside Insert menu
• Confusing with script editor tools

3. Given data in columns A and B, what will the scatter plot show if column A has values 1, 2, 3 and column B has values 2, 4, 6?

medium

A. A horizontal line at value 3

B. Points forming a straight line showing a doubling pattern

C. Random scattered points with no pattern

D. A vertical line at value 2

Solution

1. Step 1: Analyze data pairs

   Pairs are (1,2), (2,4), (3,6) showing B is double A.

2. Step 2: Understand scatter plot pattern

   Points will align on a straight line with slope 2, showing doubling.

3. Final Answer:

   Points forming a straight line showing a doubling pattern -> Option B

4. Quick Check:

   Scatter plot shows linear doubling pattern [OK]

Hint: Look for simple numeric relationships in pairs [OK]

Common Mistakes:

• Assuming points are random
• Confusing line directions
• Thinking it forms horizontal or vertical lines

4. You created a scatter plot but it shows all points in a straight vertical line. What is the most likely cause?

medium

A. Y data column has identical values, X varies

B. Both X and Y data columns have the same values

C. X data column has identical values, Y varies

D. Data contains text instead of numbers

Solution

1. Step 1: Understand vertical line in scatter plot

   A vertical line means all X values are the same, Y values differ.

2. Step 2: Identify cause from options

   X data column has identical values, Y varies states X data identical, Y varies, matching the vertical line cause.

3. Final Answer:

   X data column has identical values, Y varies -> Option C

4. Quick Check:

   Vertical line = same X values [OK]

Hint: Vertical line means X values don't change [OK]

Common Mistakes:

• Thinking identical Y values cause vertical line
• Assuming text data causes vertical line
• Confusing vertical with horizontal line causes

5. You want to compare sales and advertising spend for 12 months using a scatter plot in Google Sheets. Which steps correctly create this scatter plot?

hard

A. Select sales and advertising columns, then Insert > Chart, choose Scatter chart type

B. Select sales column only, then Insert > Chart, choose Scatter chart type

C. Select advertising column only, then Insert > Chart, choose Line chart type

D. Select sales and advertising columns, then Insert > Chart, choose Pie chart type

Solution

1. Step 1: Select both data columns

   Scatter plots need two sets of data to compare, so select sales and advertising columns.

2. Step 2: Insert scatter chart

   Go to Insert > Chart and choose Scatter chart type to plot the relationship.

3. Final Answer:

   Select sales and advertising columns, then Insert > Chart, choose Scatter chart type -> Option A

4. Quick Check:

   Select two columns + Scatter chart = correct plot [OK]

Hint: Always select both data sets before inserting scatter chart [OK]

Common Mistakes:

• Selecting only one column
• Choosing wrong chart type like pie or line
• Not selecting data before inserting chart