How to create Overlay Chart in Microsoft Excel

When we create a bar chart with 2 data elements, we will usually show the bars side by side. But there is a better way of showing this data especially if we want to compare target v/s results. We can use the overlay or overlapped bar chart in this case in Microsoft Excel.

Let’s see how this is achieved. There is no overlapped bar chart by default which we can use, so we need to make some modifications to a bar chart to accomplish this.

 

img1

 

We have monthly data showing the targets and sales achieved, we need to first plot a 2D clustered column chart. To do it follow the below mentioned steps:-

    • Select the data range A1:C13.
    • Go to “Insert” menu on the ribbon, in the “Charts” group click on “Clustered Column Chart”.

 

img2

 

    • Right clickon one of the bars either for target or results – let’spick results, and select Format data series.

 

img3

 

  • The format data series dialog box will appear. Under Series options, you will find series overlap. Drag it till the bar reaches 100%.

 

img4

 

img5

 

The 2 bars on the chart now completely overlap each other. A few more steps to go and we are done. Do the required formatting like removing gridlines and adding in axes labels.

 

To give the bars the overlap kind of look follow the below mentioned steps:-

    • If the format data series dialog box is already open on the right, just click on the target bars in the chart.

 

img6

 

    • After clicking on the target bars, right click on them, and select Format data series.
    • Under Fill & Line in the dialog box, select Solid Fill.

 

img7

 

  • Then choose the color which matches the color of the bar, in this case light blue.
  • Then under width, change it to 10pt. Change the cap type to flat and join type to Miter.

 

img8

 

img9

 

Conclusion:- Now you can see in the above picture how you can use this chart to compare the target V/S achievement. It is showing the over-achievement and under-achievement very clearly.

 

Download XLS

If you liked our blogs, share it with your friends on Facebook. And also you can follow us on Twitter and Facebook.
We would love to hear from you, do let us know how we can improve, complement or innovate our work and make it better for you. Write us at info@exceltip.com

 

Leave a Reply

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

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Terms and Conditions of use

The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.

Visit Us On TwitterVisit Us On FacebookVisit Us On Google PlusVisit Us On Youtube