How to Overlay Graphs in Excel

Overlaying graphs in Excel can be useful when you want to show different data series on a single graph, making comparisons and correlations more evident.

For example, if you have the revenue and profit values, you can plot them in the same chart. This way, we can easily see how the two compare, if there are any patterns, or if anything strange pops up.

In this article, I’ll show you three different methods to overlay graphs in Excel.

Use Excel Combo Charts to Overlay Graphs in Excel

Sometimes we need to combine two or more charts into a single chart. This is a fairly common incident in time-based charts. Then we can compare two data sets with ease.

In the table below, column B lists the monthly sales quantity, while column C lists the monthly sales value ($). Column A shows the months.

 Data set with quantity and sales in separate columns

Earlier, I created two separate charts for the quantity and sales ($) columns of the above data table.

Chart for quantity
Line chart for sales

Now I want to overlay the above two graphs.

If I select both columns (Quantity & Sales ($)) of the data table, my graphs look like the below.

Chart with sales and quantity values

Because of the reasons listed below, the above chart is not a good one.

  • I cannot see the trend in quantities clearly.
  • Sales values are not shown as dollar ($) values.

I can use the Excel Combo Chart to overlay Excel graphs in a more meaningful way. To do that, I have to follow the below steps.

  1. Select the data table, including the column headings of the table.
Select the entire data set
  1. Go to the ‘Insert’ tab.
Click the insert tab
  1. Go to the ‘Charts’ group and click the ‘Combo’ chart icon.
Click on the combo chart icon
  1. Click the ‘Create Custom Combo Chart…’ from the expanded list.
Click on the create custom combo chart option
  1. Select the chart type for each series. In this case, I want both series to be line charts. So, I select ‘Line’ for chart types of both quantity and sales ($).
Select the chart type for each series
  1. If the two series are in different units, select one of the series to the secondary axis. In this example, quantity is in numbers, and sales is in dollar ($) value. So, I am selecting the sales ($) series as the second axis of the table.
Put the sales series on a secondary axis
  1. Click the ‘OK’ button of the ‘Insert Chart’ dialog box.
Click OK

Now, I have overlaid quantity and sales graphs.

Overly chart with quantity and sales series

The blue line of the graph shows the quantity, and the orange line shows the sales ($). Now, it is very easy for me to compare the trends of both graphs together.

Also read: How to Create Bar of Pie Chart in Excel?

How to Overlay Multiple Graphs in Excel

Sometimes we have to overlay graphs that have already been created.

In that case, you can just superimpose the charts over each other (which will make it look as if the charts have been overlaid)

For example, when creating Sankey Diagrams in Excel without using Add-Ins, one of the steps is to overlay all the area charts (Sankey lines).

So, it is important to learn how to overlay multiple graphs in Excel.

I am creating a Sankey Diagram in Excel without using any Excel Add-Ins. I have created nine Sankey lines using 100% stacked area charts. I have formatted all the charts, also.

Sankey lines chart

Now, I have to overlay all the above 9 graphs. I have to follow the below steps to overlay all graphs.

  1. Select all the charts. To select all the charts, I have to hold the Control key and click on each graph.
Select all the charts
  1. Go to the ‘Shape Format’ tab.
Click on the shape format tab
  1. Click the ‘Align’ icon in the ‘Arrange’ group.
Click on the align option
  1. Click the ‘Align Center’ option.
Click on align center option

Then, all the selected charts will align the center as below.

All charts have been aligned to the centre
  1. Click the ‘Align’ icon in the ‘Arrange’ group again.
Click on the align option
  1. Click the ‘Align Middle’ option.
Click on the align middle option

The above steps would overlay all the charts in one place.

All nine charts have been overlaid

For this technique to work, your charts must be the same size and transparent.

In case they are not of the same size, you can follow the below steps to make the size the same.

  1. Select all the charts. Hold down the ‘Control’ key and click on each chart.
Select all the charts
  1. Go to the ‘Shape Format’ tab.
Click on the shape format tab
  1. Go to the ‘Size’ group and enter the height and width of all charts. If all the charts are the same size, we can see the sizes in height and width boxes. If the sizes are not matching, those boxes look empty. If any of the boxes are empty, we have to enter the expected length on it. In this case, I can see the height and width of all charts. So, I am not changing it.
Adjust the height and width
Also read: How To Switch Axes On Excel

How to Overlay Column Charts in Excel

In some reports, we have seen overlay column graphs. These overlay column graphs are easier to understand.

It gives us the opportunity to read the graph more quickly than a multiple-column chart.

Below I have sales details for a company. Column A represents the month, Column B shows the last year’s sales, and Column C shows the current year’s sales.

Data set with last year and current value

Now I need to create an overlay column chart. I can do it by following the below steps.

  1. Select the data table, including the column headings of the table.
Select the entire data set
  1. Go to the ‘Insert’ tab.
Click the insert tab
  1. Go to the ‘Charts’ group and click the Column Chart icon.
Click on the clustered column chart icon
  1. Click the 2-D Clustered Column chart from the expanded list.
Click on the clustered column option

Then, I can see the below 2-D clustered column chart for my data.

Clustered column chart with two series
  1. Right-click on one of last year’s sales columns (Blue color columns) and select the ‘Format Data Series…’ from the list.
Click on format data series option
  1. Go to the ‘Series Options’ of the ‘Format Data Series’ dialog box and change the Series Overlap to 100% and Gap Width to 75%.
Set series overlap at 100
  1. Go to the ‘Fill & Line’ option and change the ‘Color’ to a very light color. In this case, I am selecting the light orange color.
Change the color of one of the series
  1. Then, click on one of the ‘Current Year’ columns and go to the ‘Fill & Line’ option of the ‘Format Data Series’ dialog box.
Click on the fill in line option
  1. Select ‘No Fill’ for the Fill option.
Set the fill color to no fill
  1. Select ‘Solid line’ for the border option and change the width of the border to 2pt.
Set the border to solid line

Now, my chart is ready.

Two line charts have been overlaid

With the help of the above chart, I can clearly compare the current year’s monthly sales with last year’s monthly sales.

So, now you know different methods to overlay graphs in Excel. You can use these methods to overlay graphs and give more value to your charts.

Other Excel articles you may also like:

I am a huge fan of Microsoft Excel and love sharing my knowledge through articles and tutorials. I work as a business analyst and use Microsoft Excel extensively in my daily tasks. My aim is to help you unleash the full potential of Excel and become a data-slaying wizard yourself.

1 thought on “How to Overlay Graphs in Excel”

  1. So I basically need two line charts, one on top of the other, both using the same x-axis. The top half is two variables. The bottom half is the difference between those two variables. If I simply use secondary axis, I just get three lines all smashed over each other, and cannot discern anything from the chart. I need to move the difference line *below* the lines of the variables. Yes, I could just make two different charts and stack them on top of each other, but I would like to make it a little cleaner, and visually easier. Can you help?

    Reply

Leave a Comment