How to Overlay a Line on a Bar Chart in Excel

Overlaying a line on a bar chart in Excel is an effective way to compare two different measures on a single, easy-to-read visual. This type of chart, often called a "combo chart," allows you to tell a more nuanced story with your data. This tutorial will walk you through why and how to create them, from preparing your data to customizing the final product.

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

Why Combine a Bar Chart and a Line Graph?

You might be wondering why you would go to the trouble of combining two chart types. Why not just create two separate charts? The short answer is: context. A combo chart is perfect when you want to show the relationship between two different data series that have vastly different scales or units of measurement.

When you try to plot numbers with different scales - like raw sales figures in the tens of thousands and a conversion rate as a small percentage - on the same axis, the smaller value will look like a flat line squished at the bottom. The combo chart solves this by introducing a secondary axis. This gives each data series its own scale, allowing both to be visualized clearly and effectively in one place.

Here are a few common scenarios where a combo chart is the perfect tool:

• Sales Revenue vs. Profit Margin: You can display monthly sales revenue as bold bars and the corresponding profit margin percentage as a line floating above. This quickly reveals if high-revenue months also had high profitability.
• Website Traffic vs. Goal Conversion Rate: Show your total website sessions per month as bars and overlay the goal conversion rate as a line. This helps you see if spikes in traffic actually lead to more conversions.
• Individual Employee Performance Against a Target: Let's say you're tracking units sold per employee. You can represent each employee's sales with a bar and use a single straight line to visualize the company-wide sales target.
• Production Volume vs. Defect Rate: Track the total number of units produced (bars) and see how the production defect rate (line) changes alongside it.

In all these cases, the combination of bars and a line makes relationships and outliers instantly pop.

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

Preparing Your Data for a Combo Chart

Before you can even think about creating a chart in Excel, your data needs to be structured properly. Clean, organized data is the foundation of any meaningful visualization. Luckily, the setup for a combo chart is simple.

You need at least three columns:

1. One column for your categories (like months, years, employee names, or product types). This will become your horizontal (X-axis).
2. One column for the data you want to display as bars (like "Total Sales").
3. One column for the data you want to display as a line (like "Profit Margin %").

Let's use a common marketing and sales example: You're tracking monthly ad spend against the number of new leads generated. Your table in Excel should look something like this:

+---+----------+-------------+------------+
|   |    A     |      B      |     C      |
+---+----------+-------------+------------+
| 1 | Month    | Ad Spend    | New Leads  |
| 2 | January  | $12,000     | 45         |
| 3 | February | $10,500     | 52         |
| 4 | March    | $15,000     | 71         |
| 5 | April    | $14,200     | 67         |
| 6 | May      | $16,800     | 84         |
| 7 | June     | $18,000     | 102        |
+---+----------+-------------+------------+

Notice a few important details:

• Each data point is in its own row.
• Every column has a clear header. Excel will use these headers to automatically create your chart legend and titles.
• The numbers in the "Ad Spend" and "New Leads" columns are formatted as numbers (or currency).

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

Configuring the Chart & Secondary Axis

This is where it all comes together. Excel will show you the "Combo Chart" dialog box, where you can assign specific chart types and axes to each data series.

• Select a Chart Type: By default, both your data series will likely be set to a clustered column.
• For "New Leads," click the dropdown menu and change the chart type to a Line with Marker.
• Assign a Secondary Axis: This is the most crucial step. Look for a checkbox labeled "Secondary Axis" and click it to enable it.

Immediately, a secondary vertical axis will appear on the right side of your chart preview in the dialog box. The secondary axis will apply to the "New Leads" line, giving it its own scale, and the primary axis on the left will maintain its scale for "Ad Spend" bars.

Customizing and Formatting Your Combo Chart

This is where a good chart can transform a graph into a compelling story. Adding a chart title, labeling axes clearly, and customizing it can make it easy to read and understand. Here are a few critical customizations to make:

Chart and Axis Titles

To give your chart a title, click on "Chart Title" and add a name such as "Ad Spend vs. Leads." Make sure to also provide titles for the secondary axis and primary axis to represent what each data series illustrates. With your chart selected, choose "Add Chart Element" from the ribbon toolbar to add axis titles, and define them as follows:

• Primary Vertical Axis: "Ad Spend"
• Secondary Vertical Axis: "New Leads"
• Horizontal Axis: "Months"

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

Data Labels and Legend

Sometimes it's helpful to have data values directly over each bar rather than just axes. You can add data labels via the ribbon tools which will appear as you overlay them. This helps to quickly assess the data you're displaying and to clarify what data series correspond to which axis. Adjust the legend that appears at the bottom of the chart to include both series.

Colors and Styles

Finally, consider changing the colors of your datasets to make them visually stand out and align with your company branding. You can do this from the chart design tab, choosing a color scheme that is easy to read and engaging.

Creating a combo chart in Excel is straightforward and effective for visualizing complex data. It allows you to overlay one data series onto a secondary axis, clearly showing correlations or trends that might otherwise be missed. As you master the materials and tools Excel offers, the process of analyzing your data can become an engaging and enlightening task.

If you need a solution that simplifies social media scheduling and reporting, consider Graphed. Instead of writing reports manually and juggling various applications, Graphed helps you organize your data effectively and provide clear insights.