Graphed LogoGraphed
Login

How to Make an X Y Graph in Excel with AI

Cody Schneider•

Trying to see how two different numbers relate to each other? An X Y graph, also known as a scatter plot, is the perfect tool for the job. It’s one of the simplest ways to visually spot a relationship, like whether your ad spend is actually leading to more sales. This guide will show you how to set up your data and create an X Y graph in Excel, covering the traditional manual method and the faster, smarter approach using Excel’s built-in AI features.

What is an X Y Graph (and Why Should You Care)?

An X Y graph, or scatter plot, uses dots to represent the values for two different numeric variables. The position of each dot on the horizontal (X) and vertical (Y) axis indicates its value. By plotting all your data points this way, you can see the relationship between the two variables at a glance.

You’re not looking at a trend over time (like a line graph), but rather hunting for correlation. In other words: when variable X goes up, what happens to variable Y? Does it go up too? Does it go down? Or is there no connection at all?

Here are a few common business scenarios where an X Y graph is incredibly useful:

  • Marketing: Plotting Ad Spend (X-axis) vs. Website Conversions (Y-axis) to see if more spending leads to more conversions.

  • Sales: Mapping the number of sales calls a rep makes (X-axis) against the number of deals closed (Y-axis) to find a sweet spot for activity.

  • E-commerce: Visualizing Average Order Value (X-axis) vs. Customer Lifetime Value (Y-axis) to see if higher initial purchases lead to more loyal customers.

The goal is to turn a confusing table of numbers into a clear picture that either confirms a gut feeling or reveals a surprising insight you might have missed.

Step 1: Get Your Data Ready for Plotting

Before you can create any chart, your data needs to be structured properly. For an X Y graph in Excel, this is very straightforward. You just need two columns of numerical data sitting side-by-side.

The most important rule is to place your independent variable in the left column (this will be your X-axis) and your dependent variable in the right column (your Y-axis).

  • The independent variable (X) is the one you control or that you suspect is causing a change (e.g., Ad Spend, Number of Sales Calls).

  • The dependent variable (Y) is the one you believe is affected by the independent variable (e.g., Sales Revenue, Deals Closed).

Here’s a simple example of how your data should look. Let's say we want to analyze the relationship between monthly ad spend and monthly sales for the last year.

Your table in Excel should be set up like this:

Monthly Ad Spend

Monthly Sales

$1,000

$12,500

$1,500

$18,000

$1,200

$16,200

$2,000

$25,000

$2,500

$31,000

$2,300

$28,500

$3,000

$35,000

$3,500

$41,000

$2,800

$33,000

$4,000

$48,000

$4,200

$51,500

$5,000

$58,000

Make sure your columns have clear headers, and the data inside is formatted as numbers or currency. With your data organized this way, you’re ready to start visualizing.

Step 2: Create an X Y Graph the Classic Way

Let's start with the traditional, manual approach. It only takes a few clicks and gives you full control over the initial chart setup.

  1. Select your data: Click and drag your cursor to highlight both columns of data, including the headers.

  2. Go to the Insert Tab: At the top of Excel, click on the "Insert" tab in the ribbon.

  3. Find the Charts Group: Look for the Charts section. You’ll see icons for various types like bar charts, line charts, and pie charts.

  4. Click the 'Scatter (X, Y)' Icon: Find the icon that looks like a plot with a bunch of dots on it. Hovering over it will show the label "Insert Scatter (X, Y) or Bubble Chart". Click on it.

  5. Choose the Basic Scatter Plot: A dropdown menu will appear with several options. For a standard X Y graph, select the first option under "Scatter," which is just the dots without any connecting lines.

That's it! Excel will instantly drop a basic X Y graph onto your spreadsheet. You’ll see your "Monthly Ad Spend" on the horizontal X-axis and "Monthly Sales" on the vertical Y-axis, with dots representing each month's data pair.

Quickly Make It Look Professional

