How to highlight part of a line chart using a second area chart series. This trick works in Excel 2007, but not 2003. Episode 1016 shows you how.
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, we start out with massive amount of data.
How we're going to analyze this as well as file up a pivot table.
See if we solve this problem.
Yeah, alright! Welcome back to the MrExcel netcast.
I'm Bill Jelen.
Ron from Australia send in. This question is actually talking about some 1,000 forces, wait a second.
When I try to build an area chart with certain items blank or N/A.
It doesn't seem to be working and I said Ron I know that this works because I wrote about it in my charting book, and I went back and sure enough.
Figure 3.34, shows how this works.
So, here I'm in Excel 2003.
Temporarily, I'm going to do this exact same trick that the book talks about.
Where I choose series 2 and change the chart type to an area chart and look at that look, how it takes from May zero and then draws up to the June.
Completely wrong in fact.
Let me show you how cool this looks in Excel 2007 and so unfortunately the answer for Ron is going to be well it just doesn't work in Excel 2003 but it does work in Excel 2007.
I'm going to build a line chart first and we'll build a 3D line and you see if there's something unusual here.
There was highway construction in June, July and August.
So, I want to highlight that section of the chart.
So, here's what we're going to do, when I get rid of that legend and then here for June July and August.
I want to build a formula that pulls that value over.
So, just those three months and I choose the chart grab the Blue outline, to extend the data that's on the chart and I get a second series.
And what you see initially happens is that just that section is highlighted in red but I want to choose that series.
So, I come here to the layout tab and choose series 2 and then change the chart type.
For that series, I'm going to change it back to the first area chart.
So, scroll down to area choose that click [ ok ].
Now, look at that it's beautiful here in 2007.
It draws straight vertical lines, which allows us to highlight that section of the line.
Now for this particular case, how that red seems just a little bit too dramatic for me.
So, I'm going to go into shape fill.
Of course, I have to select series 2 before I do that shape fill and choose a nice light gray, maybe right there.
Alright! Good. So now we've highlighted that section of the chart, and then of course it finishes out.
We want to add a nice title.
So, chart title, above chart sales by month and I'll press [ enter ].
All right! Now, let's come back in and edit this, we'll press [ enter ] to go to a new line and say, "Highway construction in the summer hurt Sales, and this is pretty cool, I'm going to choose just those characters and go back to the home tab, change the font size of those characters, basically creating a subtitle.
I want to make that whole thing left justified and so now, we have a nice title and subtitle on the chart with those items in the subtitle, the summer months being called out by a second series.
Now, as Ron pointed out this does not work well in Excel 2003.
When we have an area chart and it goes from nothing to a number, they draw the diagonal lines in does not look as good at all.
So, there you have it, something for those of you who have upgraded to Excel 2007.
Great way to highlight one section of the line chart.
Well, I want to thank you for stopping by, we'll see you next time for another net cast from MrExcel.
Thanks for stopping by, we'll see you next time for another from MrExcel.
I'm Bill Jelen.
Basically, we start out with massive amount of data.
How we're going to analyze this as well as file up a pivot table.
See if we solve this problem.
Yeah, alright! Welcome back to the MrExcel netcast.
I'm Bill Jelen.
Ron from Australia send in. This question is actually talking about some 1,000 forces, wait a second.
When I try to build an area chart with certain items blank or N/A.
It doesn't seem to be working and I said Ron I know that this works because I wrote about it in my charting book, and I went back and sure enough.
Figure 3.34, shows how this works.
So, here I'm in Excel 2003.
Temporarily, I'm going to do this exact same trick that the book talks about.
Where I choose series 2 and change the chart type to an area chart and look at that look, how it takes from May zero and then draws up to the June.
Completely wrong in fact.
Let me show you how cool this looks in Excel 2007 and so unfortunately the answer for Ron is going to be well it just doesn't work in Excel 2003 but it does work in Excel 2007.
I'm going to build a line chart first and we'll build a 3D line and you see if there's something unusual here.
There was highway construction in June, July and August.
So, I want to highlight that section of the chart.
So, here's what we're going to do, when I get rid of that legend and then here for June July and August.
I want to build a formula that pulls that value over.
So, just those three months and I choose the chart grab the Blue outline, to extend the data that's on the chart and I get a second series.
And what you see initially happens is that just that section is highlighted in red but I want to choose that series.
So, I come here to the layout tab and choose series 2 and then change the chart type.
For that series, I'm going to change it back to the first area chart.
So, scroll down to area choose that click [ ok ].
Now, look at that it's beautiful here in 2007.
It draws straight vertical lines, which allows us to highlight that section of the line.
Now for this particular case, how that red seems just a little bit too dramatic for me.
So, I'm going to go into shape fill.
Of course, I have to select series 2 before I do that shape fill and choose a nice light gray, maybe right there.
Alright! Good. So now we've highlighted that section of the chart, and then of course it finishes out.
We want to add a nice title.
So, chart title, above chart sales by month and I'll press [ enter ].
All right! Now, let's come back in and edit this, we'll press [ enter ] to go to a new line and say, "Highway construction in the summer hurt Sales, and this is pretty cool, I'm going to choose just those characters and go back to the home tab, change the font size of those characters, basically creating a subtitle.
I want to make that whole thing left justified and so now, we have a nice title and subtitle on the chart with those items in the subtitle, the summer months being called out by a second series.
Now, as Ron pointed out this does not work well in Excel 2003.
When we have an area chart and it goes from nothing to a number, they draw the diagonal lines in does not look as good at all.
So, there you have it, something for those of you who have upgraded to Excel 2007.
Great way to highlight one section of the line chart.
Well, I want to thank you for stopping by, we'll see you next time for another net cast from MrExcel.
Thanks for stopping by, we'll see you next time for another from MrExcel.
