Another Dueling Excel podcast: How to best represent data on a chart. Mike and Bill offer their favorite solutions in Episode 985.
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! I'm Bill Jelen from MrExcel.com.
I've got a cool excel tip for you, today.
Hey, this is Mike Gel Girvin from Excel Is Fun on YouTube and I have a different way to do that.
Hey! We have another dueling Excel podcast.
I'm Bill Jelen from MrExcel.
I want to send a shout out to Joe with the Garvey Nut and Candy Company.
Joe was talking about six months ago about a project, what we gonna for Joe.
He says, "Hey!I love your videos at itunes." Like yeah, right, okay?
And then he says but you have to check out this guy Excel Is Fun at YouTube.
He's even more enthusiastic than you are and I'm like what?
And Joe says he has hundreds of videos.
What?
So, now for those of you following the netcast of course you know that Mike and I have met, we did a couple of podcasts together.
Now, this is our second dueling podcast but it hadn't been for Joe who kind of got my competitive spirit up there.
So, this netcast is definitely showing some of my competitive nature.
I said Mike we should show people how to create charts, we have a couple of datasets.
Let's go through and see who has more Excel videos and so you know, as obviously if you're watching this podcast, you know about the learn excel for MrExcel podcast.
This is episode number 885 but I also have other products at the live lessons for Excel 2007 and Excel 2003.
There's 150 videos there, the total training DVD, the advanced Excel 2007.
Another 60 videos there.
The new high def podcast, 40 videos there and soon to come out the office academy with seven pivot table videos there, for a total of 1152 and I knew that Mike had hundreds of videos at YouTube it turns out 635 but Mike is a professor Highline college in Seattle and he's doing videos for his students.
So, for each one of his classes there's more and more videos when we added all these up and obviously you can tell that I put it on the podcast.
I'm winning slightly, hooray! For me.
But the real point here is to take these two data sets and see how we can show them graphically.
So, I'm going to send this over to Mike.
Mike's going to put together a chart and then we'll come back and I'll put together chart to see which one's better.
Mike! Take it away.
Mike: Hey! Thanks MrExcel.
Hey! I have the same data set here.
The video count for MrExcel, tele-video account for Excel Is Fun.
Now if I'm going to compare these numbers to these.
I want to rearrange them over here.
So, the next to each other not only that but since I want to emphasize the difference.
I'm going to use a horizontal trying to use the bar chart.
Let's come over here and get our data equals this label which will be the data series label, and then I'm going to point to this and click and drag.
Notice, the data sign on top of each other.
I did the same thing for here.
Now, we have our data set up that will work nicely for two bars, right next to each other.
These labels up here will show up in our legend which we can use as our chart title.
Now, everything's set up the only trick is we need a label here.
And that chart, the bar chart is going to assume that we want one label for these two data points but we want two.
So, I'm going to build a formula here that will create two labels one on top of each other.
When I click there, use the ampersand (&), [ shift + 7 ] which means a join, and I'm going to use the character function.
Hey!
What's the character function?
It returns an ASCII character if you put in a code, for example 65 is the letter A and 10 is hard return.
So, no way we can have a hard return built right into our formula.
Finally, we ampersand (&) [ shift + 7 ] and get our other label.
[ Control + enter ] and double click and send it down.
I'm gonna have to come over here and adjust that.
Now, we have our labels just as we want them, one on top of each other.
Highlight this, go up to insert.
I'm gonna go to bar and select this one.
Now, let's look at these lined up perfectly but look there's a problem.
MrExcel has the little and Excel Is Fun has the big one.
That is not right.
So, I'm gonna go over here click here and [ F2 ], this one needs to be A11 and this one needs to B2.
[ Ctrl + enter ].
Now, we have them right.
The itunes has the big bar.
I'm going to double click and send that down, adjust all those labels.
We'll do a few last quick things right click, format.
Legend will show at the top.
Maybe change the font size to 16 and I'm going to scoop this down a little bit and let's add some data labels, right click add data labels.
You could also click on the bar.
Go to layout data labels and then outside Fellows, we don't need these lines and look at this.
This is chart junk if we have the numbers there.
Let's get rid of that right there, and so there we have it.
There's a nice presentation for the differences between two sets of data.
Alright! I'm going to throw it back to MrExcel.
MrExcel: Hey all right! Mike. That's great.
When I look at this I say all right well the components are mildly interesting.
But what's really important is the total number.
So, I want to have a chart that's going to allow us to compare the total number of Bill and Mike and so.
Just like Mike, I had to rearrange the data.
I kind look to this and said okay. Let's see what categories are similar?
We have podcasts, mine on iTunes, Mike's on YouTube and then DVD courses that's the live lessons and total training and then all of Mike's college videos and then the new category and I put this one separate because I'm trying to convince Mike we should start doing more online videos for the office academy and so it's going to be really small not even show up in the chart.
But basically I have this data set and I'm going to go through and create a stacked column chart.
So, insert column and I want to do the stacked column, not the 100% stacked.
There's all kinds of problems with that.
So, we're going to a stacked column and initially Excel gets the data wrong.
So, we want to switch that data much easier now, in Excel 2007.
We come here to switch roll call and we end up with two columns.
Bill column and Mike column.
Now, to put these a little bit closer to each other.
I'm going to right click format data series and then go to the gap and say that I want a very little gap that's going to get us closer.
Want to move the legend up to the top that's back here on the layout tab.
Legend, show legend at the top always, want to share the legend either at the top or the left.
Helps people who are reading the chart and maybe we don't need to make everything quite as wide.
So, we'll make it a little bit more narrow.
So, that's how I would go about it, and you know as I look at this chart.
I'm comfortable, and I say all right.
I'm ahead of Mike but I need to warn you here that, in this case.
This is, you know this happens a lot with companies.
They say, all right well we want to see how we're doing against the competition?
So, they dream up some measure create a nice chart from that measure and it gives everyone a warm fuzzy feeling that things are going well.
But as I started to think about this I mean Mike said to me that he saw my videos first.
Gave him an idea, and he started doing videos, and I think that's great that he's out there doing videos.
But then I started wonder about velocity and acceleration.
So, I took this data, and I created it in a completely different format.
I started to look at the number of videos that have been produced month by month and an accumulated number of videos.
So, I've started back in October, 2005.
I was rolling along to, one a day, every weekday, and you know things were going along well and then Mike really just started doing his videos in June of 2008.
So, he has been cranking to get that close and so when we chart these two series mine in blue and Mike in red, you see yeah!
I am ahead.
But I need to worry if my competitive nature matters at all because Mike clearly is on a very fast trend-line to pass me.
Now, a cool thing. We can do here.
You'll see that, I added extra months beyond where I had data that gives me stuff to the right of the chart and I'm going to right click, both lines right click and say add trend-line and we'll just let it be a linear trend-line click [ close ] and Then I'll do the same thing for mine, add trend-line.
Again, a linear trend-line, and we'll click [ close ] and what we see here is that sure enough, this is predicting somewhere around July 2009.
Mike is going to surpass me.
So, I should enjoy my lead, right now.
I know that clearly Mike is on a trend to completely beat me there you go, all right well!
Hey! It's devil, different cool charting tricks.
Hope you found this useful.
Wanna thank you for stopping by, we'll see you next time for another netcast from MrExcel and Excel Is Fun.
I've got a cool excel tip for you, today.
Hey, this is Mike Gel Girvin from Excel Is Fun on YouTube and I have a different way to do that.
Hey! We have another dueling Excel podcast.
I'm Bill Jelen from MrExcel.
I want to send a shout out to Joe with the Garvey Nut and Candy Company.
Joe was talking about six months ago about a project, what we gonna for Joe.
He says, "Hey!I love your videos at itunes." Like yeah, right, okay?
And then he says but you have to check out this guy Excel Is Fun at YouTube.
He's even more enthusiastic than you are and I'm like what?
And Joe says he has hundreds of videos.
What?
So, now for those of you following the netcast of course you know that Mike and I have met, we did a couple of podcasts together.
Now, this is our second dueling podcast but it hadn't been for Joe who kind of got my competitive spirit up there.
So, this netcast is definitely showing some of my competitive nature.
I said Mike we should show people how to create charts, we have a couple of datasets.
Let's go through and see who has more Excel videos and so you know, as obviously if you're watching this podcast, you know about the learn excel for MrExcel podcast.
This is episode number 885 but I also have other products at the live lessons for Excel 2007 and Excel 2003.
There's 150 videos there, the total training DVD, the advanced Excel 2007.
Another 60 videos there.
The new high def podcast, 40 videos there and soon to come out the office academy with seven pivot table videos there, for a total of 1152 and I knew that Mike had hundreds of videos at YouTube it turns out 635 but Mike is a professor Highline college in Seattle and he's doing videos for his students.
So, for each one of his classes there's more and more videos when we added all these up and obviously you can tell that I put it on the podcast.
I'm winning slightly, hooray! For me.
But the real point here is to take these two data sets and see how we can show them graphically.
So, I'm going to send this over to Mike.
Mike's going to put together a chart and then we'll come back and I'll put together chart to see which one's better.
Mike! Take it away.
Mike: Hey! Thanks MrExcel.
Hey! I have the same data set here.
The video count for MrExcel, tele-video account for Excel Is Fun.
Now if I'm going to compare these numbers to these.
I want to rearrange them over here.
So, the next to each other not only that but since I want to emphasize the difference.
I'm going to use a horizontal trying to use the bar chart.
Let's come over here and get our data equals this label which will be the data series label, and then I'm going to point to this and click and drag.
Notice, the data sign on top of each other.
I did the same thing for here.
Now, we have our data set up that will work nicely for two bars, right next to each other.
These labels up here will show up in our legend which we can use as our chart title.
Now, everything's set up the only trick is we need a label here.
And that chart, the bar chart is going to assume that we want one label for these two data points but we want two.
So, I'm going to build a formula here that will create two labels one on top of each other.
When I click there, use the ampersand (&), [ shift + 7 ] which means a join, and I'm going to use the character function.
Hey!
What's the character function?
It returns an ASCII character if you put in a code, for example 65 is the letter A and 10 is hard return.
So, no way we can have a hard return built right into our formula.
Finally, we ampersand (&) [ shift + 7 ] and get our other label.
[ Control + enter ] and double click and send it down.
I'm gonna have to come over here and adjust that.
Now, we have our labels just as we want them, one on top of each other.
Highlight this, go up to insert.
I'm gonna go to bar and select this one.
Now, let's look at these lined up perfectly but look there's a problem.
MrExcel has the little and Excel Is Fun has the big one.
That is not right.
So, I'm gonna go over here click here and [ F2 ], this one needs to be A11 and this one needs to B2.
[ Ctrl + enter ].
Now, we have them right.
The itunes has the big bar.
I'm going to double click and send that down, adjust all those labels.
We'll do a few last quick things right click, format.
Legend will show at the top.
Maybe change the font size to 16 and I'm going to scoop this down a little bit and let's add some data labels, right click add data labels.
You could also click on the bar.
Go to layout data labels and then outside Fellows, we don't need these lines and look at this.
This is chart junk if we have the numbers there.
Let's get rid of that right there, and so there we have it.
There's a nice presentation for the differences between two sets of data.
Alright! I'm going to throw it back to MrExcel.
MrExcel: Hey all right! Mike. That's great.
When I look at this I say all right well the components are mildly interesting.
But what's really important is the total number.
So, I want to have a chart that's going to allow us to compare the total number of Bill and Mike and so.
Just like Mike, I had to rearrange the data.
I kind look to this and said okay. Let's see what categories are similar?
We have podcasts, mine on iTunes, Mike's on YouTube and then DVD courses that's the live lessons and total training and then all of Mike's college videos and then the new category and I put this one separate because I'm trying to convince Mike we should start doing more online videos for the office academy and so it's going to be really small not even show up in the chart.
But basically I have this data set and I'm going to go through and create a stacked column chart.
So, insert column and I want to do the stacked column, not the 100% stacked.
There's all kinds of problems with that.
So, we're going to a stacked column and initially Excel gets the data wrong.
So, we want to switch that data much easier now, in Excel 2007.
We come here to switch roll call and we end up with two columns.
Bill column and Mike column.
Now, to put these a little bit closer to each other.
I'm going to right click format data series and then go to the gap and say that I want a very little gap that's going to get us closer.
Want to move the legend up to the top that's back here on the layout tab.
Legend, show legend at the top always, want to share the legend either at the top or the left.
Helps people who are reading the chart and maybe we don't need to make everything quite as wide.
So, we'll make it a little bit more narrow.
So, that's how I would go about it, and you know as I look at this chart.
I'm comfortable, and I say all right.
I'm ahead of Mike but I need to warn you here that, in this case.
This is, you know this happens a lot with companies.
They say, all right well we want to see how we're doing against the competition?
So, they dream up some measure create a nice chart from that measure and it gives everyone a warm fuzzy feeling that things are going well.
But as I started to think about this I mean Mike said to me that he saw my videos first.
Gave him an idea, and he started doing videos, and I think that's great that he's out there doing videos.
But then I started wonder about velocity and acceleration.
So, I took this data, and I created it in a completely different format.
I started to look at the number of videos that have been produced month by month and an accumulated number of videos.
So, I've started back in October, 2005.
I was rolling along to, one a day, every weekday, and you know things were going along well and then Mike really just started doing his videos in June of 2008.
So, he has been cranking to get that close and so when we chart these two series mine in blue and Mike in red, you see yeah!
I am ahead.
But I need to worry if my competitive nature matters at all because Mike clearly is on a very fast trend-line to pass me.
Now, a cool thing. We can do here.
You'll see that, I added extra months beyond where I had data that gives me stuff to the right of the chart and I'm going to right click, both lines right click and say add trend-line and we'll just let it be a linear trend-line click [ close ] and Then I'll do the same thing for mine, add trend-line.
Again, a linear trend-line, and we'll click [ close ] and what we see here is that sure enough, this is predicting somewhere around July 2009.
Mike is going to surpass me.
So, I should enjoy my lead, right now.
I know that clearly Mike is on a trend to completely beat me there you go, all right well!
Hey! It's devil, different cool charting tricks.
Hope you found this useful.
Wanna thank you for stopping by, we'll see you next time for another netcast from MrExcel and Excel Is Fun.
