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!
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!
Transcript of the video:
Welcome back to the MrExcel netcast.
I'm Bill Jelen.
Today's question was sent in by someone, who wanted to figure out.
How to duplicate one of the charts from our charting challenge.
So, I'm here at "MrExcel.com" and along the left-hand navigation.
I'm going to choose challenge of the month and then choose past challenges and then the innovative chart challenge from November-December.
Let me scroll down here and they were interested in this chart, sent in by Jerome Sullivan.
That shows, budget vs expenditure and the columns are actually right on top of each other.
You can see the budget numbers in white and the actual expenditures in red.
Let's see, if we can create this chart in Excel.
It's actually pretty tricky to do I set up my data series here, with the four quarters and a column for budget and a column for actual.
I'll choose that whole data set, use insert chart and say that, I want a clustered column chart.
I don't want them stacked on top of each other, click [ finish ] and initially I get not the correct effect that the columns are next to each other, instead of on top of each other.
It turns out the trick is very similar to the trick from, Monday.
I want to choose the second series, format data series and say that it should be plotted on the secondary axis.
This actually forces Excel to plot the columns right on top of each other.
Now, luckily here I have one series where budget was larger than actual and I'm able to click on that bar to select the budget series, right click and choose format data series and then go to the options tab.
Here I want to change the gap width by making the gap between the bars smaller.
I'll actually make each bar wider.
So, I'll change this from 150, may be back to 70 and you can see in the preview there it sort of works, will click [ OK ].
Alright! And now, we can see the two bars, but we're not done yet.
There's a very dangerous thing that's been set up here because we have now plotted each column on a different axis.
Excel is chosen the left axis to run from 23500 to 28000 and the right axis to run from zero to 35,000.
We need to customize both of these to make sure that, they are the exact same number.
I like the 0 to 35,000.
So, I'm going to go over to the left axis, right-click, choose format axis and on the scale tab.
Uncheck the auto box say that I wanted to start at zero, uncheck the maximum.
Say that I wanted to go 35000.
Click [ ok ] and now, we have both scales, set up exactly identical.
To change the colors fairly easy to do, click on the expense tab, choose format data series, change the color to white.
Click [ ok ], click on one of the actual columns, right click format data series.
Change the color to red, Click [ OK ].
Now, in Jerome's chart, he actually made the right numbers be red to indicate that they were actuals.
In my opinion, we can actually get rid of these right hand numbers.
Once we know that we've set both axis up to be the exact same scale.
So, I'm going to right click, format axis on the scale tab.
I'm going to turn off the automatic make sure that it goes from 0 to 35,000.
And then on the patterns tab, go to tick mark labels and say none, click [ ok ].
And now, we've completely eliminated those numbers along the right-hand side.
There you have it, how to create a chart where the two columns are on top of each other.
But, one column is wider than the other so you can see it behind.
Thanks for stopping by.
We'll see you next time for another netcast, from MrExcel.
I'm Bill Jelen.
Today's question was sent in by someone, who wanted to figure out.
How to duplicate one of the charts from our charting challenge.
So, I'm here at "MrExcel.com" and along the left-hand navigation.
I'm going to choose challenge of the month and then choose past challenges and then the innovative chart challenge from November-December.
Let me scroll down here and they were interested in this chart, sent in by Jerome Sullivan.
That shows, budget vs expenditure and the columns are actually right on top of each other.
You can see the budget numbers in white and the actual expenditures in red.
Let's see, if we can create this chart in Excel.
It's actually pretty tricky to do I set up my data series here, with the four quarters and a column for budget and a column for actual.
I'll choose that whole data set, use insert chart and say that, I want a clustered column chart.
I don't want them stacked on top of each other, click [ finish ] and initially I get not the correct effect that the columns are next to each other, instead of on top of each other.
It turns out the trick is very similar to the trick from, Monday.
I want to choose the second series, format data series and say that it should be plotted on the secondary axis.
This actually forces Excel to plot the columns right on top of each other.
Now, luckily here I have one series where budget was larger than actual and I'm able to click on that bar to select the budget series, right click and choose format data series and then go to the options tab.
Here I want to change the gap width by making the gap between the bars smaller.
I'll actually make each bar wider.
So, I'll change this from 150, may be back to 70 and you can see in the preview there it sort of works, will click [ OK ].
Alright! And now, we can see the two bars, but we're not done yet.
There's a very dangerous thing that's been set up here because we have now plotted each column on a different axis.
Excel is chosen the left axis to run from 23500 to 28000 and the right axis to run from zero to 35,000.
We need to customize both of these to make sure that, they are the exact same number.
I like the 0 to 35,000.
So, I'm going to go over to the left axis, right-click, choose format axis and on the scale tab.
Uncheck the auto box say that I wanted to start at zero, uncheck the maximum.
Say that I wanted to go 35000.
Click [ ok ] and now, we have both scales, set up exactly identical.
To change the colors fairly easy to do, click on the expense tab, choose format data series, change the color to white.
Click [ ok ], click on one of the actual columns, right click format data series.
Change the color to red, Click [ OK ].
Now, in Jerome's chart, he actually made the right numbers be red to indicate that they were actuals.
In my opinion, we can actually get rid of these right hand numbers.
Once we know that we've set both axis up to be the exact same scale.
So, I'm going to right click, format axis on the scale tab.
I'm going to turn off the automatic make sure that it goes from 0 to 35,000.
And then on the patterns tab, go to tick mark labels and say none, click [ ok ].
And now, we've completely eliminated those numbers along the right-hand side.
There you have it, how to create a chart where the two columns are on top of each other.
But, one column is wider than the other so you can see it behind.
Thanks for stopping by.
We'll see you next time for another netcast, from MrExcel.
