How to Create a Scatter Plot in Excel: Step-by-Step

Key Insights

• Scatter plots reveal relationships between two variables that other chart types obscure—use them when you need to identify correlations, clusters, or outliers in your data.
• Always place your independent variable (the cause) in the X column and dependent variable (the effect) in the Y column before creating the chart—Excel plots data in the order you select it.
• Adding a trendline with its R² value transforms a scatter plot from a visual aid into a statistical tool that quantifies the strength of relationships in your data.

Introduction to Scatter Plots

Scatter plots are the workhorse of correlation analysis. When you need to understand whether two variables move together—and how strongly—a scatter plot shows you the answer at a glance. Each point represents a single observation, with its position determined by two measurements.

Use scatter plots when you’re asking questions like: Does increased advertising spend lead to higher sales? Is there a relationship between employee tenure and productivity? Do temperature changes affect equipment failure rates?

Excel handles scatter plots well enough for most business analysis. You don’t need R or Python to explore correlations in datasets under a few thousand rows. The built-in trendline and equation features give you regression analysis without writing formulas.

Preparing Your Data

Scatter plots require exactly two numeric variables arranged in columns. The independent variable (what you think causes the change) goes in the left column. The dependent variable (what you’re measuring the effect on) goes in the right column.

Here’s a sample dataset tracking advertising spend against monthly sales:

| Month     | Ad Spend ($) | Sales Revenue ($) |
|-----------|--------------|-------------------|
| January   | 5,000        | 42,000            |
| February  | 7,500        | 51,000            |
| March     | 4,200        | 38,500            |
| April     | 8,000        | 58,000            |
| May       | 6,500        | 47,500            |
| June      | 9,200        | 62,000            |
| July      | 3,800        | 35,000            |
| August    | 10,500       | 71,000            |
| September | 7,000        | 49,500            |
| October   | 11,000       | 74,000            |
| November  | 8,500        | 59,500            |
| December  | 12,000       | 82,000            |

Before creating your chart, clean your data:

1. Remove or handle missing values. Excel will skip rows with empty cells, which can distort your visualization. Either delete incomplete rows or use interpolation to fill gaps.

2. Convert text to numbers. If your cells show numbers but are stored as text (look for the green triangle warning), select the range, click the warning icon, and choose “Convert to Number.”

3. Remove currency symbols and commas from raw data. Store pure numbers and apply formatting separately. A cell containing “$5,000” as text won’t plot correctly.

4. Check for outliers. Extreme values compress your scatter plot and hide patterns in the majority of your data. Decide whether to include, exclude, or annotate outliers before charting.

Creating a Basic Scatter Plot

Follow these steps to generate your first scatter plot:

Step 1: Select your two data columns, including headers. For the sample data above, select cells B1:C13 (Ad Spend and Sales Revenue columns).

Step 2: Navigate to the Insert tab on the Excel ribbon.

Step 3: In the Charts group, click the scatter chart icon (it looks like dots scattered in a square).

Step 4: Choose your scatter plot subtype:

Scatter Plot Subtypes in Excel:
├── Scatter (dots only) ← Use this for correlation analysis
├── Scatter with Smooth Lines
├── Scatter with Smooth Lines and Markers
├── Scatter with Straight Lines
└── Scatter with Straight Lines and Markers

Select “Scatter” (the first option) for standard correlation analysis. The line variations connect points in data order, which rarely makes sense for scatter analysis—they’re better suited for time series that happen to have two Y variables.

Step 5: Excel generates a basic chart. It’s functional but ugly. The next sections fix that.

Customizing Chart Elements

A raw Excel scatter plot lacks context. Your audience won’t know what they’re looking at without proper labels.

Click your chart to activate the Chart Design and Format tabs. Here’s what to add:

Chart Title: Click the default “Chart Title” text and replace it with something descriptive: “Advertising Spend vs. Sales Revenue (2024)”. Don’t just label your axes again—tell readers what insight to look for.

Axis Titles: Click the chart, then click the green plus icon (Chart Elements) that appears. Check “Axis Titles.” Label your X-axis “Monthly Ad Spend ($)” and Y-axis “Monthly Sales Revenue ($)”.

Gridlines: Keep major gridlines for readability. Remove minor gridlines unless you need precise value reading.

Legend: For single-series scatter plots, the legend wastes space. Uncheck it in Chart Elements. For multi-series plots, keep it and position it where it doesn’t overlap data points.

Here’s the transformation in structure:

