How to Create a Combination Chart in Excel

A combination chart does exactly what its name suggests: it combines two different types of charts, like a column chart and a line chart, into a single visualization. This article will show you how to create, customize, and effectively use combination charts in Microsoft Excel to tell a more powerful story with your data.

What Exactly is a Combination Chart?

Imagine you want to see your monthly sales revenue and your profit margin on the same graph. Revenue might be in thousands of dollars, while your profit margin is a percentage. If you plot both on a standard column chart, the small percentage values would barely be visible next to the tall revenue bars, and you'd lose the entire story.

A combination chart solves this problem by allowing you to represent different data series with different chart types and, most importantly, plot them on different scales. You can show revenue as columns measured against a primary vertical axis (on the left) and your profit margin as a line measured against a secondary vertical axis (on the right).

This approach is perfect for visualizing relationships between two distinct metrics, such as:

• Tracking website traffic (columns) against the conversion rate (line).
• Comparing units sold (columns) with customer satisfaction scores (line).
• Showing ad spend (columns) versus return on ad spend, or ROAS (line).

By blending two chart types, you can reveal connections and correlations that would be impossible to spot otherwise.

Step 1: Preparing Your Data for a Combo Chart

Before you create any chart in Excel, your data needs to be organized logically. For a combination chart, a simple tabular format is best. Your first column should contain your categories or time periods (like months, quarters, or campaign names). The subsequent columns should contain the numerical data you want to plot.

Let’s use an example of monthly sales revenue and website traffic. Your table in Excel should look something like this:

Example Data Structure:

Make sure your headers are clear and that your numbers are formatted correctly (e.g., as currency or general numbers). Clean, organized data is the foundation of a great chart.

Step 2: How to Create the Combination Chart

Once your data is ready, creating the basic chart takes just a few clicks. Follow these steps:

1. Select Your Data

Click and drag to highlight the entire data range you want to include in the chart, including the headers. In our example, you would select cells A1 through C5.

2. Insert the Chart

Navigate to the Insert tab on the Excel ribbon. In the Charts group, click on the small icon that looks like a column chart and a line chart superimposed on one another. This is the "Insert Combo Chart" button.

3. Choose Your Chart Type

A dropdown menu will appear with a few options:

• Clustered Column - Line: This is a basic combo chart where both series share the same primary axis. It's useful if your data scales are similar.
• Clustered Column - Line on Secondary Axis: This is the most common and useful type. It plots one data series (e.g., Sales Revenue) as columns against the primary axis on the left, and the other series (e.g., Website Sessions) as a line against a secondary axis on the right.
• Stacked Area - Clustered Column: A more specialized type that combines a stacked area chart with columns.

For our example, choose Clustered Column - Line on Secondary Axis. Excel will instantly generate a combination chart on your worksheet.

You’ll now see a chart where monthly Sales Revenue is represented by blue bars scaled on the left axis, and Website Sessions are shown as an orange line, measured by the axis on the right.

Step 3: Customizing and Refining Your Chart

The default chart is a great start, but the real power comes from customization. A well-formatted chart is easier to read and looks more professional. Here’s how to fine-tune your visualization.

First, click on your chart to bring up the Chart Design and Format tabs on the Excel ribbon. You can do most of your edits from here.

Manually Changing Chart Types and Axes

If you didn't pick the right combo chart type initially or want to adjust it, you can easily change things manually.

1. Right-click on your chart and select "Change Chart Type..."
2. In the All Charts tab, select "Combo" at the bottom of the list.
3. Here, you'll see a customization screen for your chart. For each data series (e.g., "Sales Revenue ($)" and "Website Sessions"), you can choose a specific chart type from a dropdown menu.
4. More importantly, you can assign each series to a specific axis by checking or unchecking the "Secondary Axis" box next to it.

This screen gives you complete control. You could change your columns to a stacked column chart or your line to an area chart while managing which axis each one maps to.

Adding and Formatting Chart Elements

Clear labels are essential for a good combo chart, especially with two different axes.

1. Chart Title

The default title is usually just "Chart Title." Click on it and type something more descriptive, like "Monthly Sales Revenue vs. Website Sessions." A good title should tell the viewer exactly what they are looking at.

2. Axis Titles

This is arguably the most important step for a combo chart with a secondary axis. Without labeled axes, your viewer will have no idea what the numbers mean.

• Click on the chart to select it.
• Click the green "+" icon (Chart Elements) that appears on the right of the chart.
• Check the box for "Axis Titles."
• New text boxes will appear for the horizontal, primary vertical, and secondary vertical axes.
• Click on each one to edit the text. For our example:

3. The Legend

The legend helps distinguish between the data series. Excel adds one by default, but you can move it for better readability. Using the same "+" menu, hover over "Legend" and choose to place it at the top, bottom, left, or right.

4. Data Labels

Sometimes you want to see the exact values for each bar or line point without having to trace it back to an axis. To add them:

• Right-click on the data series you want to label (e.g., click on one of the bars).
• Select "Add Data Labels" from the context menu.
• You can then format these labels by right-clicking them and choosing "Format Data Labels..." to change their position, font, or number format.

Improving Visual Appeal

Finally, you can adjust colors and styles to match your brand or simply make the chart easier on the eyes.

• Changing Colors: Right-click on a data series (the bars or the line) and select "Format Data Series." A panel will open on the right. Under the "Fill & Line" tab (the paint bucket icon), you can change the fill color of the bars or the color, thickness, and style of the line.
• Removing Clutter: Sometimes less is more. Consider removing chart gridlines for a cleaner look. Click on the gridlines to select them and press the Delete key.

Effective Use Cases for Combination Charts

To give you more ideas, here are a few practical scenarios where a combo chart is the best tool for the job:

Marketing Analytics

• Performance vs. Spend: Visualize your monthly ad spend (columns) versus your conversion rate (line). This can help you identify if increased spending is leading to diminishing returns on conversion efficiency.
• Volume vs. Quality: Track the number of leads generated (columns) against the lead-to-customer conversion rate (line). This helps show whether your lead generation efforts are attracting the right kind of audience.

Sales Performance

• Sales Volume vs. Average Deal Size: Plot total sales deals closed per month (columns) and the average value of those deals (line). This can reveal trends where your sales team is closing lots of small deals, but the overall revenue growth isn't accelerating.
• Revenue Target vs. Actuals: Show your sales target for each month (line) and the actual revenue achieved (columns). This "combo" is classic for quickly seeing performance against goals.

Financial Reporting

• Revenue vs. Gross Margin: Use columns to represent quarterly revenue and a line to show the gross margin percentage. This helps stakeholders quickly see if rising revenues are also coming with healthy profitability.
• Inventory Levels vs. Holding Costs: Track physical inventory counts (columns) alongside the associated storage and maintenance costs (line) to optimize inventory management.

Final Thoughts

A combination chart is an excellent tool in your Excel toolkit for visualizing two different data types on a single, compelling graph. By pairing a column chart with a line chart and using a secondary axis, you can uncover relationships, spot trends, and communicate complex data stories clearly and concisely.

Let's be honest, though: manually building these reports in Excel every week or month can become a chore, especially when you're pulling data from different places. We created Graphed because we believe getting these kinds of insights shouldn't require so much clicking and formatting. Instead of wrestling with data sets and chart customizers, you can simply connect your data sources and describe what you need in plain English - like, "Compare my total ad spend as bars and my customer acquisition cost as a line for the last quarter." Graphed instantly builds the live dashboard for you, saving you time so you can focus on the insights, not the setup.