The Easiest Way to Make a Scatterplot in Excel

A scatterplot is one of the most effective tools for spotting relationships between two sets of numbers, such as sales versus advertising spend or temperature versus energy use. Microsoft Excel makes it simple to turn raw data into this clear visual format, helping you quickly identify patterns, trends, or outliers.

What Is a Scatterplot in Excel?

A scatterplot, sometimes called an XY chart, is a graph that shows the relationship between two sets of numeric values. In Excel, each point on the plot represents one data record, with its horizontal (X) position based on one variable and its vertical (Y) position based on another. For example, you might place monthly advertising spending on the X-axis and corresponding sales revenue on the Y-axis. The resulting collection of points reveals whether the two variables move together—upward, downward, or not at all.

Unlike line charts that connect dots in a sequence, a scatterplot treats every point as an independent pair. This makes it especially useful for spotting correlations, clusters, or unusual values that stand apart from the rest. Excel offers several scatterplot styles, including markers only, smooth lines with markers, or straight lines. You can add a trendline to highlight the overall direction of the data. Understanding this basic structure is the first step to building your own scatterplot effectively.

When to Use a Scatterplot

To show the relationship between two continuous variables This is the core purpose. Use it when you want to see if X and Y move together, in opposite directions, or not at all.

• Positive correlation: As X increases, Y increases (e.g., height vs. weight)
• Negative correlation: As X increases, Y decreases (e.g., car speed vs. travel time)
• No correlation: No discernible pattern (e.g., shoe size vs. IQ)

To identify patterns or clusters Scatterplots excel at revealing groupings in data that might not be obvious in tables:

• Customer segments based on spending vs. visit frequency
• Species clusters in biological measurements (like the famous Iris dataset)

To spot outliers Points that fall far from the general pattern immediately stand out — a single anomalous reading, a data entry error, or a genuinely unusual case.

To visualize distributions across two dimensions Unlike a histogram which shows one variable’s distribution, a scatterplot shows how two variables are jointly distributed.

When you have a reasonable number of data points Scatterplots work best with dozens to thousands of points. With too few points, patterns are hard to judge; with millions, overplotting becomes a serious problem.

When NOT to Use a Scatterplot

Situation	Better Alternative
One variable is categorical	Bar chart, box plot
You want to show trends over time	Line chart
You need to show parts of a whole	Pie chart, stacked bar
You have too many points (severe overplotting)	Heatmap, 2D histogram, or sample the data
You want to show a precise ranking	Horizontal bar chart
One or both variables are discrete with few unique values	Jittered scatterplot or strip plot

How to Create a Scatterplot in Excel

Step 1: Prepare Your Data

Arrange your data in two columns:

X-axis values	Y-axis values
10	25
15	30
20	45
25	50
30	65

• X values (independent variable) typically go in the left column
• Y values (dependent variable) go in the right column
• Include headers in row 1 (Excel will use them for the legend)

Step 2: Select Your Data

1. Click and drag to highlight both columns of data, including headers.
2. Make sure you don’t include empty cells or unrelated data in your selection.

Step 3: Insert the Scatter Chart

1. Go to the Insert tab on the ribbon.
2. In the Charts group, click Insert Scatter (X, Y) or Bubble Chart — it looks like a scatterplot icon with dots.
3. Choose the type you want:
   • Scatter — basic dots
   • Scatter with Smooth Lines — connects points with curves
   • Scatter with Straight Lines — connects points with straight lines

Tip: For most data analysis, choose the basic Scatter option. Only use line versions if your X-axis represents ordered sequences (like time).

Step 4: Customize the Chart

Add Chart Elements

Click the + icon next to the chart to add:

• Chart Title — describe what the plot shows
• Axis Titles — label your X and Y axes
• Trendline — to show the relationship direction (Linear, Exponential, etc.)
• Data Labels — to show exact values on points

Format the Chart

• Double-click any element (title, axis, dots) to open the Format pane
• Right-click data points → Format Data Series to change marker style, size, or color
• Right-click the axis → Format Axis to adjust scale, bounds, or units

Step 5: Add a Trendline (Optional but Recommended)

