How to Overlay Charts in Excel

Overlaying charts in Excel allows you to display two or more different data sets on a single graph, making it a fantastic way to spot trends, compare performance, and tell a clearer data story. Instead of forcing your audience to jump between two separate visuals, you can combine them to reveal powerful relationships. This guide will walk you through the most effective methods for layering charts, from the easy, built-in Combo Chart to a more manual approach for custom visualizations.

Why Overlay Charts in Excel?

Before jumping into the “how,” it’s useful to understand the “why.” Combining charts isn't just a design trick, it’s a strategic way to make your data more insightful. When you overlay charts correctly, you can achieve several key goals:

• Compare Data with Different Scales: The most common use case is when you need to show two metrics that are measured on wildly different scales. For instance, you might want to chart the number of units sold (e.g., 500-1000) alongside the average sale price (e.g., $25-$50). Plotting these on the same axis would make the price line look almost flat and useless. Overlaying charts with a secondary axis solves this problem perfectly.
• Highlight Relationships and Correlations: Are your marketing expenditures driving website traffic? Does an increase in customer support tickets correlate with a recent product launch? By overlaying these metrics - like ad spend as a bar chart and website sessions as a line chart - you can visualize these connections instantly.
• Show Progress Against a Goal: It's one thing to show monthly revenue in a column chart. It's far more impactful to overlay a straight line representing your monthly target. This gives viewers immediate context on performance without needing to check another number.
• Save Dashboard Space: Modern dashboards are all about conveying maximum information in a limited space. Combining related metrics into one well-designed chart cleans up your report, reduces clutter, and gives your dashboard a more professional, polished look.

The Primary Method: Creating a Combo Chart

Excel's built-in Combo Chart feature is the easiest and most recommended way to overlay different chart types. It’s designed specifically for this purpose and provides a straightforward way to configure different data series and add a secondary axis. Let’s walk through it with a practical example: tracking monthly units sold (which we want to show as columns) against the average profit per unit (which we’ll represent as a line).

Step 1: Structure Your Data Correctly

Your chart is only as good as the data powering it. For a combo chart, your data needs to be organized in clean columns with a shared label. In our case, the shared label is the month.

Here’s what our sample data looks like:

Month,Units Sold,Avg. Profit Per Unit ($) Jan,540,15.20 Feb,610,14.90 Mar,750,16.10 Apr,720,16.50 May,810,17.80 Jun,950,18.50

Make sure your headers are clear and your numeric data is formatted correctly (e.g., currency for profit).

Step 2: Insert a Basic Chart

Start by creating a simple chart that will serve as your foundation. It doesn't have to be perfect yet, we'll convert it in the next step.

1. Select your entire data range, including the headers (e.g., A1:C7).
2. Go to the Insert tab on the ribbon.
3. In the Charts group, click on the icon for Insert Column or Bar Chart and choose the first option, Clustered Column.

At this point, you’ll have a chart with two sets of columns for each month. The "Avg. Profit Per Unit" columns will likely be tiny and almost invisible because their values are so much smaller than the "Units Sold." This is exactly the problem we're about to fix.

Step 3: Change to a Combo Chart Type

Now, we’ll convert this clustered column chart into a dynamic combo chart.

1. Right-click anywhere on the chart's plot area (the main white area with the columns).
2. From the context menu, select Change Chart Type….
3. In the "Change Chart Type" dialog box, select the Combo category at the bottom of the list on the left.

Step 4: Configure Your Data Series and Secondary Axis

This is where the magic happens. The Combo Chart menu gives you full control over how each data series is presented.

1. You will see a list of your data series ("Units Sold" and "Avg. Profit Per Unit").
2. For the "Units Sold" series, keep the chart type as Clustered Column. This will be our primary data visualization.
3. For the "Avg. Profit Per Unit" series, click the dropdown next to it and change the chart type to Line. You'll see the preview on top update immediately.
4. Now for the most important step: For the "Avg. Profit Per Unit" series, check the box under the Secondary Axis column. This tells Excel to create a second vertical axis on the right side of the chart scaled specifically for this data.

The preview now shows a column chart for sales volume and a clear, meaningful line chart for the profit trend, each with its own appropriate scale. Click OK to apply your changes.

Polishing Your Combo Chart for Maximum Readability

