How to Make an X Y Graph in Excel with ChatGPT

Tired of clicking through endless Excel menus just to visualize your data? Creating a simple XY graph to see the relationship between two business metrics - like ad spend and sales - should be fast, not frustrating. This guide shows you how to use ChatGPT to generate the exact steps or even the code you need to build and customize XY graphs in Excel in minutes, not hours.

What's an XY Graph, Anyway? (A Quick Refresher)

An XY graph, more commonly known as a scatter plot or scatter chart, is a powerful tool for visualizing the relationship between two different variables. Each dot on the graph represents a single data point, with its position determined by its value on the X-axis (the horizontal one) and the Y-axis (the vertical one).

For marketers, founders, and sales leaders, this is incredibly useful. You can quickly spot trends, patterns, and correlations that would be impossible to see in a spreadsheet of raw numbers. Common business uses include:

Marketing Analysis: Plotting marketing spend (X) against website traffic or sales revenue (Y) to see if your budget increases are actually driving results.
Sales Performance: Charting the number of sales calls made (X) against the number of deals closed (Y) to gauge a rep's effectiveness.
Product Pricing: Graphing the price of a product (X) versus the number of units sold (Y) to understand price sensitivity.
Website Analytics: Comparing average session duration (X) with conversion rate (Y) to see if more engaged users are more likely to buy.

Simply put, if you want to know "Does X affect Y?" an XY graph is usually the best place to start looking for an answer.

The Classic Way to Build an XY Graph in Excel

Before an AI assistant came along, creating a scatter plot in Excel involved a very manual, click-based process. You probably know the drill:

Organize your data into two adjacent columns (one for your X-variable, one for your Y-variable).
Highlight the data range with your mouse.
Navigate to the Insert tab on the Ribbon.
Find the Charts group and click the icon that looks like a dot-filled graph (Insert Scatter (X, Y) or Bubble Chart).
Select the first Scatter option.
Manually add chart titles, axis labels, a trendline, and customize colors by right-clicking each element.

This method works, but it's slow. If you need to make several charts or perform the same customization every week, the repetitive clicking adds up. That's where asking an AI for help can dramatically speed things up.

Step-by-Step: Making an XY Graph with ChatGPT and VBA Code

For maximum speed and repeatability, you can ask ChatGPT to write a small script (a VBA macro) that builds your chart for you with a single click. Even if you've never seen code before, don't worry - you just need to know how to copy and paste.

Step 1: Get Your Data Ready

First, make sure your data is set up cleanly in Excel. All you need is two columns: one for your independent variable (X) and one for your dependent variable (Y). Your sheet should look something like this, with clear headers in the first row.

For our example, let's say Column A contains "Ad Spend ($)" and Column B contains "Sales Revenue ($)." Our data runs from row 2 down to row 13.

Step 2: Write a Clear Prompt for ChatGPT

Now, open ChatGPT and describe exactly what you want it to build. The key to a good result is providing specific context. A great prompt includes:

The Goal: "Write an Excel VBA macro."
The Chart Type: "...to create an XY scatter plot."
The Data Location: "The data is in the active sheet. The X-axis values are in range A2:A13, and the Y-axis values are in range B2:B13."
Labels and Titles: "Label the X-axis 'Ad Spend ($)', the Y-axis 'Sales Revenue ($)', and give the chart the title 'Marketing Spend vs. Sales'."

Here’s the full prompt you can use:

"Write an Excel VBA macro to create an XY scatter plot on the active sheet. My X-axis data is in range A2:A13 and my Y-axis data is in range B2:B13. Set the chart title to 'Marketing Spend vs. Sales'. Label the X-axis 'Ad Spend ($)' and the Y-axis 'Sales Revenue ($)'."

Step 3: Get the VBA Code from ChatGPT

ChatGPT will process your request and generate a block of VBA code. It will look intimidating if you're not a coder, but you don't need to understand every line. ChatGPT does the heavy lifting for you.

The output should look something like this:

Step 4: Add and Run the Code in Excel

Now, let's put this code to work in your spreadsheet. The process is simple:

In Excel, press Alt + F11 (or Option + F11 on a Mac) to open the VBA Editor. It will open in a new window.
In the top menu of the VBA Editor, click Insert > Module. A blank white code window will appear.
Copy the entire code block from ChatGPT and paste it into this blank module window.
Close the VBA Editor window by clicking the "X". The code is now saved with your workbook.
Back in your Excel sheet, go to the Developer tab. (If you don't see it, go to File > Options > Customize Ribbon and check the "Developer" box).
Click Macros, select "CreateScatterPlot" from the list, and hit Run.

Just like that, a fully labeled XY plot appears on your worksheet!

Step 5: Customize Your Chart with Follow-Up Questions

The real power comes from iterating. Want to add a trendline to see the correlation more clearly? Don't look for the menu option. Just ask ChatGPT.

Your follow-up prompt could be:

"That's great. Now, modify the code to add a linear trendline to the chart."

ChatGPT will update the code, adding a few lines to the end of the script. You just replace the old code in your VBA module with the new version and run it again.

Want to change the dots to green? Ask: "Update the code to make the color of the scatter plot markers green."

This conversational approach lets you build a complex, perfectly styled chart without ever learning VBA syntax or hunting through formatting panes.

Prefer Instructions Over Code? ChatGPT Can Do That Too

If VBA feels like a step too far, you can use ChatGPT as an expert guide instead of a coder. Instead of asking for a macro, ask for the exact clicks needed to accomplish your goal.

Try a prompt like this:

"I'm using Excel and have data in two columns, A and B. Column A is 'Ad Spend' and Column B is 'Sales'. How do I create an XY scatter plot showing this relationship? I also need to add a trendline and label the axes. Please give me clear, step-by-step instructions for a beginner."

ChatGPT will generate a clear, numbered list detailing the entire manual process. It's like having an instruction manual tailored precisely to your question, saving you from searching through help articles or generic tutorials. This is especially helpful for more obscure tasks or custom formatting options you can't seem to find.

Quick Tips for Effective ChatGPT Prompts for Excel

To get the best results from ChatGPT, keep these principles in mind:

Be Specific With Ranges: Don't just say "my data." State the exact cell range (e.g., C2:C50) and sheet name if necessary.
State the Chart Type: Clearly say "XY scatter plot," "line chart," or "bar graph."
Include Labels and a Title: Always specify the exact text you want for your chart title and x/y axis labels in your prompt.
Provide Column Headers: Mentioning what your headers are ("Column A is Ad Spend") gives the AI context to better understand your goal.
Start Simple and Iterate: Get a basic chart working first, then use follow-up prompts to add formatting, trendlines, or other customizations.

Final Thoughts

Pairing ChatGPT with Excel can fundamentally change how you analyze and present data. It transforms a tedious, manual process into a simple conversation, whether you're generating VBA code to automate charting or just getting clear step-by-step instructions. This approach saves you valuable time and frees you up to focus on what the data actually means for your business.

Of course, the process of preparing data, prompting an AI, and managing VBA code still requires getting your hands dirty in a spreadsheet. To simplify this even further, we built Graphed. We enable you to connect your data sources (like Google Analytics, Shopify, or your ads platforms) directly, so you never have to work with CSV exports again. From there, you just describe the dashboards and charts you need in plain English, and Graphed builds them for you in real-time. It's all the power of AI analysis without any of the steps in between.