How to Overlay Graphs in Excel
Aug 09, 2022
Learning how to overlay charts in Excel is one of the best ways to compare two data sets within one chart. A great example 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, an Overlay Chart will allow you to interpret this data quickly and easily.
You can also watch this video to see how it's done if you are more of a visual learner.
Suggested read: How to Get Microsoft Excel for Mac
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. Head to the All Charts tab to plot and overlay your graphs and format data series manually in Microsoft Excel.
6. From there, choose Line as your chart type and the line graph type 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.
Suggested read: How to Add Leading Zeros in Microsoft Excel
Overlay Line Graph with Column Chart
If your data table requires a different option, there is a straightforward process to overlay a line graph with other graph types, such as bar or column charts.
These combinations are great if you compare values from one particular series with all other series.
1. Choose the dataset you want to plot across the two graphs.
2. From here, click on the Insert tab from the Ribbon.
3. 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. Choose the first series chart as Line and 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 select OK, a chart will show on your Excel spreadsheet with line graphs overlapped with clustered columns.
8. Finally, you can modify the charts to suit your needs.
Suggested read: Everything You Need to Know About the Sunburst Chart In Excel
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.
-
Next, select OK. The chart will show in your Excel spreadsheet and can be modified.
Suggested read: Basic Microsoft Excel Formulas You Need to Know
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 types 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.
Related Articles:
Excel: Remove Trailing Spaces Quickly and Easily With These Simple Steps
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.
