How to Make a Bubble Chart in Excel with ChatGPT

Making a bubble chart in Excel used to involve a lot of clicking through confusing menus, but it's a fantastic way to represent three dimensions of data on a single two-dimensional chart. Using ChatGPT, you can skip the manual setup entirely and create these useful visualizations with a simple text prompt. This article will walk you through what bubble charts are, how to structure your data for one, and the exact steps to generate and run the code to build them automatically.

Graphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Build Your First Agent

What Exactly Is a Bubble Chart?

Think of a bubble chart as a scatter plot with a bonus feature. While a standard scatter plot uses dots to show the relationship between two different variables along an X and Y-axis, a bubble chart adds a third variable represented by the size of the "bubble" or dot. This allows you to visualize the relationship between three pieces of data all at once.

The goal is to spot patterns and connections that wouldn't be obvious if you looked at the numbers in a table. It's especially useful for identifying high-impact items or outliers that deserve more attention.

When Would You Use a Bubble Chart?

Bubble charts are perfect for situations where you want to compare items based on three quantitative measures. A few real-world examples include:

• Marketing Campaign Analysis: Compare multiple campaigns by plotting Ad Spend (X-axis) against Conversion Rate (Y-axis), with the total Number of Sales (Bubble Size). This instantly shows you which low-cost campaigns are efficient performers and which high-spend campaigns might need a second look.
• Sales Performance Review: Plot the Number of Client Calls made by each sales rep (X-axis) against their Close Rate (Y-axis), with the Total Revenue of their closed deals representing the bubble size. You will quickly see who is closing large deals, who is closing many small deals, and who is busy without results.
• Project Management: Evaluate different projects by showing the Estimated Time to Complete (X-axis) versus the Estimated Cost (Y-axis), with the Potential ROI determining the bubble size. A big bubble in the lower-left corner (low time, low cost) is probably a project worth prioritizing.
• E-commerce Product Analysis: You could plot the Number of Units Sold (X-axis) versus the Average Customer Rating (Y-axis), with the Profit Margin per unit being the size of the bubble. This helps you identify popular but low-margin products or hidden gems that customers love.

Step 1: Get Your Data Organized for Excel

Before you turn to ChatGPT, your data in Excel needs to be organized in a way that the bubble chart can understand. A clean, simple table structure is all you need. You'll need three columns for your core data, plus an optional first column for labels.

AI Agents for Marketing

Crash Course

Graphed

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Download the PDF

How to Structure Your Table

Make sure your data is in adjacent columns with a clear header for each. Excel will create one series of bubbles based on this structure.

• Column A (Optional, but Recommended): Labels. These are the names of the items you're comparing (e.g., Campaign Name, Sales Rep, Product SKU).
• Column B: X-Axis Values. The first numerical value (e.g., Ad Spend, Calls Made).
• Column C: Y-Axis Values. The second numerical value (e.g., Conversions, Deals Closed).
• Column D: Bubble Size. The third numerical value that determines the size of each bubble (e.g., Revenue, ROI).

Your finished table should look something like this example of paid advertising campaigns:

Sample Data Format:

With your data prepped, you're ready for the main event.

Step 2: Use ChatGPT to Generate the Excel VBA Code

This is where the magic happens. We're going to ask ChatGPT to write a small script for us using VBA (Visual Basic for Applications). VBA is Excel's built-in coding language that lets you automate just about anything, including creating charts. It sounds technical, but you don't need to know how to write it - you just need to know how to ask for it.

Crafting a Good Prompt for ChatGPT

To get the best possible code, your prompt should be specific and clear. The more context you provide, the less likely the code is to have errors. Always include the sheet name, data range, column definitions, and a chart title.

Example Prompt Template:

Write an Excel VBA macro that creates a bubble chart on the active worksheet. My data is located on a sheet named 'Sheet1' in the range A1:D5. Column B should be used for the X-axis (Ad Spend), Column C for the Y-axis (Clicks), and Column D for the bubble size (Conversions). The chart title should be 'Campaign Performance'. Please include axis titles as well.

Be sure to change the sheet name, range, labels, and title to match your specific spreadsheet.

Graphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Build Your First Agent

Example ChatGPT Output (The VBA Code)

Below is an example of what ChatGPT might generate based on that prompt. Your code will look very similar.

