How to Make a Scatter Plot in Excel with ChatGPT
Creating a scatter plot in Excel is the perfect way to see if there's a relationship between two different things you're tracking, like how your ad spend affects your revenue or how website traffic influences sign-ups. While Excel has built-in tools for this, you can now use ChatGPT to skip the steps and generate exactly the chart you need, complete with formatting and analysis features like trendlines. This guide will walk you through exactly how to do it.
We'll cover how to prepare your data, craft the right prompt for ChatGPT, and use its output to create a professional-looking scatter plot without clicking through endless Excel menus. You’ll learn a new and surprisingly simple way to automate your chart creation.
What Exactly is a Scatter Plot, Anyway?
A scatter plot is a type of chart that uses dots to represent the values of two different numeric variables. The position of each dot on the horizontal (X-axis) and vertical (Y-axis) axes indicates values for an individual data point. Its main job is to show the correlation - or lack thereof - between two sets of data.
Think of it like this:
Your X-axis could be your monthly Facebook Ads spend.
Your Y-axis could be your monthly sales revenue.
Each dot on the plot would represent a single month, showing how much you spent versus how much you earned. When you plot all the months, you might see a clear pattern:
Positive Correlation: The dots form a line that goes up and to the right. This means as your ad spend increases, your revenue tends to increase as well.
Negative Correlation: The dots form a line that goes down and to the right. This would suggest that as one value increases, the other decreases (e.g., higher product returns as customer support wait times increase).
No Correlation: The dots are scattered randomly all over the chart. There’s no clear relationship between the two variables.
It’s an essential tool for marketers, founders, and analysts who need to move beyond simple totals and understand the relationships driving their business performance.
The Traditional Way: Making a Scatter Plot in Excel Manually
Before using ChatGPT, it's helpful to understand the manual process. It isn't overly complicated, but it does involve a fair amount of clicking and formatting, especially if you want your chart to look just right. Manually creating a scatter plot produces a static chart - when you get new data, you have to adjust your data ranges or rebuild it all over again.
The typical steps are:
Organize your data: First, format your data with one variable in Column A (your X-axis) and the other in B (your Y-axis).
Select your data: Click and drag to highlight the cells containing your data.
Insert the chart: Navigate to the Insert tab, find the Charts group, and click on the dot-style icon for Insert Scatter (X, Y) or Bubble Chart.
Choose the subtype: Pick the basic scatter plot from the dropdown menu.
Customize and format: Add a chart title, label your X and Y axes, adjust colors, and maybe add a trendline to better visualize the relationship.
This method works, but when you have to do it repeatedly for weekly or monthly reports, the time adds up. It also requires you to remember exactly which menu options to click. This is where a little automation can be a big help.
Using ChatGPT to Write Excel VBA Code
This is where things get interesting. Instead of clicking through menus, you can tell ChatGPT to write a small script for Excel that builds the chart for you. This script is written in a language called Visual Basic for Applications (VBA) - Excel’s own automation language.
Don’t let the term "VBA" intimidate you. You don’t need to learn how to code. You just need to know how to ask ChatGPT for the code and where to paste it. For Excel users, this turns a repetitive, multi-step process into a simple copy-and-paste instruction.
Step 1: Get Your Data Ready
First, organize your data in a clean spreadsheet table. Your independent variable (the one you control or change) should be in the left column, and your dependent variable (the one that responds) should be in the right column. For example, use a sample dataset tracking marketing spend vs. new customers acquired over 12 months.
Make sure your spreadsheet is set up neatly, something like this on Sheet1 in your file:
Step 2: Craft Your Prompt for ChatGPT
How you ask ChatGPT for the code will determine the quality of the result. The key is to be specific. Tell it everything it needs to know about your data's location and how you want the chart to look.
A strong prompt should include:
The type of chart you want (a scatter plot).
The name of the worksheet containing your data (e.g.,
Sheet1).The exact cell ranges for your X-axis and Y-axis data.
Instructions for formatting, like chart titles and axis labels.
Bonus instructions, like adding a trendline or placing the chart on a new sheet.
Example Prompts
Here are a few prompts you can adapt, from basic to advanced:
Basic Prompt:
Detailed Prompt (Recommended):
This more detailed prompt gives ChatGPT everything it needs to create a polished, presentation-ready chart right from the start.
Step 3: Access the VBA Editor in Excel
Now, you need a place to paste the code ChatGPT gives you. Every version of Excel has a built-in code editor for VBA.
On Windows, press Alt + F11.
On a Mac, press Fn + Option + F11.
This will open the Visual Basic for Applications window. In this window, go to the menu bar at the top and click Insert > Module. A blank white window will appear on the right side. This is where your code will go.
Step 4: Generate, Copy, and Paste the Code
Go back to ChatGPT and give it your detailed prompt. It will generate a block of VBA code. It will look something like this:
IMPORTANT NOTE
A recent ChatGPT update may add unusual, extra, erroneous characters when copying code. Always double-check anything you are running. It should function properly despite the visual quirks.
Click the "Copy code" button that ChatGPT provides and paste it directly into the blank module you opened in Excel's VBA editor.
Step 5: Run the Macro and Admire Your Chart
With the code pasted, all you need to do is run it. Make sure your cursor is somewhere inside the code text, then press the F5 key (or Fn + F5 on some laptops). Alternatively, you can click the green "Run" button in front of the subroutine to execute your script.
Switch back to your main Excel window. Voilà! A perfectly formatted scatter plot should appear on your worksheet, built automatically according to your precise instructions.
Fine-Tuning: Chatting with Your Code
The biggest benefit of using ChatGPT is its ability to remember past conversations, allowing you to iterate on the code. If the first chart isn't quite right, you can ask ChatGPT to refine the code. You might say something like, "Change the plot color to yellow," and ChatGPT will provide updated code to suit your needs.
Final Thoughts
ChatGPT transforms the process of creating scatter plots in Excel into a seamless task. It makes automation accessible, even for those without prior coding experience. Knowing how to create and fine-tune your charts through ChatGPT is a great skill to have, enhancing the way you visualize data. Automation through VBA code saves time, allowing you to focus on insights that drive results.