Creating the chart is just the first half of the job. A great chart is one that requires no explanation. Here are some quick formatting tips to make your new combo chart easy to understand.

• Add Axis Titles: Your chart has two Y-axes, and a viewer won't know what they mean without labels. Select the chart, click the green “+” icon that appears on the top-right (Chart Elements), and check the box for Axis Titles. Then you can select each new title placeholder and type something descriptive like "Total Units Sold" for the left axis and "Average Unit Profit ($)" for the right.
• Refine the Legend: Ensure your legend is clear and placed well. By default, Excel usually places it at the bottom, which is often a good choice. Move it if needed to save space or improve clarity.
• Use Good Color Choices: Pick colors that have enough contrast to be easily distinguished. Avoid using colors that are too similar, like a light blue and a light green, side by side. Go for a strong pairing like a solid blue for the columns and a vibrant orange or gray for the line.
• Give Your Chart a Great Title: A good chart title tells a story. "Chart 1" is unhelpful. Something like "Monthly Unit Sales vs. Average Profit Margin" immediately tells the reader what relationship they should be looking for.

The Manual Method: Making Charts Transparent

Sometimes, the built-in Combo Chart doesn't quite meet your needs, or maybe you want to overlay two charts of the same type (like two Area Charts) in a way that combo charts don't allow. For these situations, you can use the transparency "trick." This method involves creating two separate charts and making the top one transparent so the one underneath shows through.

Step 1: Create Your First Chart

Start by creating the chart that you want to be in the background. Using your first data set, insert a chart as you normally would. For this example, let's say you've created a standard Clustered Column chart. Format it completely, including colors, titles, and labels. This will be your base layer.

Step 2: Create a Second Brand New Chart

Now, select your second data series and create an entirely separate chart. For example, you might create a Line Chart to represent a sales target. Don't worry about where it appears on the sheet for now, just get the basic chart design right.

Step 3: Make the Top Chart Transparent

This is the essential step for the manual overlay.

1. Select the Chart Area of the second chart (the one you want to overlay on top). You can click on the outer edge of the chart to select it.
2. Right-click the selected chart area and choose Format Chart Area….
3. In the formatting sidebar that appears, under the Fill section, select No fill.
4. Right underneath, under the Border section, select No line.

Your second chart's background and border will now be completely transparent. You should only see the line, its axis, gridlines, and legend.

Step 4: Align the Two Charts Perfectly

Now comes the delicate part: positioning the transparent chart directly over the base chart.

1. Carefully drag the transparent chart over the first one.
2. Resize the transparent chart by dragging its corners so that its plot area, axes, and gridlines align perfectly with the bottom chart. This requires some precision. Pro Tip: Hold down the Alt key while dragging or resizing the chart to make it snap to the cell grid. This can make alignment much easier.
3. You may also need to delete redundant elements from the top chart, such as its horizontal axis or gridlines, so it doesn't clash with the one underneath. You're simply borrowing the lines or bars from it.

Step 5 (Optional but Recommended): Group the Charts

Once you’ve perfectly aligned your two charts, you should group them into a single object. This ensures they will move and resize together, so all your hard work on alignment isn't undone.

1. Click on the first chart to select it.
2. Hold down the Ctrl key (or Cmd on Mac) and click on the second chart. Both charts should now be selected.
3. Go to the Shape Format (or Picture Format) tab that appears on the Ribbon.
4. Click the Group dropdown and select Group.

Your two separate charts now behave as one cohesive visual. This manual method offers incredible flexibility for creating custom advanced visualizations that aren't possible with Excel's standard tools.

Final Thoughts

Mastering chart overlays in Excel transforms you from someone who simply presents data to someone who tells stories with it. Whether you use the straightforward Combo Chart for common scenarios or the manual transparency method for custom visuals, the goal is the same: to show meaningful relationships in your data that might otherwise go unnoticed.

Building these sophisticated charts is rewarding but can be time-consuming, especially when your data comes from different platforms like Google Analytics, Shopify, or Salesforce ads. This is where we built Graphed to remove the friction. Instead of manually pulling data, cleaning it, and going through multi-step formatting processes, you can connect your sources and create these combined visuals in seconds using plain English. For example, simply ask, "Compare my monthly revenue from Shopify with my Facebook Ads spend in a combo chart," and our tool instantly generates a live, interactive dashboard that does just that.