8.6 Scatter Chart
Shows the relationship between two numeric measures. Each dot is one item (store, order, day).
Worked example. For 15 dark stores: X = average delivery minutes, Y = cancellation %. Add a linear trendline with Display R-squared value on chart to see if slower stores have more cancellations.
Steps in Excel
- Put X values in the first column and Y values in the second › select both › Insert › Charts › Scatter.
-
- › Trendline › More Options › tick Display Equation on chart and Display R-squared value on chart.
- Label dots with store names: + › Data Labels › More Options › Value From Cells › select the store names.
Ravindra Bagale's Tip
Many students choose a line chart instead of a scatter chart – then the X axis shows 1, 2, 3 instead of the numbers and the relationship isn't visible. For the relationship between two numbers, always use Scatter. And correlation is not causation – be sure to use this sentence in an interview.
Ravindra Bagale's Tip – मराठी
Scatter chart ऐवजी बरेच students line chart निवडतात – मग X axis वर numbers ऐवजी 1, 2, 3 येतात आणि relationship दिसत नाही. दोन numbers च्या relationship साठी नेहमी Scatter. आणि correlation म्हणजे causation नाही – interview मध्ये हे वाक्य नक्की वापरा.
Ravindra Bagale's Tip – हिंदी
Scatter chart की जगह बहुत से students line chart चुन लेते हैं – फिर X axis पर numbers की जगह 1, 2, 3 आते हैं और relationship नहीं दिखता. दो numbers के relationship के लिए हमेशा Scatter. और correlation का मतलब causation नहीं – interview में यह वाक्य ज़रूर बोलना.
Worked example — scatter chart
Why
Rohan Patil in Pune and James O'Neill abroad both need a clear, repeatable way to use scatter chart on a Summit Schools sheet without breaking totals.
What
Scatter Chart is the Excel idea this page teaches. We will walk one tiny example end to end.
How
- Open a blank sheet. In A1 type a short label for scatter chart.
- Enter a small made-up value for Rohan Patil using $ where money appears.
- Add a second value for James O'Neill using $ so you see mixed currencies stay numeric.
- Apply the method from this lesson step by step (menu or formula as shown above).
- Check the result still works inside SUM or a simple check cell.
Formula / action
Follow the formula or ribbon steps shown in this lesson (keep values numeric).Meaning: Once scatter chart is correct on one row, copy the same pattern down the column.
Practice tasks (10+)
Try these on a blank sheet. Open a hint only if you are stuck. No quiz — just practice.
1. Rebuild the smallest example of scatter chart on a blank sheet for NovaCraft Tools.
Hint: Use only the steps on this page. Keep numbers numeric.
2. Change one input for Rohan Patil and confirm the result updates.
Hint: If the result is stuck, check absolute vs relative references.
3. Repeat the same steps with a £ or € value for Yuki Tanaka.
Hint: Formatting must not break SUM.
4. Write the result meaning in one plain sentence next to the cell.
Hint: Imagine explaining it to a new joiners.
5. Add a second row and fill the method down.
Hint: Double-click the fill handle if the pattern is consistent.
6. Break the example on purpose, then fix it using this lesson.
Hint: Common break: text instead of a number.
7. Apply scatter chart to a 5-row made-up NovaCraft Tools table.
Hint: Keep column headers clear.
8. Check your answer with a simple total or COUNT.
Hint: A wrong total usually means a format or key mismatch.
9. Swap ₹ and $ on one column and re-check totals.
Hint: Stored values stay the same; only the display changes.
10. List two mistakes beginners make with scatter chart.
Hint: Look back at the tip box on this page.
11. Teach the steps out loud in under one minute.
Hint: Why → What → How.
12. Save the sheet as NovaCraftTo_practice.xlsx.
Hint: Use a clear file name for class.
Loading practice tasks...
- Total parcels (Easy): Add up the "Parcels" column.
- Average cost (Easy): Average of the Cost (£) column, rounded to 2 decimals.
- Rows in Royal Mail (Easy): How many rows have Category/group "Royal Mail"? (the "Carrier" column)
- Largest parcels (Easy): The largest value in the "Parcels" column.
- Parcels for Royal Mail (Medium): Total of the Parcels column where Carrier is "Royal Mail".
- Royal Mail in Glasgow (Medium): Total of the Parcels column where Carrier is "Royal Mail" and City is "Glasgow".
- Best in Royal Mail (Medium): Largest value in the "Cost (£)" column where "Carrier" is "Royal Mail".
- Value of row 2 (Medium): Multiply the first-row Parcels by the first-row Cost (£).
- Look up Sneha Patil (Medium): The Parcels for "Sneha Patil" (use a lookup on the "Name" column).
- Total value (Hard): Sum of "Parcels" times "Cost (£)" for all rows.
- Share of Royal Mail (Hard): Share of the "Parcels" column that is "Royal Mail", rounded to 3 decimals.
- Total "Parcels" for DPD (Hard): Add up the "Parcels" column for the rows where "Carrier" is "DPD".
- Above average? (Hard): "Above" if the "Cost (£)" value in the first row is above the average of the "Cost (£)" column, else "Below".
Quick revision cards
Three standalone cards. Each one explains the idea with its own example. Scan the QR to open this lesson.
