How to create a combination chart that includes both stacked columns and a line chart. Episode 442 shows you how.
This blog is the video netcast companion to the new book, Excel 2007 Miracles Made Easy. Download a new two minute video every Tuesday and Thursday to learn one of the tips from the book!
This blog is the video netcast companion to the new book, Excel 2007 Miracles Made Easy. Download a new two minute video every Tuesday and Thursday to learn one of the tips from the book!
Transcript of the video:
Hey! Welcome back to the MrExcel netcast.
I'm Bill Jelen.
Today, we have a question sent in, by Eric.
Eric's trying to build a chart in Excel 2003.
And he's gone back here to the Custom Types tab and chosen Line Column on 2 Axes.
He noticed, that the column version of that cannot be, a stacked Column.
Wonders, what he can do?
This is a great question because I've been writing a book about Charts and Graphs for Excel 2003.
That book will be out in about four to six weeks and in the process of writing that book. Discovered some really cool turning tricks.
Eric! rather than using the Custom types tab.
I want you to go back and just create a standard chart and I'll show you, how to edit that chart, to create the effect, that you're trying to do.
First thing, we want to do is, select a stacked Column Chart.
Since most of the series are end up a stack column.
Let's start with that kind of a chart and I'll click [ Finish ].
Now, what I have here is, I have four series in a stacked column chart and you said that you wanted one of those series, to be a line and to be on the secondary axis.
Let's assume it's this fourth series.
The series on top.
I'm going to click that series once and you'll see that, I've selected the entire series.
I have a selection dot in each of those bars.
Now, that series is selected.
I can go to my Charting Toolbar.
Open the Chart Type and change the Chart Type of that one series to a line chart.
Now, it's a really faint line that you can barely see.
If I right click on the line and choose format data series, I can choose a darker color.
And we also need to change the color of the markers.
Click [ ok ].
Here we go, a little bit better you can see it now.
Now, the other thing that Eric wanted to do is to have this line beyond the secondary axis.
This is usually good if one of the series has a different order of magnitude than the other series.
What we'll do, again right-click the line and choose format data series.
This time go to the second tab, the axis Tab and say that it should be on the secondary axis, click [ OK ].
And now, this line is plotted against the numbers on the right axis.
Eric! Great question, thanks for sending in.
If you have a question to send in.
Many different ways to get it here.
If you want to leave a voicemail.
You can call me at, 8665810221.
We also have our, New London number there, or if you're on Skype.
You can use my Skype voice mail.
Sending a Skype, to Bill Jelen or just send me an E-mail.
Thanks for stopping by.
I will see you tomorrow for another netcast, from MrExcel.
I'm Bill Jelen.
Today, we have a question sent in, by Eric.
Eric's trying to build a chart in Excel 2003.
And he's gone back here to the Custom Types tab and chosen Line Column on 2 Axes.
He noticed, that the column version of that cannot be, a stacked Column.
Wonders, what he can do?
This is a great question because I've been writing a book about Charts and Graphs for Excel 2003.
That book will be out in about four to six weeks and in the process of writing that book. Discovered some really cool turning tricks.
Eric! rather than using the Custom types tab.
I want you to go back and just create a standard chart and I'll show you, how to edit that chart, to create the effect, that you're trying to do.
First thing, we want to do is, select a stacked Column Chart.
Since most of the series are end up a stack column.
Let's start with that kind of a chart and I'll click [ Finish ].
Now, what I have here is, I have four series in a stacked column chart and you said that you wanted one of those series, to be a line and to be on the secondary axis.
Let's assume it's this fourth series.
The series on top.
I'm going to click that series once and you'll see that, I've selected the entire series.
I have a selection dot in each of those bars.
Now, that series is selected.
I can go to my Charting Toolbar.
Open the Chart Type and change the Chart Type of that one series to a line chart.
Now, it's a really faint line that you can barely see.
If I right click on the line and choose format data series, I can choose a darker color.
And we also need to change the color of the markers.
Click [ ok ].
Here we go, a little bit better you can see it now.
Now, the other thing that Eric wanted to do is to have this line beyond the secondary axis.
This is usually good if one of the series has a different order of magnitude than the other series.
What we'll do, again right-click the line and choose format data series.
This time go to the second tab, the axis Tab and say that it should be on the secondary axis, click [ OK ].
And now, this line is plotted against the numbers on the right axis.
Eric! Great question, thanks for sending in.
If you have a question to send in.
Many different ways to get it here.
If you want to leave a voicemail.
You can call me at, 8665810221.
We also have our, New London number there, or if you're on Skype.
You can use my Skype voice mail.
Sending a Skype, to Bill Jelen or just send me an E-mail.
Thanks for stopping by.
I will see you tomorrow for another netcast, from MrExcel.
