Graphed LogoGraphed
Graphed LogoGraphed
How It WorksAgentsIntegrationsPricing

How to Make a Combo Chart in Excel with ChatGPT

Cody Schneider••7 min read

Telling a compelling story with your data often requires showing two different things at once. For example, maybe you want to see how your monthly revenue, a big number, relates to your customer growth percentage, a small one. Trying to put both on a standard bar or line chart in Excel can be a visual mess. This article will show you how to solve that problem by creating a combo chart in Excel, and we'll use ChatGPT as our clever assistant to make the process faster and easier.

GraphedGraphed

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.

Watch Graphed demo video

What is a Combo Chart, and When Should You Use One?

A combo chart, just as it sounds, combines two different chart types (like a column chart and a line chart) into a single visual. Its superpower is its ability to display data series with vastly different scales. It does this by using a secondary vertical axis (a Y-axis) on the right side of the chart.

This is perfect for situations where you want to compare values that are measured in completely different units. Without a combo chart, one of your data series would likely look completely flat and useless.

Use a combo chart when you want to:

  • Show revenue (in dollars) against profit margin (in percentages).
  • Compare website traffic (number of sessions) with the conversion rate (percentage).
  • Visualize sales units sold (a hard number) and the average temperature for that month (degrees).
  • Track ad spend (dollars) against click-through rate (percentage).

In all these cases, you’re comparing two different stories that are related. A combo chart lets them share a stage without one overshadowing the other.

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.

First, Get Your Data Ready

Before you get ChatGPT involved or even think about a chart, your data needs to be clean and tidy. Excel is powerful, but it needs a clear, structured table to work its magic. Time spent here will save you headaches later.

Your data should be organized in simple columns. For a typical combo chart, you’ll want at least three:

  1. A Label Column: This is your horizontal axis (the X-axis). It’s usually a time period, like Month, Day, or Quarter, or it could be categories like Product Name or Campaign.
  2. First Data Series: This will be your primary data, which we’ll visualize with columns or bars. Let's call it "Units Sold."
  3. Second Data Series: This will be your secondary data, displayed as a line on its own axis. We'll use "Customer Satisfaction Score (%)" for our example.

Method 1: Using ChatGPT as Your Step-by-Step Guide in Excel

Let's say you’re comfortable in Excel, but you can’t quite remember where the combo chart option is hiding or how to get the secondary axis to work. Instead of clicking through menus endlessly, you can just ask ChatGPT to be your guide.

Step 1: Craft Your 'Excel'ucator Prompt

The key to a good answer from ChatGPT is a good question. Be specific about what you have and what you want to achieve. Include the software you're using (Excel), the data you have, and the chart you want to end up with.

Here's a great example prompt:

I have an Excel sheet with data structured in three columns: Month (A), Units Sold (B), and Customer Satisfaction Score (%) (C). I want to create a combo chart. I need "Units Sold" to be a clustered column chart and "Customer Satisfaction Score" to be a line chart on a secondary axis. Can you please give me the step-by-step instructions with the exact button clicks for the latest version of Excel?

This prompt is effective because it's crystal clear. The AI knows what you have and exactly what you want it to help you create.

GraphedGraphed

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.

Watch Graphed demo video

Step 2: Follow ChatGPT’s Instructions

ChatGPT will likely provide a nicely formatted set of instructions that you can follow like a recipe. It will probably look something like this:

ChatGPT's Response (example):

  1. Select Your Data: In your Excel sheet, click and drag to select the entire data range, including the headers. For your data, that would be cells A1 through C13.
  2. Insert Chart Menu: Go to the Insert tab on the ribbon.
  3. Open Recommended Charts: In the 'Charts' group, click on Recommended Charts.
  4. Select All Charts: In the dialog box that appears, click the All Charts tab at the top.
  5. Choose Combo: In the list of chart types on the left, select Combo at the bottom.
  6. Customize Chart Types & Axes:
  7. Create the Chart: Click OK. Your customized combo chart will appear on your spreadsheet.

Now all you have to do is follow these directions. ChatGPT cuts out all the guesswork and gets you directly to the finish line.

Method 2: Creating a Combo Chart Instantly with a VBA Script from ChatGPT

What if you want to skip all the clicking? Or maybe you have to make this same chart every week and want to automate the task. In that situation, you can ask ChatGPT to be your personal coder by getting it to write an Excel VBA script.

VBA stands for "Visual Basic for Applications," and it's the built-in programming language that lets you automate just about anything in Excel. You don’t need to know how to write VBA - that’s what ChatGPT is for.

Step 1: Prompt ChatGPT to Write the VBA 'Macro' Code

This time, our prompt will be a little different. We’re asking for code snippets. You have to be even more specific, mentioning the names, sheet names, and the exact cell ranges.

Here’s an example prompt for asking AI to generate VBA code:

Write an Excel VBA macro that creates a combo chart. In 'Sheet1', the source data is located in the range A1:C13. Column A is the category label (Months). Column B 'Units Sold' should be shown as a clustered column chart on the primary axis. Column C 'Customer Satisfaction Score (%)' should be shown as a line chart on a second axis.

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.

Step 2: Paste and Run the VBA Script in Excel

ChatGPT will generate a block of code. It may look intimidating, but you just need to know where to paste it into your Excel dashboard.

Here's what the VBA script from ChatGPT might look like:

Sub CreateCustomComboChart()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets("Sheet1")
    
    Dim chartObj As ChartObject
    Set chartObj = ws.ChartObjects.Add(Left:=100, Width:=480, Top:=50, Height:=300)
    
    With chartObj.Chart
        .SetSourceData Source:=ws.Range("A1:C13")
        .ChartType = xlColumnClustered
        
        ' Set the second series to a Line chart type
        .SeriesCollection(2).ChartType = xlLine
        
        ' Move the second series to the secondary axis
        .SeriesCollection(2).AxisGroup = 2
        
        ' Add chart titles and axis labels
        .HasTitle = True
        .ChartTitle.Text = "Monthly Sales vs. Customer Satisfaction"
        .Axes(xlCategory, xlPrimary).HasTitle = True
        .Axes(xlCategory, xlPrimary).AxisTitle.Text = "Month"
        .Axes(xlValue, xlPrimary).HasTitle = True
        .Axes(xlValue, xlPrimary).AxisTitle.Text = "Units Sold"
        .Axes(xlValue, xlSecondary).HasTitle = True
        .Axes(xlValue, xlSecondary).AxisTitle.Text = "Satisfaction Score (%)"
    End With
End Sub

Here's all you need to do to run this VBA script:

  1. Open the VBA Editor: Press Alt + F11 (or Fn + Alt + F11 on Mac) to bring up the Visual Basic for Applications (VBA) editor.
  2. Insert a New Module: Go to Insert > Module to create a new module. This opens a blank window where you can paste the script.
  3. Paste the Code: Copy the generated VBA code from ChatGPT and paste it into the module window.
  4. Run the Macro: Close the VBA editor to return to Excel. Press F5 to run the macro. Your combo chart will appear on the sheet.

And that's it! Using ChatGPT to automate Excel tasks like creating a combo chart can save you time and streamline your work.

Related Articles