{"id":1248,"date":"2021-02-13T16:14:45","date_gmt":"2021-02-13T15:14:45","guid":{"rendered":"https:\/\/www.myexcelonline.com\/?p=1248"},"modified":"2024-03-22T18:52:57","modified_gmt":"2024-03-22T17:52:57","slug":"overlap-graphs-in-excel","status":"publish","type":"post","link":"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/overlap-graphs-in-excel\/","title":{"rendered":"How to Create Overlay Charts in Excel"},"content":{"rendered":"

\"Overlap<\/a><\/p>\n

In this article, you will learn How to Overlay Charts in Excel<\/a> and easily compare two sets of data in one graph<\/strong>, like actual sales vs target sales<\/strong> results, actual rating vs target rating<\/strong>, etc.<\/p>\n

If you try to plot Actual vs Target values in form of a Clustered Column in Excel, it will look like this:<\/p>\n

\"How<\/a><\/p>\n

Even though, it will get the job done it will be slow to read and difficult to interpret by the users. Comparing the right of each bar will take time and may be prone to errors.<\/p>\n

Whereas, by just changing the format of the Clustered column we can convert it to overlay chart. Overlay Chart will help you to interpret the data at a glance.<\/p>\n

\"How<\/a><\/p>\n

This overlay of graphs may seem like a difficult exercise but it is a very easy process<\/strong>.<\/p>\n

Keep reading to learn how to overlay charts in Excel!<\/h3>\n

In the example below, we have both actual and planned sales amount for each month<\/strong> and you need to plot it on a graph for easy comparison.<\/p>\n

\"How<\/a><\/p>\n\n

Follow the step-by-step tutorial<\/strong> on how to overlay graphs in Excel<\/a> and download this Excel workbook<\/strong> to practice along:<\/p>\n

\n

download excel workbook<\/strong><\/i><\/span>Overlap-Graphs.xlsx<\/span><\/a><\/p>\n<\/div>\n

STEP 1:<\/strong><\/span> Select all the cells in the table.<\/p>\n

\"How<\/a><\/p>\n

STEP 2:<\/strong><\/span> Go to Insert<\/strong> Tab > In the Charts Group<\/strong>, click on the Clustered Column Chart<\/strong> icon.<\/p>\n

\"How<\/a><\/p>\n

A clustered column chart<\/a><\/strong> will appear next to the data table.<\/p>\n

\"\"<\/a><\/p>\n

STEP 3:<\/strong><\/span> Click on the Plan Value<\/strong> Bars.<\/p>\n

\"How<\/a><\/p>\n

STEP 4:<\/strong><\/span> Right-click<\/strong> on the bar and select Format Data Series<\/strong>.<\/p>\n

\"How<\/a><\/p>\n

STEP 5:<\/strong><\/span> In the Format Data Series<\/strong> dialog box, select Series Overlap<\/strong> as 100%.<\/strong><\/p>\n

\"How<\/a><\/p>\n

STEP 5:<\/span> <\/strong>Under Fill Tab, Select No Fill.<\/strong><\/p>\n

\"How<\/a><\/p>\n

STEP 6:<\/span> <\/strong>Select Solid Line <\/strong>as Border,<\/strong> Dark Blue<\/strong> as Color, and 2pt<\/strong> as Width.<\/p>\n

\"How<\/a><\/p>\n

STEP 7:<\/span> <\/strong>Right Click Actual Value Bar<\/strong> and Select Format Data Series<\/strong>.<\/p>\n

\"How<\/a><\/p>\n

STEP 8: <\/span><\/strong>Select Solid Fill<\/strong> and\u00a0Light Blue <\/strong>as color.<\/p>\n

\"How<\/a><\/p>\n

STEP 9:<\/span> <\/strong>Select Gridlines<\/strong> and Press Delete<\/strong>.<\/p>\n

\"How<\/a><\/p>\n

STEP 10:<\/span> <\/strong>Select Chart Title<\/a><\/strong> and Press Delete<\/strong>.<\/p>\n

\"How<\/a><\/p>\n

Your Overlay Chart is now ready!<\/p>\n

\"How<\/a><\/p>\n

Conclusion<\/span><\/h3>\n

In summary, this is how to superimpose graphs in Excel<\/a>:<\/p>\n

First, you need to edit your “Plan<\/strong>” graph by clicking on its series and pressing the\u00a0CTRL+1<\/strong> shortcut<\/a>.\u00a0 Then within the Format box, you need to choose:<\/p>\n

FILL:<\/strong> NO FILL
\nBORDER COLOR:<\/strong> SOLID LINE & DARK
\nBORDER STYLE:<\/strong> 2pt WIDTH<\/p>\n

Then you need to edit your “Actual<\/strong>” graph by clicking on its series and pressing the CTRL+1 shortcut.\u00a0 Then within the Format box, you need to choose:<\/p>\n

FILL:<\/strong> SOLID FILL & LIGHT COLOR
\nSERIES OPTIONS:<\/strong> 100% OVERLAPPED
\nGAP WIDTH: <\/strong>60%<\/p>\n

 <\/p>\n

So, just by changing the format of the chart<\/a> you can interpret the data easily.\u00a0<\/em><\/p>\n

Further Learning:<\/strong><\/p>\n