Ultimate Guide: Creating and Customizing Scatter Plots in Excel with Two Data Sets

 

Scatter plots are one of the most effective ways to visualize relationships between two variables in Microsoft Excel. But did you know you can also compare multiple datasets, use secondary axes, adjust scaling, and even save your chart as an image?

In this step-by-step tutorial, we’ll cover:
✔ Preparing two datasets
✔ Creating a scatter plot
✔ Editing and customizing the plot
✔ Using secondary axes and adjusting axis scales
✔ Saving your chart as a picture

Let’s dive in! 🚀

---

🟢 Step 1: Prepare Your Data

For two datasets, arrange them side by side.

Example: Comparing Two Products’ Sales

Month	Product A Sales	Product B Sales
1	500	50
2	1000	120
3	1500	200
4	2000	350
5	3000	600

👉 Notice that Product A sales are much larger than Product B sales — this is where a secondary axis will be useful.

---

🟢 Step 2: Insert the Scatter Plot

1. Highlight your data (all three columns).

2. Go to the Insert tab → Charts group → Scatter (X, Y).

3. Select Scatter with only Markers.

✅ Now, Excel will plot two series (Product A and Product B) against Month.

---

🟢 Step 3: Add Chart Elements

1. Select the chart → Click Chart Elements (+).

2. Add:

   • Chart Title → “Comparison of Product A & B Sales Over Time”

   • Axis Titles →

     • X-axis = Months

     • Y-axis = Sales (Units)

   • Legend → To differentiate Product A and B.

---

🟢 Step 4: Insert a Secondary Axis

Since Product A sales are much higher, Product B might look too flat. Let’s fix that:

1. Right-click on Product B data points → Format Data Series.

2. In the Format pane → Check Plot Series on Secondary Axis.

✅ Now you’ll see two Y-axes:

• Left axis → Product A Sales

• Right axis → Product B Sales

This makes it easy to compare two datasets of different magnitudes.

---

🟢 Step 5: Scale the Axes

To improve clarity, adjust axis ranges manually:

For X-axis (Months):

1. Right-click X-axis → Format Axis.

2. Set:

   • Minimum = 0

   • Maximum = 6

   • Major Unit = 1

For Y-axis (Primary - Product A):

1. Right-click left Y-axis → Format Axis.

2. Set:

   • Minimum = 0

   • Maximum = 3500

   • Major Unit = 500

For Secondary Y-axis (Product B):

1. Right-click right Y-axis → Format Axis.

2. Set:

   • Minimum = 0

   • Maximum = 700

   • Major Unit = 100

👉 This way, both datasets are properly scaled and easy to compare.

---

🟢 Step 6: Customize Markers and Add Trendlines

Change Marker Style

• Product A → Blue circles, size 10

• Product B → Red squares, size 10

Add Trendlines

1. Right-click on Product A → Add Trendline → Linear.

2. Repeat for Product B.

3. Check Display Equation on chart and R² value for analysis.

---

🟢 Step 7: Final Touches

• Background: Right-click chart area → Format → Choose subtle color.

• Legend: Place below chart for a clean look.

• Data Labels (Optional): Right-click → Add Data Labels → Adjust position.

---

🟢 Step 8: Save Chart as a Picture

1. Click on the chart → Right-click → Save as Picture.

2. Choose format:

   • PNG (best for blogs/web)

   • JPEG (smaller size)

   • SVG (scalable, for presentations)

3. Save to your desired folder.

✅ Now you can insert the chart into documents, reports, or blog posts!

---

🟢 Example: Final Scatter Plot Features

Your final chart should have:

• Two datasets with different markers

• Secondary axis for fair comparison

• Customized scales for clarity

• Trendlines with equations

• A professional title, labels, and legend

• Exported as a high-quality image

---

🔑 Key Takeaways

• Scatter plots are best for showing relationships between two variables.

• When datasets have different scales, use a secondary axis.

• Always adjust axis scaling to make patterns visible.

• Save your chart as an image for easy use in reports, slides, and blogs.