1. Click on any data point in the chart.
2. Click the + (Chart Elements) button.
3. Check Trendline.
4. Click the arrow next to it → More Options.
5. In the Format Trendline pane, choose:
   • Linear for straight-line relationships
   • Exponential or Polynomial for curved patterns
   • Check Display Equation on chart and Display R-squared value for statistical context

How to Customize a Scatterplot in Excel

1. Format Data Points

Change marker appearance:

1. Click any data point in the series (all points highlight).
2. Right-click → Format Data Series.
3. Under Marker Options:
   • Choose Built-in and pick a shape (circle, square, triangle, etc.)
   • Adjust Size (default is 5; try 7–10 for visibility)
4. Under Fill: Choose solid color, gradient, or No fill for hollow markers.
5. Under Border: Add an outline color and width for better definition.

Vary point colors by value:

• In the Format Data Series pane, under Fill → check Vary colors by point for automatic rainbow coloring.

2. Add and Format Axis Titles

1. Click the + (Chart Elements) → check Axis Titles.
2. Click each title box and type your label (e.g., “Advertising Spend ($)” or “Units Sold”).
3. Format: Select the title → Home tab → adjust font, size, bold, color.

Tip: Always include units in axis titles so the chart is self-explanatory.

3. Adjust Axis Scales and Units

When data is clustered or you want to focus on a specific range:

1. Right-click the axis → Format Axis.
2. Under Axis Options:
   • Bounds: Set Minimum and Maximum to zoom in on relevant ranges
   • Units: Adjust Major and Minor unit spacing
   • Horizontal axis crosses: Move where the Y-axis intersects the X-axis

Use logarithmic scale:

• Check Logarithmic scale when data spans several orders of magnitude (e.g., 1 to 1,000,000).

4. Add and Customize Trendlines

1. Click + → Trendline → More Options.
2. Choose the type that fits your data:TableTypeUse WhenLinearStraight-line relationshipExponentialRapid growth or decayPolynomialCurved patterns (set Order: 2 for U-shapes, 3 for S-shapes)LogarithmicRapid initial change that levels offPowerY proportional to X raised to a power
3. Format the line: Change color, width, and dash type under Line options.
4. Check Display Equation on chart and Display R-squared value for analysis context.

5. Add Data Labels

1. Click + → Data Labels → More Options.
2. In the Format Data Labels pane, choose what to show:
   • X Value, Y Value, or Series Name
   • Value From Cells: Link labels to a separate column (great for naming outliers or categories)
3. Position: Above, Below, Left, Right, or Center.
4. Format text: Font size, color, and background fill to ensure readability against points.

6. Format the Chart Area and Background

Chart background:

• Click the chart background → Format Chart Area → Fill → Solid color, gradient, or No fill (transparent for reports).

Plot area:

• Click inside the plot (the grid area) → Format Plot Area → adjust fill or border.

Gridlines:

• Click + → Gridlines → add or remove major/minor lines.
• Format: Right-click gridlines → change color to light gray and reduce width so they don’t compete with data.

7. Use Color to Represent a Third Variable

Categorical third variable (groups):

• Arrange data with each group in its own column:TableGroup A XGroup A YGroup B XGroup B Y
• Select all four columns → Insert → Scatter. Excel colors each series differently.
• Click + → Legend to identify groups.

Continuous third variable (bubble chart):

• Add a third column for point size.
• Select all three columns → Insert → Bubble Chart.
• Format bubble sizes under Format Data Series → Scale bubble size to.

8. Handle Overlapping Points (Overplotting)

When you have many data points:

Technique	How To
Transparency	Format Data Series → Fill → set transparency to 50–70%
Smaller markers	Reduce marker size to 2–3
Jitter	Add small random values to coordinates (requires formula manipulation)
Hexbin/2D histogram	Not native in Excel; consider using Python or R for very dense data

9. Add Error Bars

1. Click + → Error Bars → More Options.
2. Choose direction: Both, Plus, or Minus.
3. Under Error Amount:
   • Fixed value: Same error for all points
   • Percentage: Proportional to the value
   • Standard deviation or Standard error
   • Custom: Specify your own error values from worksheet columns

10. Multi-Series and Combination Charts

Add a second data series to an existing chart:

1. Right-click the chart → Select Data.
2. Click Add → select new X and Y ranges.
3. Format each series independently by clicking its points.

Combo chart (scatter + line):