A naked chart isn't very helpful. Always take a minute to add titles and labels so anyone can understand what it represents.

  • Add a Chart Title: Double-click the default "Chart Title" at the top and give it a descriptive name, like "Monthly Sales vs. Ad Spend."

  • Add Axis Titles: Click on your chart. A plus sign (+) icon should appear on the top right. Click it, and check the box for "Axis Titles." Now you can edit the titles for both your X and Y axes, labeling them "Ad Spend" and "Sales," respectively.

Step 3: Let AI Create Your X Y Graph in Excel

The manual method is easy enough, but Excel's integrated AI tools can make the process even faster and help you discover insights you weren't even looking for. These features take the guesswork out of choosing the right chart for your data.

Using "Recommended Charts"

If you're not entirely sure which chart type to use, "Recommended Charts" is your best friend. Excel analyzes your selected data and suggests several visualizations that fit its structure.

  1. Select your data: Just as before, highlight your two columns of data, including the headers.

  2. Navigate to Insert > Recommended Charts: In the "Insert" tab, instead of picking a specific chart icon, click the "Recommended Charts" button.

  3. Find the Scatter Plot: A dialog box will pop up with a list of suggested chart types. Since your data is perfect for correlation analysis, Excel will likely suggest an "XY (Scatter)" chart as one of the top options. Click on it to see a preview.

  4. Click OK: If you like what you see, hit "OK," and Excel will create the chart for you, often with some basic formatting already applied.

Using the "Analyze Data" Feature

For an even more powerful AI-driven experience, you can use the "Analyze Data" feature. This tool doesn't just suggest charts, it actively looks for trends, outliers, and patterns in your data and presents them to you in plain English.

  1. Select your data: Highlight your data set.

  2. Open "Analyze Data": Go to the "Home" tab. All the way on the right side of the ribbon, you'll find a button labeled "Analyze Data." Click it.

  3. Review AI-Generated Insights: A new "Analyze Data" pane will slide out from the right side of your screen. It will be full of suggested charts and observations Excel found. Scroll through these suggestions. Excel will often create a scatter plot automatically and describe the relationship it found, such as, "Monthly Sales and Monthly Ad Spend shows a correlation of 0.98."

  4. Add Chart to Sheet: When you see the X Y graph, simply click the "+ Insert Chart" button below it, and Excel will add the fully formatted chart to your worksheet.

You can also use the text box at the top of the "Analyze Data" pane to ask questions in natural language. Try typing "scatter plot of sales by ad spend," and Excel's AI will generate it for you on the fly. It's like having a helpful data analyst right inside your spreadsheet.

Step 4: Add a Trendline to See the Pattern

Once you have your scatter plot, the next step is to interpret it. The simplest way to see the overall direction of the relationship is to add a trendline.

A trendline is a straight line that slices through your data points to show the general tendency. Does the line point up, down, or is it flat?

Adding one is simple:

  1. Right-click any data point on your chart (one of the dots).

  2. Select "Add Trendline..." from the dropdown menu.

  3. In the "Format Trendline" pane that appears on the right, you can leave the "Linear" option selected, as that's what you'll typically want.

Your chart will now have a straight line running through the data. In our sales vs. ad spend example, the line will likely point upwards to the right, showing a clear positive correlation. This visually confirms that as ad spend increases, sales tend to increase as well.

Final Thoughts

Whether you create it manually or use helpful AI tools like "Analyze Data," making an X Y graph in Excel is a straightforward way to discover the relationships hidden in your business data. These charts turn columns of numbers into clear, simple stories that help you make smarter decisions about your sales, marketing, and operational strategies.

Of course, this process works great when your data is already in a spreadsheet. But if you’re like most businesses, your data is probably scattered across a dozen different tools - your ad spend is in Google Ads, sales are in Shopify, and customer info lives in HubSpot. We built Graphed to eliminate the endless cycle of exporting CSVs and manually VLOOKUP-ing everything together. Graphed connects to all your sources, lets you build real-time dashboards using just plain English, and keeps all your reports constantly updated so you can focus on insights instead of data prep.