How to Make a Scatter Plot in Excel

A scatter plot is one of the most effective ways to see if a relationship exists between two different sets of numbers. Instead of guessing whether your ad spend is driving more traffic or if more sales calls lead to more deals, a scatter plot can show you the connection at a glance. This article provides a step-by-step guide on how to create, customize, and interpret a scatter plot in Microsoft Excel.

What is a Scatter Plot and When Should You Use One?

A scatter plot, also known as a scattergram or scatter chart, uses dots to represent the values of two different numeric variables. One variable is plotted along the horizontal axis (x-axis), and the other is plotted along the vertical axis (y-axis). Each dot on the chart represents a single data point where the values of the two variables intersect.

The primary purpose of a scatter plot is to visualize the relationship, or correlation, between the two variables. It helps you answer questions like:

• As we spend more on advertising, do our sales go up?

• Does the number of hours a student studies correlate with their final exam score?

• Is there a relationship between an employee's years of experience and their productivity?

By looking at the pattern of the dots, you can quickly identify the nature of the relationship:

• Positive Correlation: As one variable increases, the other variable also tends to increase. The dots will generally move from the bottom-left to the top-right of the chart. Imagine plotting ice cream sales versus daily temperature - as it gets hotter, sales go up.

• Negative Correlation: As one variable increases, the other variable tends to decrease. The dots will drift from the top-left to the bottom-right. Think of the relationship between car speed and travel time, the faster you go, the less time it takes.

• No Correlation: There is no discernible pattern or relationship between the two variables. The dots will appear to be scattered randomly across the chart. For example, plotting a person's height against their IQ score would likely show no correlation.

Preparing Your Data for a Scatter Plot in Excel

Before you even think about creating a chart, getting your data organized correctly is the most important step. For a scatter plot, Excel needs two columns of numerical data, with each row representing a paired observation.

You need to identify your independent variable and your dependent variable:

• The Independent Variable (X-axis) is the variable you think might be causing a change. It’s what you control or observe. In Excel, this data should be in your first column.

• The Dependent Variable (Y-axis) is the variable that you think is affected by the independent variable. Its value "depends" on the other. In Excel, this data should be in the second column, right next to the first.

For example, if you want to see if your daily advertising spend impacts your daily website traffic, "Ad Spend" would be your independent variable (X-axis) and "Website Traffic" would be your dependent variable (Y-axis).

Here’s how you should format a sample dataset. Let's say you've been tracking ad spend and website sessions over the past two weeks:

Example Data Structure:

Step-by-Step Guide: Creating a Basic Scatter Plot in Excel

Once your data is properly arranged in two columns, creating the initial chart takes just a few clicks. Follow these steps:

Step 1: Select Your Data

Click and drag your mouse to highlight the cells containing your data, including the headers for each column. In our example, you would select the full range from "Daily Ad Spend ($)" down to the final number in the "Website Sessions" column.

Step 2: Go to the Insert Tab

With your data selected, click on the Insert tab in the Excel ribbon at the top of the window.

Step 3: Choose the Scatter Chart Option

In the Charts group, look for an icon that looks like a plot with scattered dots. This is the Insert Scatter (X, Y) or Bubble Chart button.

Click on this icon. A dropdown menu will appear showing several types of scatter and other charts. For most purposes, you’ll want the first one: the simple Scatter chart. Click on it.

That's it! Excel will instantly generate and place a default scatter plot on your worksheet. You’ll see "Daily Ad Spend ($)" on the x-axis and "Website Sessions" on the y-axis, with blue dots representing each day’s data pair.

Customizing Your Scatter Plot for Clarity

A default Excel chart is a good start, but it often lacks the proper labels and context to be easily understood by others. Customizing your scatter plot makes it more professional, readable, and insightful.

To begin customizing, click on your chart. You'll see several new tabs appear in the ribbon (like "Chart Design" and "Format"), as well as three small icons on the right side of the chart itself. We'll primarily use the + icon (Chart Elements) to add key components.

1. Give Your Chart a Clear Title

The default title might just be your y-axis header. Double-click the chart title to edit the text. Make it descriptive and concise. For our example, a good title would be "Relationship Between Daily Ad Spend and Website Sessions."

2. Add Axis Titles

Your axes need labels so your audience knows what the numbers represent.

• Click the + icon to the right of your chart.

• Check the box next to Axis Titles.

• Excel will add placeholder text boxes on both the horizontal (X) and vertical (Y) axes.

• Click on each placeholder to rename them. Label the X-axis "Daily Ad Spend ($)" and the Y-axis "Website Sessions". This removes ambiguity and makes your chart instantly understandable.

3. Add a Trendline to Show the Relationship

A trendline is a straight or curved line that visually indicates the general direction of the data points. It’s the single most powerful addition to a scatter plot for interpreting the correlation.

• Click the + icon again.

• Check the box next to Trendline. Excel will automatically add a straight (linear) trendline to your chart.

• For more advanced options, click the arrow next to Trendline and select More Options.... This opens a formatting pane where you can choose other types of trendlines (like Exponential or Logarithmic), though Linear is the most common.

4. Display the R-squared Value for Statistical Significance

While still in the "Format Trendline" pane, you can add a powerful statistical measure called the R-squared (R²) value. In simple terms, R² tells you how much of the variation in your dependent variable (Website Sessions) can be explained by your independent variable (Ad Spend). It’s a value between 0 and 1.

• An R² of 0.85 means that 85% of the change in website sessions is explained by the change in ad spend, which indicates a strong relationship.

• An R² of 0.10 would indicate a very weak relationship.

To display it, scroll down in the "Format Trendline" pane and check the box for Display R-squared value on chart. Excel will add a small text box with the R² value directly onto your plot.

How to Read and Analyze Your Scatter Plot

Now that you have a well-formatted chart, what does it actually tell you? Interpreting the plot is the final and most important step.

1. Identify the Direction of the Correlation

Look at the overall pattern of the dots and the slope of your trendline.

• If the trendline slopes upward from left to right, you have a positive correlation. In our example, as ad spend increases, website sessions also increase. Great news for your marketing efforts!

• If the trendline slopes downward, you'd have a negative correlation.

• If the trendline is nearly flat and the dots are everywhere, there is likely little to no correlation.

2. Analyze the Strength of the Correlation

How closely do the points follow the trendline?

• If the dots are tightly clustered around the trendline, the relationship is strong. Your R-squared value will be high (e.g., above 0.70). This suggests your model is a good fit.

• If the dots are widely scattered far from the trendline, the relationship is weak. Your R-squared value will be low (e.g., below 0.30). Other factors are likely influencing your results.

3. Look for Outliers

Are there any points that are far away from the main cluster and the trendline? These are called outliers, and they represent unusual cases. In our example, an outlier could be a day where you had very high traffic with very low ad spend. This might point to an external event, like a viral social media post or an email campaign, and it's worth investigating separately.

Final Thoughts

Creating a scatter plot in Excel is a straightforward process that transforms two columns of numbers into a powerful visual insight. It's the perfect tool for helping you quickly confirm suspected relationships in your data, whether you are in marketing, sales, finance, or operations.

As powerful as Excel is, the process of exporting CSVs from various platforms, cleaning the data, and manually building charts can become tedious, especially with reports you need to run every week. At Graphed, we automate this entire process. You can connect your platforms like Google Analytics, Shopify, and Facebook Ads directly, and then use simple conversation to get answers. Instead of a multi-step manual process, you could just ask, "Create a scatter plot comparing my daily Facebook Ads spend to Shopify sales this quarter," and we deliver a live, interactive chart in seconds - keeping you focused on insights, not manual tasks.