How to Make a Scatter Plot in Excel with AI

A scatter plot is one of the best ways to see if there’s a relationship between two different things you’re measuring, like how your ad spend affects sales or if more website traffic leads to more sign-ups. While creating one in Excel has always been possible, it often involves a lot of clicking, formatting, and tweaking things just right. This article covers the traditional way to build a scatter plot in Excel from scratch, and then explores how new AI features can help you create them almost instantly.

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

A scatter plot (or scatter chart) uses dots to represent the values of two different numeric variables. One variable goes on the horizontal (X) axis, and the other goes on the vertical (Y) axis. The position of each dot on the chart shows the values for an individual piece of data, and the overall pattern of the dots reveals the relationship - or correlation - between the two variables.

You’re essentially asking: "When this thing goes up, what happens to that other thing?"

This type of chart is incredibly useful for spotting trends that aren't obvious in a simple table of data. Here are a few common scenarios where a scatter plot shines:

• Marketing Analysis: Is there a connection between my daily Facebook Ads spend (X-axis) and the number of conversions I get (Y-axis)?

• Sales Performance: Does a sales rep making more calls (X-axis) result in closing more deals (Y-axis)?

• E-commerce Operations: Does offering a higher discount percentage (X-axis) lead to a higher average order value (Y-axis), or does it hurt it?

• Product Development: Is there a relationship between app load time (X-axis) and user engagement (Y-axis)?

By plotting your data, you can quickly see one of three patterns:

• Positive Correlation: As the X-axis variable increases, the Y-axis variable also tends to increase. The dots will appear to move up and to the right. *(Example: More ad spend leads to more revenue).)

• Negative Correlation: As the X-axis variable increases, the Y-axis variable tends to decrease. The dots will appear to move down and to the right. *(Example: Higher product price leads to fewer units sold).)

• No Correlation: There is no clear pattern. The dots are scattered randomly across the chart. *(Example: Number of social media followers has no relationship with customer support tickets).)

The main goal is to turn raw data into a visual story that can guide your decisions.

The Traditional Way: How to Make a Scatter Plot in Excel

Before AI tools entered the scene, making a scatter plot was a manual process. It's straightforward, but involves several steps to get from raw data to a useful visualization. Let’s walk through it with a classic marketing example: tracking ad spend vs. website traffic.

Step 1: Get Your Data Ready

First, you need two columns of data. One represents your independent variable (the thing you control or change), which goes on the X-axis. The other is your dependent variable (the thing you are measuring), which goes on the Y-axis.

For a clean chart, organize your data with the X-axis variable in the left column and the Y-axis variable in the right. Make sure the headers are clear.

Here’s our sample data:

In this case, Ad Spend ($) is our X-axis variable and Website Sessions is our Y-axis variable.

Step 2: Select Your Data

Click and drag your mouse to highlight the two columns containing your X and Y data, including the headers. Don't include the 'Date' column for a scatter plot, as we’re only comparing Ad Spend to Website Sessions.

Step 3: Insert the Scatter Chart

With your data selected, go to the Excel ribbon at the top of the screen:

1. Click on the Insert tab.

2. In the Charts group, find the little icon that looks like a plot with dots on it. This is the "Insert Scatter (X, Y) or Bubble Chart" menu.

3. Click it, and a dropdown menu will appear. Select the first option, the basic Scatter chart.

Excel will instantly generate a scatter plot and place it on your worksheet. You’ll see a collection of dots, each representing a day's ad spend and resulting website sessions.

Step 4: Customize Your Chart and Add a Trendline

A bare scatter plot is a good start, but the real insight comes from customization. This is where most of the manual work happens.

Add Chart and Axis Titles:

Your chart needs context. Click on the chart, and a plus sign (+) icon will appear on the top right. Click it and check the boxes for Chart Title and Axis Titles.

• Double-click the Chart Title to rename it to something descriptive, like "Ad Spend vs. Website Sessions."

• Click the horizontal axis title and rename it "Ad Spend ($)."

• Click the vertical axis title and rename it "Website Sessions."

Add a Trendline:

The most powerful feature of a scatter plot is the trendline, which shows the overall direction of the data. To add one:

1. Right-click on any of the data points in your chart.

2. From the context menu, select Add Trendline...

