Excel - How to Create an Overlay Chart in Excel: Duplicate Charting Challenge - Episode 444
Microsoft Excel Tutorial: How to Create an Overlay Chart in Excel: Duplicate Charting Challenge.
Welcome back to the MrExcel netcast where we help you become an Excel pro. In today's episode, we will be tackling a question sent in by one of our viewers on how to duplicate a chart from our charting challenge. We will be focusing on creating an overlay chart, specifically the one submitted by Jerome Sullivan for our innovative chart challenge in November-December.
To begin, I will navigate to "MrExcel.com" and select the challenge of the month from the left-hand navigation. From there, I will choose the past challenges and scroll down to the innovative chart challenge. Our goal is to recreate the chart submitted by Jerome Sullivan, which shows the budget vs expenditure with the columns overlapping each other.
To create this chart in Excel, I will first set up my data series with four quarters, a column for budget, and a column for actual expenditures. Then, I will select the entire data set and insert a clustered column chart. However, the columns will initially appear next to each other instead of on top of each other. To fix this, I will select the second series, format data series, and choose to plot it on the secondary axis. This will force Excel to plot the columns on top of each other.
Next, I will adjust the gap width between the columns to make them wider. This can be done by selecting the budget series, right-clicking, and choosing format data series. Then, on the options tab, I will decrease the gap width to make the columns wider. I will repeat this process for the actual expenditures series, making sure both columns are the same width.
However, we are not done yet. Excel has automatically set different scales for the two axes, which can be misleading. To fix this, I will customize both axes to have the same scale. I will also change the colors of the columns to white for budget and red for actual expenditures. Finally, I will remove the numbers on the right-hand side of the chart, as they are no longer necessary once the scales are identical.
And there you have it, a perfectly replicated overlay chart in Excel. Thank you for tuning in to this episode of the MrExcel netcast. Don't forget to subscribe to our channel for more Excel tips and tricks. We'll see you next time for another netcast from MrExcel.
Buy Bill Jelen's latest Excel book: https://www.mrexcel.com/products/latest/
You can help my channel by clicking Like or commenting below: https://www.mrexcel.com/like-mrexcel-...
In a recent charting challenge, Jerome Sullivan sent in an interesting chart where the budget and actual numbers were actually plotted on top of each other. Episode 444 shows the non-intuitive steps required to create this type of chart.
This blog is the video podcast companion to the book, Learn Excel from MrExcel and Charts and Graphs for Microsoft Office Excel 2007. Download a new two minute video every workday to learn one of the 277 tips from the book!
Table of Contents:
(00:00) Introduction to the charting challenge
(00:20) Duplicating a chart from the challenge
(01:02) Setting up the data series and creating a clustered column chart
(01:22) Formatting the data series to plot on a secondary axis
(02:02) Adjusting the gap width and customizing the axis scales
(02:47) Changing the colors and removing unnecessary axis labels
(03:32) Clicking Like really helps the algorithm
#excel #microsoft #microsoftexcel #exceltutorial #exceltips #exceltricks #excelmvp #freeclass #freecourse #freeclasses #excelclasses #microsoftmvp #walkthrough #evergreen #spreadsheetskills #analytics #analysis #dataanalysis #dataanalytics #mrexcel #spreadsheets #spreadsheet #excelhelp #accounting #tutorial
This video answers these common search terms:
Adjusting gap width and bar width in Excel charts
Changing colors of data series in Excel charts
Creating a budget vs expenditure chart in Excel
Creating a chart with columns on top of each other
Creating a clustered column chart in Excel
Customizing axis scales in Excel charts
Formatting axis in Excel charts
Formatting data series in Excel charts
How to duplicate a chart in Excel
Making one column wider than the other in Excel charts
Plotting columns on a secondary axis in Excel
Removing tick mark labels in Excel charts
Join the MrExcel Message Board discussion about this video at https://www.mrexcel.com/board/threads...