Pinterest Pixel

How to Create Overlay Charts in Excel

In this article, you will learn How to Overlay Charts in Excel and easily compare two sets... read more

Download Excel Workbook
John Michaloudis
Posted on

Overview

How to Create Overlay Charts in Excel | MyExcelOnline

In this article, you will learn How to Overlay Charts in Excel and easily compare two sets of data in one graph, like actual sales vs target sales results, actual rating vs target rating, etc.

If you try to plot Actual vs Target values in form of a Clustered Column in Excel, it will look like this:

How to Create Overlay Charts in Excel

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.

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.

How to Create Overlay Charts in Excel

This overlay of graphs may seem like a difficult exercise but it is a very easy process.

Keep reading to learn how to overlay charts in Excel!

In the example below, we have both actual and planned sales amount for each month and you need to plot it on a graph for easy comparison.

See also  Process Map Using Excel

How to Create Overlay Charts in Excel

Follow the step-by-step tutorial on how to overlay graphs in Excel and download this Excel workbook to practice along:

download excel workbookOverlap-Graphs.xlsx

STEP 1: Select all the cells in the table.

How to Create Overlay Charts in Excel

STEP 2: Go to Insert Tab > In the Charts Group, click on the Clustered Column Chart icon.

How to Create Overlay Charts in Excel

A clustered column chart will appear next to the data table.

How to Create Overlay Charts in Excel | MyExcelOnline

STEP 3: Click on the Plan Value Bars.

How to Create Overlay Charts in Excel

STEP 4: Right-click on the bar and select Format Data Series.

How to Create Overlay Charts in Excel

STEP 5: In the Format Data Series dialog box, select Series Overlap as 100%.

How to Create Overlay Charts in Excel

STEP 5: Under Fill Tab, Select No Fill.

How to Create Overlay Charts in Excel

STEP 6: Select Solid Line as Border, Dark Blue as Color, and 2pt as Width.

How to Create Overlay Charts in Excel

STEP 7: Right Click Actual Value Bar and Select Format Data Series.

How to Create Overlay Charts in Excel

STEP 8: Select Solid Fill and Light Blue as color.

How to Create Overlay Charts in Excel

STEP 9: Select Gridlines and Press Delete.

How to Create Overlay Charts in Excel

STEP 10: Select Chart Title and Press Delete.

See also  In-Cell Bar Charts with the REPT Function

How to Create Overlay Charts in Excel

Your Overlay Chart is now ready!

How to Create Overlay Charts in Excel

Conclusion

In summary, this is how to superimpose graphs in Excel:

First, you need to edit your “Plan” graph by clicking on its series and pressing the CTRL+1 shortcut.  Then within the Format box, you need to choose:

FILL: NO FILL
BORDER COLOR: SOLID LINE & DARK
BORDER STYLE: 2pt WIDTH

Then you need to edit your “Actual” graph by clicking on its series and pressing the CTRL+1 shortcut.  Then within the Format box, you need to choose:

FILL: SOLID FILL & LIGHT COLOR
SERIES OPTIONS: 100% OVERLAPPED
GAP WIDTH: 60%

 

So, just by changing the format of the chart you can interpret the data easily. 

Further Learning:

HELPFUL RESOURCE:

Make sure to download our FREE PDF on the 333 Excel keyboard Shortcuts here:

If you like this Excel tip, please share itEmail this to someone

email

Pin on Pinterest

Pinterest

Share on Facebook

Facebook

Tweet about this on Twitter

Twitter

Share on LinkedIn

Linkedin

See also  Save A Chart Template
How to Create Overlay Charts in Excel | MyExcelOnline
Founder & Chief Inspirational Officer at MyExcelOnline.com

John Michaloudis is a former accountant and finance analyst at General Electric, a Microsoft MVP since 2020, an Amazon #1 bestselling author of 4 Microsoft Excel books and teacher of Microsoft Excel & Office over at his flagship Academy Online Course.

Get Video Training

Advance your Microsoft Excel & Office Skills with the MyExcelOnline Academy!

Dramatically Reduce Repetition, Stress, and Overtime!
Exponentially Increase Your Chances of a Promotion, Pay Raise or New Job!

Learn in as little as 5 minutes a day or on your schedule.

Learn More!