Show two different orders of magnitude in a chart by using a secondary axis along with columns and lines
Transcript of the video:
MrExcel podcast is sponsored by Excel-XL.
Excel Charts Chapter 6; Mixing Chart Types.
All right so, you know some people kind of complained after Excel 2003 because 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 wanna have some control.
So, here I have monthly Sales going across, I also have two other lines that are yearly numbers, so the year-to-date sales and our goal for the whole year.
I wanna create a nice chart showing how our sales progressed towards the goal, so I Insert, I'm just gonna create everything as a Column Chart, 2-D Column, a little smaller, okay so couple things we really lose the detail of the smaller items, so I want 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 gonna choose the Year-to-Date number, wanna do Ctrl+1, so it gets a format or on the Layout tab, click Format Selection or in Excel 2010 double-click and those will work.
I'm gonna move that to the Secondary Axis.
All right and I'm also gonna choose the third item, the goal and move that to a Secondary Axis.
All right, there choose that with 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 wanna 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 gonna go back to the Design tab and say change chart type, I'm gonna 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, again change chart type, choose a Line.
And they really kind of see through and you can actually see the various monthly Sales.
All right, wanna make those monthly sales a bit more prominent, choose that item or do Format Cells again .
Ctrl+1 and I'm gonna make the columns wider by making the gap narrower, click Close.
Right, add some indication that the numbers along the left are tied to those blue columns.
So, we'll choose those numbers 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...
We know what our total goal was here, is constant throughout the year and how we progressed towards that goal over the course of the year.
So now here's a chart where we combine one series as columns, 2 other series is lined.
Now 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 where lined, 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...
Right hey, I wanna thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
Excel Charts Chapter 6; Mixing Chart Types.
All right so, you know some people kind of complained after Excel 2003 because 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 wanna have some control.
So, here I have monthly Sales going across, I also have two other lines that are yearly numbers, so the year-to-date sales and our goal for the whole year.
I wanna create a nice chart showing how our sales progressed towards the goal, so I Insert, I'm just gonna create everything as a Column Chart, 2-D Column, a little smaller, okay so couple things we really lose the detail of the smaller items, so I want 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 gonna choose the Year-to-Date number, wanna do Ctrl+1, so it gets a format or on the Layout tab, click Format Selection or in Excel 2010 double-click and those will work.
I'm gonna move that to the Secondary Axis.
All right and I'm also gonna choose the third item, the goal and move that to a Secondary Axis.
All right, there choose that with 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 wanna 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 gonna go back to the Design tab and say change chart type, I'm gonna 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, again change chart type, choose a Line.
And they really kind of see through and you can actually see the various monthly Sales.
All right, wanna make those monthly sales a bit more prominent, choose that item or do Format Cells again .
Ctrl+1 and I'm gonna make the columns wider by making the gap narrower, click Close.
Right, add some indication that the numbers along the left are tied to those blue columns.
So, we'll choose those numbers 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...
We know what our total goal was here, is constant throughout the year and how we progressed towards that goal over the course of the year.
So now here's a chart where we combine one series as columns, 2 other series is lined.
Now 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 where lined, 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...
Right hey, I wanna thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
