Rick asks how to set up a chart with two different orders of magnitude. This was a built-in chart type in Excel 2003, but it is harder in Excel 2007. In today's Where Is it Wednesday edition, we will take a look at how to create a chart showing series with different orders of magnitude. Episode 888 shows you how.
Transcript of the video:
Hey, alright, welcome back to the Mrexcel netcast, I'm Bill Jelen.
Great question sent in by Rick.
Now, Rick wants to create a chart in Excel 2007 that was relatively easy to create back in Excel 2003.
In Excel 2003, in step one of the wizard, you'd go to Custom Types and all the way at the bottom was something called Line - Column on 2 Axes.
And this was a great chart for showing when you had different orders of magnitude.
A little bit harder to do in Excel 2007.
So here's Rick's data set.
Now, first thing you'll notice is that, really what we want to do is have Months, the Kilowatt Hours, and Occupancy percentage on the chart.
But I'm going to create a chart with everything-- Year and Cost-- and then just get rid of the extra stuff.
So we go to the Insert tab and just choose a regular 2d column.
And initially, what you see is the Kilowatt Hours are showing up, and nothing else is showing up.
So we're going to go, not to the Design tab, but over to the Layout tab.
And here, we can select those individual series.
So I'll go to Series "YEAR", and then click Delete.
That gets that off the chart.
And then I'll go to Series "COST" and click Delete, and that gets that off the chart.
Now, I'm down to just two series.
Here's the important part: So we're going to go to Series Occupancy Percentage (Series "OCC%"), and then, either press Ctrl+1 or click Format Selection.
On Format Selection we're going to go to the Secondary Axis.
So what that's going to do is put the Kilowatt Hours along the left axis-- those are in hundreds of thousands-- and then the Occupancy Percentage along the right axis-- which are numbers from zero to ninety percent.
In this case though, and actually in every case, when you do this, as soon as you put something on the second series, Microsoft decides to plot both items directly on top of each other.
So here, you can only see a couple of months where the Kilowatt Hours are above the Occupancy Percentage; everywhere else, we can't see any of the data.
So what we have to do is change one of these chart types to something else.
I always use a line chart.
So right now, I still have Series Occupancy Percentage selected.
I'll go back to the Design tab and choose Change Chart Type, and choose, maybe, a Line with points, click OK.
Alright.
And now, I have a chart that shows both Kilowatt Hours in hundreds of thousands, and the Occupancy Percentage in percentages.
I'll usually go through and choose this series of numbers over here on the right-hand side, and then go back to the Home tab and choose a different font color-- a font color to match the color of the line, so we'll use that one there just to kind of give someone a clue that that purple line refers to the purple numbers along the right-hand side.
So there you have it, great way to show two different orders of magnitude on the same chart.
A little bit more difficult in Excel 2007, but certainly a doable process by isolating that that second series and then changing to the secondary axis, and then finally, changing the chart type to some other kind of chart type.
Well, I want to thank you for stopping by, we'll see you next time for another netcast from MrExcel.
Great question sent in by Rick.
Now, Rick wants to create a chart in Excel 2007 that was relatively easy to create back in Excel 2003.
In Excel 2003, in step one of the wizard, you'd go to Custom Types and all the way at the bottom was something called Line - Column on 2 Axes.
And this was a great chart for showing when you had different orders of magnitude.
A little bit harder to do in Excel 2007.
So here's Rick's data set.
Now, first thing you'll notice is that, really what we want to do is have Months, the Kilowatt Hours, and Occupancy percentage on the chart.
But I'm going to create a chart with everything-- Year and Cost-- and then just get rid of the extra stuff.
So we go to the Insert tab and just choose a regular 2d column.
And initially, what you see is the Kilowatt Hours are showing up, and nothing else is showing up.
So we're going to go, not to the Design tab, but over to the Layout tab.
And here, we can select those individual series.
So I'll go to Series "YEAR", and then click Delete.
That gets that off the chart.
And then I'll go to Series "COST" and click Delete, and that gets that off the chart.
Now, I'm down to just two series.
Here's the important part: So we're going to go to Series Occupancy Percentage (Series "OCC%"), and then, either press Ctrl+1 or click Format Selection.
On Format Selection we're going to go to the Secondary Axis.
So what that's going to do is put the Kilowatt Hours along the left axis-- those are in hundreds of thousands-- and then the Occupancy Percentage along the right axis-- which are numbers from zero to ninety percent.
In this case though, and actually in every case, when you do this, as soon as you put something on the second series, Microsoft decides to plot both items directly on top of each other.
So here, you can only see a couple of months where the Kilowatt Hours are above the Occupancy Percentage; everywhere else, we can't see any of the data.
So what we have to do is change one of these chart types to something else.
I always use a line chart.
So right now, I still have Series Occupancy Percentage selected.
I'll go back to the Design tab and choose Change Chart Type, and choose, maybe, a Line with points, click OK.
Alright.
And now, I have a chart that shows both Kilowatt Hours in hundreds of thousands, and the Occupancy Percentage in percentages.
I'll usually go through and choose this series of numbers over here on the right-hand side, and then go back to the Home tab and choose a different font color-- a font color to match the color of the line, so we'll use that one there just to kind of give someone a clue that that purple line refers to the purple numbers along the right-hand side.
So there you have it, great way to show two different orders of magnitude on the same chart.
A little bit more difficult in Excel 2007, but certainly a doable process by isolating that that second series and then changing to the secondary axis, and then finally, changing the chart type to some other kind of chart type.
Well, I want to thank you for stopping by, we'll see you next time for another netcast from MrExcel.