• Useful when overlaying a benchmark or target line.
• Add a series with just two points (start and end) → format as line with no markers.

Quick Reference: Formatting Shortcuts

Task	Shortcut
Open Format pane	Double-click any chart element
Copy formatting	Select element → Ctrl+C → select target → Ctrl+V
Reset to default	Right-click → Reset to Match Style
Change all text font	Select chart → Home tab → adjust font

How to Create a Scatterplot with Multiple Data Series

Method 1: Side-by-Side Columns (Recommended)

Step 1: Structure Your Data

Arrange each series in paired columns — X values first, then Y values, repeating for each series:

Table

Product A Sales	Product A Profit	Product B Sales	Product B Profit	Product C Sales	Product C Profit
100	20	120	15	80	25
150	35	140	22	110	30
200	45	180	30	160	42
250	55	220	38	190	48

Rules:

• Each series needs its own X and Y column
• Use headers in row 1 — Excel will use these for the legend
• Series can have different numbers of rows (unequal lengths are fine)

Step 2: Insert the Chart

1. Select all data columns including headers
2. Go to Insert → Scatter (X, Y) or Bubble Chart → Scatter
3. Excel automatically assigns a different color/marker to each series

Step 3: Verify Series Assignment

If colors look wrong:

1. Right-click the chart → Select Data
2. In the dialog, each series should show as:
   • Series name: =Sheet1!$B$1 (the header)
   • Series X values: =Sheet1!$A2: A$5
   • Series Y values: =Sheet1!$B2: B$5
3. Click Edit to correct any misaligned ranges

Method 2: Adding a Series to an Existing Chart

Use this when you already have one scatterplot and want to layer in more data.

Step 1: Open Select Data Dialog

1. Right-click the existing chart → Select Data

Step 2: Add the New Series

1. Click Add under Legend Entries (Series)
2. Fill in the dialog:
   • Series name: Click the cell with your header, or type a name
   • Series X values: Select the X-axis data range
   • Series Y values: Select the Y-axis data range
3. Click OK → OK

Repeat for each additional series.

Method 3: Category Column with Filters (Better for Many Groups)

If you have a long dataset with a category label:

Category	Sales	Profit
Product A	100	20
Product A	150	35
Product B	120	15
Product B	140	22
Product C	80	25
Product C	110	30

Excel can’t split this automatically for scatterplots. You have two options:

Option A — Pivot to separate columns: Use formulas or copy-paste to restructure into Method 1’s side-by-side format.

Option B — Manual series addition: Use Method 2, but for each series, manually select only the rows belonging to one category. This is tedious for many groups.

Recommendation: For datasets with 4+ categories, restructure into Method 1 before charting.

Formatting Multiple Series

Distinguish Series Visually

Click any point in a series → right-click → Format Data Series → Marker Options:

Table

Technique	When to Use
Different colors	Default; best for 2–5 series
Different shapes	Add under Marker Options → Built-in; critical for colorblind accessibility
Different sizes	Emphasize one series over others
Filled vs. hollow	Good for black-and-white printing

Accessibility tip: Don’t rely on color alone. Use shape + color combinations (circles, squares, triangles, diamonds).

Edit the Legend

1. Click the legend once to select it
2. Click again on a single entry to select just that series name
3. Type to rename directly on the chart, or edit the source header cell

To move the legend: Click + → Legend → choose position (Right, Top, Bottom, Left). For many series, Right or Bottom works best.

Add Individual Trendlines

1. Click a specific series (not the whole chart)
2. + → Trendline → More Options
3. Format trendline color to match its series
4. Repeat for each series you want to trend

Advanced: Combination Elements

Highlight One Series

Make one series stand out as a benchmark:

1. Format the benchmark series with:
   • Larger markers (size 10–12)
   • Bold border
   • Hollow fill with thick colored outline
2. Format other series with:
   • Smaller markers (size 4–5)
   • Semi-transparent fill (50% transparency)

Add a Reference Line

Overlay a target or average line across all series:

1. Add a new series with just 2 points: (min X, target Y) and (max X, target Y)
2. In Select Data, add this series
3. Click the new series → Change Series Chart Type → select Line with Markers or Line
4. Format the line: No markers, dashed style, neutral color (gray or black)

FAQs