BEFORE (Default Chart):
- Generic "Chart Title" text
- No axis labels
- Unnecessary legend
- Default blue dots
- Auto-scaled axes

AFTER (Customized Chart):
- Descriptive title with date range
- Clear axis labels with units
- No legend (single series)
- Larger, contrasting markers
- Manually scaled axes starting at logical values

Adding a Trendline and Equation

Trendlines turn scatter plots into analytical tools. They show the best-fit line through your data and quantify the relationship mathematically.

Adding a Trendline:

1. Click any data point in your scatter plot to select the series.
2. Right-click and select “Add Trendline.”
3. In the Format Trendline pane, choose your regression type:

Trendline Types:
├── Linear ← Start here for most business data
├── Exponential (for growth curves)
├── Logarithmic (for diminishing returns)
├── Polynomial (for complex curves)
└── Power (for scientific relationships)

4. Check “Display Equation on chart” and “Display R-squared value on chart.”

For the advertising vs. sales data, a linear trendline produces:

y = 5.89x + 12,847
R² = 0.987

Interpreting the Results:

• The equation (y = 5.89x + 12,847): For every dollar spent on advertising, sales increase by approximately $5.89. The baseline sales (with zero advertising) would theoretically be $12,847.

• R² = 0.987: This means 98.7% of the variation in sales is explained by advertising spend. Values above 0.7 indicate strong correlation. Values below 0.3 suggest weak or no linear relationship.

Don’t confuse correlation with causation. The R² value tells you how well the variables move together, not whether one causes the other. Your advertising might drive sales, or a third factor (like seasonality) might drive both.

Advanced Formatting Options

When preparing scatter plots for reports or presentations, default formatting won’t cut it.

Adjusting Axis Scales:

Right-click an axis and select “Format Axis.” Set minimum and maximum bounds manually. Starting your Y-axis at zero prevents visual exaggeration of small differences. For the sales data:

X-Axis (Ad Spend):
  Minimum: 0
  Maximum: 15,000
  Major Unit: 2,500

Y-Axis (Sales Revenue):
  Minimum: 0
  Maximum: 100,000
  Major Unit: 20,000

Changing Marker Styles:

Click any data point, then right-click and select “Format Data Series.” Under Marker Options, you can change:

• Marker type (circle, square, triangle, diamond)
• Size (6-8 points works well for most charts)
• Fill color (use your brand colors or high-contrast options)
• Border color and width

Adding Multiple Data Series:

To compare relationships across categories (e.g., advertising effectiveness by region), structure your data with separate columns for each series:

| Ad Spend | Sales (North) | Sales (South) | Sales (West) |
|----------|---------------|---------------|--------------|
| 5,000    | 42,000        | 38,000        | 45,000       |
| 7,500    | 51,000        | 48,500        | 54,000       |
| ...      | ...           | ...           | ...          |

Create the chart with the first two columns, then right-click the chart and select “Select Data.” Add each additional series, specifying the Ad Spend column as X values and each regional sales column as Y values.

Use distinct colors and marker shapes for each series. Add a legend when plotting multiple series.

Common Mistakes and Troubleshooting

Switched X and Y Axes:

Symptoms: Your independent variable appears on the Y-axis, or the relationship looks inverted from what you expected.

Fix: Right-click the chart, select “Select Data,” click “Edit” on your series, and swap the X and Y value ranges.

Non-Numeric Data Errors:

Symptoms: Excel won’t create the chart, or points are missing.

Fix: Select your data range and check the Number format in the Home tab. Convert text-formatted numbers by selecting the range, clicking the warning icon, and choosing “Convert to Number.” Remove any non-numeric characters from cells.

Misleading Scale Choices:

Symptoms: Small differences look dramatic, or significant patterns appear flat.

Fix: Always consider whether your axis should start at zero. Truncated axes exaggerate variation. If you must use a non-zero baseline, clearly label it and note the truncation in your chart title or caption.

Overcrowded Data Points:

Symptoms: With hundreds of points, individual observations become indistinguishable blobs.

Fix: Reduce marker size, increase transparency (Format Data Series > Fill > adjust transparency slider), or consider using a density plot instead. For very large datasets, switch to specialized tools like Python’s matplotlib or R’s ggplot2.

Scatter plots remain one of the most informative visualizations for understanding variable relationships. Master the basics in Excel, and you’ll have a foundation for more sophisticated statistical analysis when your questions outgrow spreadsheet capabilities.