Sub CreateBubbleChart()
    'Declare variables
    Dim ws As Worksheet
    Dim chtObj As ChartObject
    Dim cht As Chart
    Dim dataRange As Range

    'Set the worksheet where your data is located
    Set ws = ThisWorkbook.Sheets("Sheet1")

    'Set the data range including headers
    Set dataRange = ws.Range("A1:D5")

    '== Create the Chart ==
    
    'Add a new chart object to the worksheet
    Set chtObj = ws.ChartObjects.Add(Left:=150, Width:=480, Top:=75, Height:=300)
    Set cht = chtObj.Chart
    
    'Set the chart type to a Bubble Chart
    cht.ChartType = xlBubble
    
    'Set the data source for the chart
    cht.SetSourceData Source:=ws.Range("B1:D5")

    '== Format the Chart ==
    
    'Set Chart Title
    cht.HasTitle = True
    cht.ChartTitle.Text = "Campaign Performance"
    
    'Set a default legend (optional)
    cht.HasLegend = False

    'Set X-Axis Title
    cht.Axes(xlCategory).HasTitle = True
    cht.Axes(xlCategory).AxisTitle.Text = "Ad Spend ($)"

    'Set Y-Axis Title
    cht.Axes(xlValue).HasTitle = True
    cht.Axes(xlValue).AxisTitle.Text = "Clicks"
    
End Sub

Even if you don't understand what this code is doing, that’s perfectly fine! The key is that ChatGPT has done the heavy lifting for you.

AI Agents for Marketing

Crash Course

Graphed

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Download the PDF

Step 3: Pop the Code into Excel and Run It

Now that you have your script, it's time to put it into Excel. This process only takes a minute and requires no advanced skills. Just follow these simple steps:

1. Open the VBA Editor: From your Excel spreadsheet, press Alt + F11 (or Fn + Alt + F11 on many laptops). This action opens the Visual Basic for Applications editor window.
2. Insert a Module: In the VBA editor's menu, go to Insert and select Module. A blank white code window will appear. This is where your code lives.
3. Paste Your Code: Copy the entire VBA code block provided by ChatGPT and paste it into the empty module window.
4. Run the Macro: With your cursor anywhere inside the code you just pasted, press the F5 key or click the green "Play" triangle icon in the toolbar.

That's it! If you switch back to your Excel sheet (you can close the VBA editor), you should see a newly created bubble chart with your data, placed right next to your table.

Step 4: Customizing Your New Chart

The code gives you a solid, functional chart, but you’ll probably want to tweak its appearance to make it more professional and easier to read. Excel's standard chart formatting tools work perfectly here.

• Add Helpful Data Labels: A bubble chart is nearly useless without labels. Click the "+" icon on the right side of the chart and check the "Data Labels" box. Click the small arrow next to it, go to "More Options," and choose to display the "Bubble Size" or "Series Name." This tells viewers what each bubble actually represents.
• Adjust Colors and Styles: Go to the Chart Design tab. Here you can pick from dozens of pre-made color palettes and visual styles that can make your chart look much sleeker with a single click.
• Refine Your Axes: If your bubbles are all clustered in one area, you may need to adjust the axis scale. Double-click the X or Y-axis to open the "Format Axis" pane. Here, you can set the minimum and maximum bounds to "zoom in" on your data.
• Scale Bubbles for Readability: Sometimes bubbles can be too small or so large they overlap. In the chart, right-click a bubble, select 'Format Data Series...'. An option should appear to 'Scale bubble size to %' - try adjusting this to 75% or 150% to see what works best for your data.

Dealing with Common Roadblocks

If the script doesn't work perfectly on the first try, don't worry. Here are a few common issues and easy fixes:

• The Macro doesn't Run/Shows an Error: The most common problem is a mismatch between your prompt and your spreadsheet. Double-check that the sheet name (Sheet1) and data range (A1:D5) in the code exactly match your Excel file.
• You Can't Seem to Open the VBA Editor: Office installations sometimes disable developer tools by default. To enable them, go to File > Options > Customize Ribbon and check the "Developer" box. Then, the Alt + F11 shortcut will work.
• The chart is Blank: This typically happens if the SetSourceData range in the VBA code is wrong or only includes the headers. Ensure the range covers all your numerical data.

Final Thoughts

Manually creating charts in Excel can be a slow, frustrating exercise in clicking through endless formatting panes. By enlisting ChatGPT to generate the VBA code, you turn a chore into a simple copy-and-paste task, letting you get straight to the insights your data holds.

While using an AI assistant for Excel VBA is a huge leap forward, it still requires shuffling code back and forth and setting up spreadsheets manually. We built Graphed to create a more seamless experience. You can connect datasets like Google Analytics, Shopify, or even a Google Sheet, and just ask, "Show me a bubble chart of ad spend versus clicks, with conversions as the bubble size." We build the live, interactive visualization for you instantly - no code, no clicks, no hassle.