How to Overlay Graphs in ExcelAug 09, 2022
Learning how to overlay charts in Excel is one of the best ways to compare two sets of data within one chart. A great example of this would be when you want to see actual revenue vs. target revenue or actual rating vs. target rating.
You can achieve this using a Clustered Column in Microsoft Excel, but it will be slow to read and hard to interpret by anyone reading it.
However, if you use an Overlay Chart for this, it will allow you to interpret this data quickly and easily.
Overlay Line Graph with Another Line Graph
In our first example, let's look at how to overlay line graphs with one another in Excel.
1. Choose your entire data set.
2. Head to the Insert tab from your Excel ribbon.
3. Click on Recommended Charts from the Chart title.
4. From here, the Insert Chart dialog box will appear.
5. To plot and overlay your graphs and format data series manually in Microsoft Excel, head to the All Charts tab.
6. From there, choose Line as your chart type, and then choose the type of line graph that suits your needs.
7. Now you have chosen your graph type, select OK.
8. A chart will show overlaying line graphs within your spreadsheet.
Overlay Line Graph with Column Chart
If your data table requires a different option, there is a very easy process to overlay a line graph with other types of graph, such as bar or column charts.
These sorts of combinations are great if you need to compare values from one particular series with all other series.
1. To start, choose the entire dataset you want to plot across the two graphs.
2. From here, click on the Insert tab from the Ribbon.
3. From there, choose the Recommended Charts section from the Charts group.
4. The insert chart option will appear. Choose the All Charts tab from that box.
5. From there, choose Combo as the chart type from the left-hand side of the box.
6. From there, choose the first series chart as Line, and choose Clustered Colum for the rest. These options are available from the drop-down within the Choose the chart type and axis for your data series section.
7. Once you have selected OK, a chart will show on your Excel spreadsheet with line graphs overlayed with clustered columns.
8. Finally, you can modify the charts to suit your needs.
Overlay Line Graph with Scatter Chart
You can also create overlay charts with line graphs and scatter charts in Excel. One example is that you overlay line graphs with smooth scatter graphs.
Firstly, choose your entire data set, and head to the Insert tab from the Excel Ribbon.
Choose Recommended Charts from the Charts group.
The Insert Chart option will appear. From here, select All Charts.
Choose Combo as your chart type.
Choose Line as your first chart, and Scatter with Smooth Lines and Markers as your second chart.
Next, select OK. The chart will show in your excel spreadsheet and can be modified to your liking.
Frequently Asked Questions About How to Overlay Graphs in an Excel Spreadsheet
Where is the charts tab that contains chart types in Excel?
To find a menu of all the chart types and graphs in Excel, select the down arrow by "Charts" from the Insert tab in the Ribbon.
What chart type should I choose for the best combo chart?
Pie charts and sunburst charts are fantastic examples of two different types that you can use when creating a demo to practice this tutorial. Search for them in the chart type tab, and double-click to add them to your worksheet.
What are the recommended charts businesses use?
Which graphs in Excel you opt for will depend on your business needs, but some of the most common ones include sunburst charts, bar charts, and line graphs.
Want to Make Excel Work for You? Try out 5 Amazing Excel Templates & 5 Unique Lessons
We hate SPAM. We will never sell your information, for any reason.