In today's episode, #1296, Bill shows us how to mix Chart types in Excel 2010 so that we may present two (2) distinct Orders of Magnitude in a single Chart! Watch and see how its done!
"Charts And Graphs: Microsoft Excel 2010", by Bill Jelen
"Charts And Graphs: Microsoft Excel 2010", by Bill Jelen
Transcript of the video:
MrExcel podcast is sponsored by Easy-XL.
Excel Charts, Chapter 6: Mixing Chart Types.
All right! So, you know, some people kind of complain after Excel 2003 because the the mixed chart types disappeared from the wizard you no longer have the column line charts.
Well, you can still create them they're just harder to do, but actually when you get right down to it you need to be able to do it this way anyway because you want to have some control.
So, here I have monthly sales going across I also have two other lines that are yearly number.
So, the year to date sales and our goal for the whole year.
I want to create a nice chart showing how our sales progressed towards the goal.
So, I'm going to Insert I'm just going to create everything as a Column Chart a 2-D Column.
It's a little bit smaller okay so, a couple things we really lose the detail of the smaller items.
So, I'm going to take those smaller chart items and move them to a secondary axis.
Actually I want to leave that the small items there and take the other two items and move those to a secondary axis.
So, I'm going to choose the year to date number when I do control one to get to format or on the Layout tab, click Format Selection or in Excel 2010 double click any of those will work and I'm going to move that to the secondary axis.
All right and I'm also going to choose the third item the goal and move that to a secondary axis right there choose that put that on the secondary axis see how this looks so far All right! So now, we have our monthly sales numbers tied to the left axis and our year to date numbers tied to the right axis.
I want to put a lot of focus on those month numbers and make the other items just be simple lines.
So, we have the goal chosen I'm going to go back to the design tab and say, Change Chart Type.
I'm going to change that to a line with dots.
Now, the cool thing is once you change that to a line and change this one to a line.
So, again Change Chart Type, choose a line.
Now, you can really kind of see through and you can actually see the various monthly sales.
All right, want to make those monthly sales a bit more prominent choose that item will do Format Cells again Control+1 and I'm going to make the columns wider by making the gap narrower, click close want to add some indication that the numbers, along the left are tied to those blue columns.
So, we'll choose those numbers then on the Home tab change the font color to match the color of the bars.
All right! So now, we have a nice chart showing our sales month, by month, by month, we know what our total goal was here is constant throughout the year and how we progress towards that goal over the course of the year.
So now, here's a chart where we combine one series as columns to other series is lined you know, although we weren't able to just choose that from the first step of the wizard.
In fact, the first step of the wizard it would have been very unlikely that ever would have had a chart where the first series was columns second and third series were lines, so just being able to go through and control that bit, by bit, by bit.
I really gives you a lot more chance to come up with a chart that's going to match exactly.
All right! Hey, wanna thank you for stopping by.
We'll see you next time or other netcast from MrExcel.
Excel Charts, Chapter 6: Mixing Chart Types.
All right! So, you know, some people kind of complain after Excel 2003 because the the mixed chart types disappeared from the wizard you no longer have the column line charts.
Well, you can still create them they're just harder to do, but actually when you get right down to it you need to be able to do it this way anyway because you want to have some control.
So, here I have monthly sales going across I also have two other lines that are yearly number.
So, the year to date sales and our goal for the whole year.
I want to create a nice chart showing how our sales progressed towards the goal.
So, I'm going to Insert I'm just going to create everything as a Column Chart a 2-D Column.
It's a little bit smaller okay so, a couple things we really lose the detail of the smaller items.
So, I'm going to take those smaller chart items and move them to a secondary axis.
Actually I want to leave that the small items there and take the other two items and move those to a secondary axis.
So, I'm going to choose the year to date number when I do control one to get to format or on the Layout tab, click Format Selection or in Excel 2010 double click any of those will work and I'm going to move that to the secondary axis.
All right and I'm also going to choose the third item the goal and move that to a secondary axis right there choose that put that on the secondary axis see how this looks so far All right! So now, we have our monthly sales numbers tied to the left axis and our year to date numbers tied to the right axis.
I want to put a lot of focus on those month numbers and make the other items just be simple lines.
So, we have the goal chosen I'm going to go back to the design tab and say, Change Chart Type.
I'm going to change that to a line with dots.
Now, the cool thing is once you change that to a line and change this one to a line.
So, again Change Chart Type, choose a line.
Now, you can really kind of see through and you can actually see the various monthly sales.
All right, want to make those monthly sales a bit more prominent choose that item will do Format Cells again Control+1 and I'm going to make the columns wider by making the gap narrower, click close want to add some indication that the numbers, along the left are tied to those blue columns.
So, we'll choose those numbers then on the Home tab change the font color to match the color of the bars.
All right! So now, we have a nice chart showing our sales month, by month, by month, we know what our total goal was here is constant throughout the year and how we progress towards that goal over the course of the year.
So now, here's a chart where we combine one series as columns to other series is lined you know, although we weren't able to just choose that from the first step of the wizard.
In fact, the first step of the wizard it would have been very unlikely that ever would have had a chart where the first series was columns second and third series were lines, so just being able to go through and control that bit, by bit, by bit.
I really gives you a lot more chance to come up with a chart that's going to match exactly.
All right! Hey, wanna thank you for stopping by.
We'll see you next time or other netcast from MrExcel.
