How to Make a Combo Chart in Excel with AI
Creating a chart that clearly compares two different types of data - like monthly revenue in dollars and profit margin as a percentage - can feel tricky in Excel. You need a way to visualize both without one set of numbers overpowering the other. That’s precisely what a combo chart is for, and with Excel's built-in AI features, you no longer have to build them through a series of confusing clicks. This guide will walk you through how to use Excel's AI to effortlessly create insightful combo charts, and we'll cover the manual process too, so you understand exactly what the AI is doing for you.
What Exactly is a Combo Chart?
A combo chart, officially known as a combination chart, is exactly what it sounds like: a single chart that combines two or more chart types. The most common combination is a column chart and a line chart. This dynamic pairing allows you to display data that is related but measured on wildly different scales.
Imagine you have this data for your E-commerce store:
Monthly Sales Revenue: $50,000, $75,000, $60,000
Site Conversion Rate: 2.1%, 2.5%, 2.3%
If you try to plot both sets of data on a standard column chart, the revenue bars would be gigantic, while the tiny conversion rate values would be practically invisible, squished against the bottom axis. It wouldn't tell a useful story.
A combo chart solves this by using a secondary axis. The revenue columns would be measured against the primary vertical axis (on the left), and the conversion rate line would be measured against a secondary vertical axis (on the right). Now you can easily see the relationship between revenue and conversion rate in the same view.
Why Use a Combo Chart? Two Key Scenarios
Combo charts aren't just for fancy presentations, they solve very specific and common data visualization problems. They are most useful when you need to show a relationship between two metrics that would otherwise be difficult to compare.
1. When You're Comparing Different Units of Measurement
This is the classic use case. You want to see if one thing affects another, but they are measured in disparate ways.
Example: A marketing team wants to track monthly Ad Spend (in dollars) against the number of Sales Leads generated (a raw count).
The Problem: Ad spend might be in the thousands of dollars ($10,000), while leads are in the hundreds (300). On a single axis, the leads data would be flattened and its trends hard to see.
The Solution: A combo chart can show Ad Spend with column bars (using the left axis for dollars) and Leads generated with a line chart (using the right axis for the count). You can instantly see if a spike in ad spend led to a corresponding spike in leads.
2. When You Need to Visualize a Value and a Percentage Together
This situation is incredibly common in business reporting, where you often want to see absolute performance alongside relative efficiency.
Example: A sales manager needs to report on the Total Sales Revenue (in dollars) and the team’s Win Rate (a percentage).
The Problem: Total revenue will be a large number, while the win rate will be a small value (e.g., 25% or 0.25). A single axis chart makes this comparison impossible.
The Solution: The combo chart shows revenue as columns measured in tens of thousands of dollars on the primary axis and win rate as a line graph measured from 0% to 100% on the secondary axis. This allows the manager to answer questions like, "Did our win rate improve last quarter even if total revenue was slightly down?"
The "Old Way": Manually Creating a Combo Chart in Excel
Before diving into the AI-powered methods, it's helpful to understand the traditional way of making a combo chart. This context makes you appreciate how much easier the new tools are. It's still a reliable method, especially in older versions of Excel.
Let's use an example table showing monthly website traffic (Sessions) and the Bounce Rate %.
Here’s our data:
Month | Sessions | Bounce Rate
Jan | 45,210 | 55%
Feb | 48,930 | 52%
Mar | 55,100 | 48%
Apr | 51,300 | 51%
May | 62,500 | 45%
Step-by-Step Instructions:
Select Your Data: Highlight the entire data table, including headers (A1 to C6 in our example).
Insert Chart: Navigate to the Insert tab on the Ribbon. In the Charts group, click the small icon that looks like a combination bar and line chart, labeled Insert Combo Chart.
Choose Your Chart Sub-Type: A dropdown menu will appear. The most common option is the "Clustered Column - Line" chart. You can also choose the "Clustered Column - Line on Secondary Axis," which will preemptively move one of your data series to the secondary axis. This is usually the one you want.
Customize the Chart Series: Click on your new chart, then go to the Chart Design tab. Click Change Chart Type. At the bottom of this dialog box, you'll see your data series ("Sessions" and "Bounce Rate").
For each series, you can choose its chart type from a dropdown menu. Let's keep "Sessions" as a Clustered Column.
Change "Bounce Rate" to a Line.
Here's the key step: Check the Secondary Axis box next to "Bounce Rate."
Finalize and Format: Click OK. Your combo chart is now created. You can further customize it by adding a chart title, axis titles (highly recommended for clarity!), and adjusting colors.
This method works perfectly well, but it involves several clicks and a bit of know-how. If you're not sure which series should go on the secondary axis, it can take some trial and error.
The Smarter Way: Using Excel's AI to Build Your Chart
Modern versions of Excel (part of Microsoft 365) have incredibly smart AI analytics features baked in that practically automate the chart creation process. These tools analyze your data's structure and recommend the best visualization, often landing on a combo chart without any manual configuration.
Method 1: Using "Recommended Charts"
The "Recommended Charts" feature is the simplest form of AI assistance in Excel. It acts like a data analyst looking over your shoulder and suggesting the most effective chart for your selected data.
How It Works:
Organize and Select Data: Make sure your data is in a clean tabular format, just like our example above. Select the entire range.
Click "Recommended Charts": Go to the Insert tab and, instead of picking a specific chart type, click the Recommended Charts button.
Let Excel Do the Work: A dialog box will pop up. Excel’s algorithm instantly recognizes that your data has two different scales (a large integer and a percentage). In most cases like this, one of the top recommendations will be a combo chart, already configured with an appropriate line and column type, and second axis. It literally figures it all out for you.
Select and OK: Click on the combo chart preview that Excel suggests, and click OK. Your chart is ready to go, and it probably looks better than the one you would have made manually.
This feature is a huge time-saver. It removes the guesswork and takes you straight to the most logical visualization for your data.
Method 2: Using the "Analyze Data" AI Tool
For an even more powerful, conversational approach, you can use the "Analyze Data" feature (formerly called "Ideas"). This tool not only suggests charts but also allows you to ask questions about your data in plain English.
How It Works:
Select Your Data: Click anywhere inside your data table.
Launch "Analyze Data": Navigate to the Home tab and click the Analyze Data button on the far right.
Review Initial Insights: A pane will open on the right side of the screen, automatically displaying various insights and potential charts based on its analysis of your data. Scroll through these, Excel may have already generated the exact combo chart you need. If it has, just click "+ Insert Chart".
Ask a Direct Question: This is where the real AI power comes in. At the top of the "Analyze Data" pane, there is a text box that says, "Ask a question about your data..." Here, you can type a natural language request.
Try typing: "Compare Sessions and Bounce Rate by Month as a line and column chart."
Or more simply: "Chart sessions vs bounce rate by month."
Get Your Instant Chart: Excel’s AI will interpret your request, identify the relevant data series, choose the best chart type (a combo chart), and generate it instantly within the pane. You can then click the insert button to add it to your spreadsheet. You just told Excel what you wanted, and it delivered. It's a true game-changer.
Quick Tips for an Effective Combo Chart
Building the chart is one thing, making it easy to understand is another. Keep these best practices in mind:
Label Everything Clearly: Don't make people guess. Use a clear chart title that explains the key takeaway. Critically, add titles for both the primary and secondary vertical axes so everyone knows what each scale represents (e.g., "Revenue (USD)" and "Profit Margin (%)").
Don't Overcomplicate It: A combo chart with two data series is great. Three can sometimes work. Any more than that, and it will likely become a cluttered, confusing mess. Simplicity wins.
Be Smart with Color: Use colors that are easy to distinguish. Avoid pairing two different shades of blue, for example. High-contrast colors for your columns and line will make the chart much easier to read at a glance.
Look for the Story: What insight is the chart revealing? Is your bounce rate going down as your site sessions increase? Does a higher ad spend actually correlate with a better conversion rate? The chart exists to help you find and tell that story.
Final Thoughts
Combo charts are a fantastic tool for comparing related metrics with different scales, and Excel's AI features like "Recommended Charts" and "Analyze Data" have made them incredibly easy to create. You can now get to the insights faster by simply asking your spreadsheet for the visuals you need, rather than manually building them click by click.
As powerful as this is, the process can become repetitive if your analysis involves connecting data from sources outside of Excel, like Google Analytics, Shopify, QuickBooks, or your CRM. At Graphed , we built a tool to solve exactly this problem. Instead of exporting data to a spreadsheet first, we allow you to connect all your marketing and sales data sources one time. From there, you can just ask plain English questions to build entire dashboards with real-time, interactive charts from any connected source, moving far beyond the limits of a single spreadsheet.