How to Overlay Graphs in Excel

Sometimes, a single chart just doesn't tell the whole story. You’re left staring at two separate graphs, trying to mentally connect the dots between your soaring website traffic and your sales numbers. Overlaying graphs in Excel, often by creating what's known as a "combination chart," solves this problem by visualizing two different types of data in one place. This guide will walk you through exactly how to set up, create, and customize a professional combination chart to find deeper insights into your data.

Why Combine Charts in Excel?

Combining charts is all about revealing relationships and providing context. When you layer a line graph on top of a column chart, for instance, you can suddenly see how two different metrics influence each other over time. The "why" becomes instantly visible without having to flip back and forth between reports.

Here are a few practical scenarios where this is incredibly useful:

• Sales & Marketing Analysis: Plot your monthly ad spend as a line graph over a column chart of your revenue. Did that budget increase in May actually lead to a bump in sales?
• Performance vs. Goals: Show actual monthly sales as columns with a flat horizontal line graph representing the sales target. You can immediately see which months exceeded the goal and which fell short.
• Website Analytics: Compare website sessions (columns) with the conversion rate (line graph). This helps you see if high-traffic days also lead to higher-quality engagement.
• Inventory & Sales Volume: Track units sold (columns) against the inventory level (line) to identify potential stockout issues or periods of excess inventory.

In short, overlaying graphs turns disparate data points into a cohesive narrative, making it easier for you and your team to understand performance at a glance.

Step 1: Preparing Your Data for a Combo Chart

Before you get into building the chart, the single most important step is setting up your data correctly. A clean, properly organized table is the foundation of a good chart. Messy data will give you a messy, unreadable result.

Here’s the golden rule: organize your data in columns with clear headers. Your first column should typically contain the labels for your horizontal axis (the X-axis), such as dates, months, or campaign names. The subsequent columns should contain the numeric data you want to plot.

Let's use a common sales and revenue example. Your data should look something like this:

Month | Units Sold | Revenue ($)

• Jan | 450 | 54,000
• Feb | 370 | 44,400
• Mar | 520 | 62,400
• Apr | 610 | 73,200
• May | 750 | 90,000
• Jun | 680 | 81,600

Notice how everything is lined up. Months are in the first column, and the two different data series we want to compare - "Units Sold" and "Revenue ($)" - are in the next two columns. This simple structure is exactly what Excel needs.

Step 2: Creating Your First Combination Chart

With your data prepped, it's time to create the chart itself. We'll build a chart that shows "Units Sold" as columns and "Revenue" as a line to see how they trend together.

1. Select Your Data Range

Click and drag your mouse to highlight the entire table, including the headers. In our example, you would select the cells from "Month" down to the last revenue number.

2. Insert a Combo Chart

Navigate to the Insert tab on Excel's top ribbon. In the "Charts" section, find and click the icon that looks like a small column and line chart. It's labeled Insert Combo Chart.

A dropdown menu will appear. You can pick one of the pre-made combo charts, but for more control, select Create Custom Combo Chart... at the bottom. This opens up the "Insert Chart" dialog, which is where the magic happens.

3. Configure the Chart Types and Secondary Axis

This dialog box is the control center for your combination chart. You'll see each of your data series ("Units Sold" and "Revenue") listed, with options to assign a chart type and axis to each.

Here's the problem you need to solve: "Units Sold" values are in the hundreds, while "Revenue" is in the tens of thousands. If you plot them on the same axis, the "Units Sold" columns will be tiny, barely visible bars at the bottom of the chart. The scales are just too different.

This is where the Secondary Axis comes in. It adds a second vertical axis (Y-axis) on the right side of the chart with its own scale.

Follow these steps in the dialog box:

• For the "Units Sold" series: Choose Clustered Column as the Chart Type. Leave the "Secondary Axis" box unchecked. The scale for this series will be on the left.
• For the "Revenue ($)" series: Choose Line as the Chart Type. And here's the key step: check the Secondary Axis box next to it.

Instantly, the preview chart will update. You'll see your columns showing the units sold (measured by the left axis) and an overlaid line showing the revenue trend (measured by the new right axis). Everything is now clearly visible and properly scaled.

Click OK to create the chart.

Step 3: Making Your Chart Clear and Professional

You’ve made the chart, but it's not finished yet. A default chart lacks context. Now is the time to add titles and labels to make sure anyone who sees it understands exactly what they’re looking at.

Add Chart Titles and Axis Labels

This is the most critical part of customizing your chart. Without labels, your audience is left guessing what the numbers mean.

1. Click on your newly created chart to select it. Two new tabs will appear on the ribbon: Chart Design and Format.
2. Go to the Chart Design tab. On the far left, click Add Chart Element.
3. From here, you’ll add the following:

Now, both axes are clearly defined, and the chart's purpose is unmistakable. You have officially created a useful, easy-to-read business visual.

Adjusting Colors and Styles

First impressions matter, even with charts. You can quickly improve the look of your combo chart from the Chart Design tab.

• The Chart Styles gallery gives you a variety of pre-designed themes. Hover over them to see a live preview.
• The Change Colors button lets you select different color palettes to match your brand presentation or simply make the chart more visually appealing.

For more detailed control, you can right-click on any element of the chart (like the columns or the line) and select Format Data Series... to open a side panel where you can change fill colors, line thickness, add markers to the line graph, and much more.

Step 4: Advanced Tips & Common Scenarios

Once you've mastered the basics, you can apply this technique to other common business needs.

Plotting a Target or Goal Line

A frequent request is to show performance against a goal. Let's say you have a fixed monthly sales target of $75,000. Here’s how you add that to your chart:

1. Go back to your data source. Add a new column called "Revenue Target" and fill every row in that column with 75000.
2. Right-click on your existing chart and choose Select Data.
3. In the "Legend Entries (Series)" box, click Add. For the "Series name," select the "Revenue Target" header. For the "Series values," select all the 75000 values you just entered. Click OK.
4. Now, right-click the chart again and go to Change Chart Type.
5. Find your new "Revenue Target" series in the list. Change its Chart Type to Line and make sure it’s assigned to the Secondary Axis (the same one as your Revenue series).

You'll now have a perfectly flat line running across your chart, instantly showing which months hit the target and which didn't.

Troubleshooting Common Issues

• My line graph is flat along the bottom! This is almost always a secondary axis issue. Your line series data scale is much larger than your column series data. Go back to Change Chart Type, find the flat line data series, and check the Secondary Axis box for it.
• My dates on the horizontal axis are just sequential numbers (e.g., 44197, 44228). Excel sometimes defaults to its general number format for dates. To fix this, right-click the horizontal axis, select Format Axis, and in the "Number" section, change the category to Date and pick your preferred format.

Final Thoughts

That's it. Creating an overlay or combination chart in Excel is a surprisingly straightforward process that turns a simple spreadsheet into a much clearer story. By combining columns and lines on primary and secondary axes, you can quickly unlock and communicate the relationships hiding between your different datasets.

While mastering charts in Excel is a valuable skill, the process heavily relies on manually preparing data and clicking through menus. When you factor in pulling data from multiple sources like Google Analytics, your ad platforms, and your CRM, this can quickly become a weekly time-sink. At Graphed, we created a faster way by making data analysis conversational. We integrate directly with your key platforms, so instead of building reports click-by-click, you can just ask for "a combo chart showing my Facebook Ads spend versus Shopify sales for last quarter," and get a live, automated dashboard in seconds.