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.
Earlier, I created two separate charts for the quantity and sales ($) columns of the above data table.
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.
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.
- Select the data table, including the column headings of the table.
- Go to the ‘Insert’ tab.
- Go to the ‘Charts’ group and click the ‘Combo’ chart icon.
- Click the ‘Create Custom Combo Chart…’ from the expanded list.
- 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 ($).
- 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.
- Click the ‘OK’ button of the ‘Insert Chart’ dialog box.
Now, I have overlaid quantity and sales graphs.
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.
Now, I have to overlay all the above 9 graphs. I have to follow the below steps to overlay all graphs.
- Select all the charts. To select all the charts, I have to hold the Control key and click on each graph.
- Go to the ‘Shape Format’ tab.
- Click the ‘Align’ icon in the ‘Arrange’ group.
- Click the ‘Align Center’ option.
Then, all the selected charts will align the center as below.
- Click the ‘Align’ icon in the ‘Arrange’ group again.
- Click the ‘Align Middle’ option.
The above steps would overlay all the charts in one place.
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.
- Select all the charts. Hold down the ‘Control’ key and click on each chart.
- Go to the ‘Shape Format’ tab.
- 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.
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.
Now I need to create an overlay column chart. I can do it by following the below steps.
- Select the data table, including the column headings of the table.
- Go to the ‘Insert’ tab.
- Go to the ‘Charts’ group and click the Column Chart icon.
- Click the 2-D Clustered Column chart from the expanded list.
Then, I can see the below 2-D clustered column chart for my data.
- Right-click on one of last year’s sales columns (Blue color columns) and select the ‘Format Data Series…’ from the list.
- Go to the ‘Series Options’ of the ‘Format Data Series’ dialog box and change the Series Overlap to 100% and Gap Width to 75%.
- 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.
- Then, click on one of the ‘Current Year’ columns and go to the ‘Fill & Line’ option of the ‘Format Data Series’ dialog box.
- Select ‘No Fill’ for the Fill option.
- Select ‘Solid line’ for the border option and change the width of the border to 2pt.
Now, my chart is ready.
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:
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?