3. A "Format Trendline" panel will appear on the right side of your screen. Linear is usually selected by default, which is perfect for most cases.

4. For extra analytical power, scroll down in the panel and check the boxes for "Display Equation on chart" and "Display R-squared value on chart."

The R-squared value tells you how well your data fits the trendline, ranging from 0 to 1. A value closer to 1 indicates a stronger correlation. This simple number can help you feel more confident about the relationship you're seeing.

After these steps, you have a complete, insightful scatter plot. While effective, the process is undeniably manual - it requires knowing where to click and what to format. This is where AI starts to change the game.

The Faster Way: Using AI to Create a Scatter Plot

Manually building charts is a tedious process that non-technical users often avoid. Digging through menus and formatting options takes you away from the more important task: understanding what the data actually means. AI-powered features in Excel and other analytics tools are designed to eliminate this friction.

Using Excel’s Built-In "Analyze Data" (Formerly "Ideas")

Microsoft has integrated a feature called Analyze Data directly into Excel. It automatically scans your dataset and suggests relevant charts, pivot tables, and insights.

Here’s how to use it:

1. Select your data range, just as you would manually.

2. Go to the Home tab.

3. On the far right, click the Analyze Data button.

A pane will open on the right, proposing various visualizations based on its analysis. If your data has a potential correlation, Excel will often suggest a scatter plot titled something like "Website Sessions and Ad Spend ($)."

This feature is a fantastic starting point and can build a chart in one click. However, it's a suggestion engine - it might not always recommend the exact chart you need, and any further customizations, like adding an R-squared value, still have to be done manually.

Using AI Copilot in Microsoft 365

The true leap forward is conversational AI, like Microsoft’s Copilot. If you have a Microsoft 365 subscription with this feature enabled, you can skip the menus entirely and create charts just by typing what you want in plain English.

With your data table selected, you could open the Copilot interface and type a prompt like:

Create a scatter plot of Ad Spend vs. Website Sessions.

Copilot will generate the chart for you. But it doesn't stop there. You can continue the conversation to refine it:

• Add a linear trendline to this chart.

• What is the correlation between these two variables? Show the R-squared value.

• Change the chart title to 'Impact of Advertising on Site Traffic'.

This conversational approach turns hours of reporting busy-work into a quick conversation. It massively lowers the barrier for team members who aren't data experts. You no longer need to learn Excel’s complex features, you just have to know what question to ask.

Why AI is a Game-Changer for Spreadsheet Analysis

Moving from manual chart creation to a conversational AI model isn't just a small-time saver - it fundamentally changes how you work with data.

1. It Turns Hours of Work into Seconds

The typical weekly reporting process often involves downloading CSVs, cleaning data in Excel, and manually building the same visualizations over and over. That process can take hours. AI tools automate the busy work - writing formulas, creating charts, and applying formatting - so you can jump straight to the insights.

2. It Makes Data Analysis Accessible to Everyone

You no longer need to be a "data person" to get answers. People who aren't familiar with pivot tables or VLOOKUP can now ask questions about company data in plain language. This democratizes data and allows marketers, salespeople, founders, and product managers to make better, data-informed decisions without waiting on a dedicated analyst.

3. It Encourages Deeper Exploration

When creating a chart is difficult, you often stop after getting the first answer. But when it's as easy as typing a question, one visual often sparks another question. You can drill down easily: "Okay, it looks like ad spend is working. Now show me this by channel." or "Let's filter this just for last month." This creates a fluid exploration process where you can follow your curiosity and uncover insights you would have otherwise missed.

Final Thoughts

Creating a scatter plot in Excel to find relationships between variables is a powerful analytical skill. You can absolutely build a professional, useful chart using the traditional manual method, and knowing those steps is valuable. However, the rise of AI tools, especially conversational ones like Copilot, is making the entire process faster, easier, and more accessible than ever before.

We built Graphed to take this idea a step further, helping marketing and sales teams get answers without wrangling any spreadsheets at all. Instead of copying and pasting CSVs, you can connect directly to live data sources like Google Analytics, Shopify, Salesforce, and Facebook Ads. From there, you just ask questions in plain English to build real-time monitoring dashboards, giving your whole team instant access to the insights they need to grow the business.