How to Overlay Charts in Excel

How to Overlay Charts in Excel

Learning how to overlay charts in Microsoft Excel is useful when comparing two different groups of data in one graph altogether

A graph is a diagram that depicts the connections between two or more data sets. It shows a variable’s variation in contrast to the variance of one or more other variables. 

An overlay chart displays not one but two sets of data, one on top of the other. The data is presented in this fashion to make it simple to visually recognize events that occur simultaneously and rapidly evaluate if there is a correlation or relationship between them.

An overlay chart shows two values on the same axis, which is usually the X-axis. An overlay chart, for example, can be used to show two occurrences over time, such as profits from a business and the weather of the location. 

By showing the data on an overlay chart, we can visually assess if these two variables agree in this situation. This method of displaying two data groups can also help visualize the correlation between them and easily identify the connection. 

Not sure how to use the overlay chart? Do not fear, as we are here to go through some examples to demonstrate step-by-step how to apply the overlay chart!

By clicking on the link below, you can find a copy of the spreadsheet.

Once you’re ready to begin the tutorial, we’ll get right to work on comparing two different data grounds by using the overlay chart on Microsoft Excel!


Creating an Overlay Chart in Microsoft Excel

Example 1

In this example, imagine you are the financial controller for a fast-food franchise. 

Every year-end, you are required to report on the yearly revenue. You will also need to analyze how it has performed compared to the projected revenue created at the beginning of the year. 

By using an overlay chart, you can easily compare and visualize the difference between the actual revenue against the projected revenue for each branch. 

Follow these steps to start creating an overlay chart for your revenue comparison:

  1. First, we need to compile the projected revenue and actual revenue for each branch and insert these data into the sheet neatly. 

How to Overlay Charts in Excel

  1. Then, we will select the entire range of data to include when we create the overlay chart. In this example, we will select A1:C7.

How to Overlay Charts in Excel

  1. Now we can press Insert, select Column in the Charts section, and select Clustered Column to use vertical rectangular to compare values across categories. 

How to Overlay Charts in Excel

  1. Once the above steps are completed, a default chart will appear in the center of your sheet. 

How to Overlay Charts in Excel

  1. To edit the chart, we can double click on any surface area of the chart. A box will pop up on the right side of your sheet. 

How to Overlay Charts in Excel

  1. We can also access the formatting box by selecting Chart, followed by selecting Format

Select Format to access the editing section

  1. First, we can change the chart title to ‘Revenue Analysis’ to help identify the purpose of this graph. You can also change the font, sizing, color, etc, as to your preference.

Change the chart title

  1. Another area we can change is the bounds for the vertical axis to suit your data better. In our case, we will change the maximum bound to 1,000,000 to show more relevance between the chart and the data table. 

Amend the maximum amount

  1. You can also change the color of the data columns by selecting the desired colors in Series “Projected Revenue” and Series “Actual Revenue”. In this example, we will remain as it is. 

Select desired color

  1. Once the chart is formatted to our liking, it will look like this:

Final Chart outlook

To give you a different scenario, you can easily use the overlay chart to measure the correlation between two sets of data. Imagine you want to study if the volume of rain affects gym members’ attendance at fitness classes. 

Your hypothesis is if the volume of rain is higher, the lower the amount of attendance on that day. 

Once you have plotted all the relevant data into the excel sheet, follow the above steps to create an overlay chart. The chart will show that the shorter the column for rain volume, the taller the column for class attendance.

There you go! Now you know how to create overlay charts and visualize and compare two separate data sets in one graph.

Don’t forget to have a look at the other of Microsoft Excel’s fantastic features to see how they can help you improve and simplify your real-world work!

Make sure to sign up for our newsletter to be the first to learn about our new guides and tutorials.

Get emails from us about Microsoft Excel.

Our goal this year is to create lots of rich, bite-sized tutorials for Microsoft Excel users like you. If you liked this one, you'd love what we are working on! Readers receive ✨ early access ✨ to new content.

Leave a Reply

Your email address will not be published. Required fields are marked *

You May Also Like