You need to chart two different orders of magnitude in a chart. I always end up creating a 2-axis combo chart, with a column series and a line series. Today in Episode 979, Bill writes in with a cool way to solve this problem with both series shown as columns.
This video is the podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
This video is the podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
Transcript of the video:
Hey! Welcome back to the MrExcel netcast.
I'm Bill Jelen.
Basically, you start out with massive amount of data.
How we're gonna analyze as well.
Let's fire up a pivot table.
See if we can solve this problem.
Welcome back to the MrExcel netcast.
I'm Bill Jelen.
Well, I may have mentioned that we started this weekly podcast on home, in office podcast and this is weird thing.
I just got an email about, one of the things I showed in the, "On home and office" podcast which of course, those were all recorded you know ahead of time.
So, there's nothing I can do about that.
So, let me discuss the problem.
I was talked on that podcast about when we have two different orders of magnitude in the same chart.
You see here that the water shows up, but the air doesn't show up at all.
So, my usual method is to come in to lay out and go into series water, format that and move it to the secondary axis that allows us to see both things, except Excel does.
In my opinion, what is a really stupid thing and putting both columns right on top of each other.
So, I'm always forced at that point to take one of the series and change it to a different chart type, like a line chart, to allow it to show through.
So, one of the viewers of that other podcast, Bill.
Bill wrote in he said, "Hey! I have a great solution for this, and I've never seen the solution before." Definitely the winner of an Excel master pin, Bill says, create a chart of four series.
So, here I'm going to take Bill's data, will copy it down below and take a look at how this works.
We're going to create a chart, column chart, click [ ok ].
Now, we're going to switch row and data.
So, you know we're back at the same spot, where we were before.
Air and water, we're seeing the water.
We're not seeing the air.
So, here's Bill's trick.
He's going to take the water and move it to the secondary axis, just like before.
So, we'll format secondary axis, click [ close ] but then he takes the second invisible series.
So, in this case is going to be series three and he also formats that, to move it to the secondary axis.
Click [ close ] and low behold, that gets both series one and series four, showing exactly like they should.
Now, let's take a look at Bill's chart, used a couple of nice items here.
He took the purple, the air and drew a little auto shape here, to point to the left.
Indicate that it's using the left axis and then an arrow pointing to the right for water also he went into a format tab here and under shape fill probably went into texture and found the nice water background there.
And then used purple for air really kind of dresses up that chart wonderfully.
So, I want to thank Bill for watching the other podcast and hopefully this podcast as well.
What a great idea for solving this long-standing problem of charting different orders of magnitude.
I want to thank you for stopping by, see you next time for another netcast from MrExcel.
Thanks for stopping by, we'll see you next time for another netcast from MrExcel.
I'm Bill Jelen.
Basically, you start out with massive amount of data.
How we're gonna analyze as well.
Let's fire up a pivot table.
See if we can solve this problem.
Welcome back to the MrExcel netcast.
I'm Bill Jelen.
Well, I may have mentioned that we started this weekly podcast on home, in office podcast and this is weird thing.
I just got an email about, one of the things I showed in the, "On home and office" podcast which of course, those were all recorded you know ahead of time.
So, there's nothing I can do about that.
So, let me discuss the problem.
I was talked on that podcast about when we have two different orders of magnitude in the same chart.
You see here that the water shows up, but the air doesn't show up at all.
So, my usual method is to come in to lay out and go into series water, format that and move it to the secondary axis that allows us to see both things, except Excel does.
In my opinion, what is a really stupid thing and putting both columns right on top of each other.
So, I'm always forced at that point to take one of the series and change it to a different chart type, like a line chart, to allow it to show through.
So, one of the viewers of that other podcast, Bill.
Bill wrote in he said, "Hey! I have a great solution for this, and I've never seen the solution before." Definitely the winner of an Excel master pin, Bill says, create a chart of four series.
So, here I'm going to take Bill's data, will copy it down below and take a look at how this works.
We're going to create a chart, column chart, click [ ok ].
Now, we're going to switch row and data.
So, you know we're back at the same spot, where we were before.
Air and water, we're seeing the water.
We're not seeing the air.
So, here's Bill's trick.
He's going to take the water and move it to the secondary axis, just like before.
So, we'll format secondary axis, click [ close ] but then he takes the second invisible series.
So, in this case is going to be series three and he also formats that, to move it to the secondary axis.
Click [ close ] and low behold, that gets both series one and series four, showing exactly like they should.
Now, let's take a look at Bill's chart, used a couple of nice items here.
He took the purple, the air and drew a little auto shape here, to point to the left.
Indicate that it's using the left axis and then an arrow pointing to the right for water also he went into a format tab here and under shape fill probably went into texture and found the nice water background there.
And then used purple for air really kind of dresses up that chart wonderfully.
So, I want to thank Bill for watching the other podcast and hopefully this podcast as well.
What a great idea for solving this long-standing problem of charting different orders of magnitude.
I want to thank you for stopping by, see you next time for another netcast from MrExcel.
Thanks for stopping by, we'll see you next time for another netcast from MrExcel.
