The Basics of Excel Charting: Part I
This is the first of a Four (4) Part series on the Basics of Charting with Microsoft Excel 2010. Today, in Episode #1405, Bill will begin by showing us the criteria for the Data Set we want to create our Chart with. By taking just words in a Microsoft Word Document, Bill will then proceed with the steps of Best Practice to get the Data from Word set up to create our Chart in Excel. Follow along to learn - from the ground up - how to proficiently Chart in Microsoft Excel 2010!
...This episode is the video podcast companion to the book, "Charts And Graphs: Microsoft Excel 2010", by Bill Jelen a.k.a. MrExcel.
For more Excel 2010 knowledge, check out "Excel 2010 In Depth", by Bill Jelen a.k.a. MrExcel.
This is the first of a Four (4) Part series on the Basics of Charting with Microsoft Excel 2010. Today, in Episode #1405, Bill will begin by showing us the criteria for the Data Set we want to create our Chart with. By taking just words in a Microsoft Word Document, Bill will then proceed with the steps of Best Practice to get the Data from Word set up to create our Chart in Excel. Follow along to learn - from the ground up - how to proficiently Chart in Microsoft Excel 2010!
...This episode is the video podcast companion to the book, "Charts And Graphs: Microsoft Excel 2010", by Bill Jelen a.k.a. MrExcel.
For more Excel 2010 knowledge, check out "Excel 2010 In Depth", by Bill Jelen a.k.a. MrExcel.
Transcript of the video:
This week's MrExcel netcast is sponsored by Charts and Graphs.
Microsoft Excel 2010 from Q.
This is episode 1405.
Setup Data For Charting.
Hey! Well, welcome back to the MrExcel netcast.
I'm Bill Jelen.
Right here something little bit different, I want to do, a four part podcast.
This is part one, the basics of Excel charting.
Now, I got this data from some of my hometown.
It said, "Hey! I need, never set up a chart in Excel before.
Can you walk me through how to set up a chart?" And I said, "Yeah! Sure send me the data." So, the data came in Microsoft Word.
You know, Word that's that add-in for people who aren't comfortable in Excel and I looked at the data I said okay!
It seems to be that they're looking at, you know the difference if someone scored well in this in the power Excel class.
How many people were there, one year later versus not there.
Alright! So, what I did is I said well, we probably want to have look at it over the over time.
So, I set 2006 -2007, 2007- 2008 and 2008- 2009.
So, that kind of gives us a trim line.
That we're going to look at and then what we're interested in is, we're interested in the percentage of people.
You know, like all employees and then employees who got greater than or equal to 50.
All right! Now, this is the first from right here that greater than or equal to sign is not, is not on my keyboard and some people know that that's greater than or equal to but that's gonna look really bad in the chart.
So, I need to get a real character.
Now, there I'm gonna go to insert symbol.
Alright! And they always start with wing-dings, that's really not what we want to do.
We want to go back to the font that you're using and boy I didn't even bother to check to see what it was, the font that I'm using is calibri of course, what else would it be.
Okay! So, employees, this is where I want the greater than or equal to, click the symbol.
Alright! And then we scroll down looking for some mathematical symbols.
Now, I actually set this up there, today.
So, it's here my recently used but you're gonna have to scroll down.
So, you get into kind of math symbols, there's this to me.
Let's go through this little set here and we'll do mathematical operators.
Good that arrows are down, it's right there.
You can click and then click insert.
Just double click and then close.
Greater than, B in power Excel.
Alright! So, we have those too.
Now, they actually have the percentages here for us but you know, I don't know why, I just don't trust those percentages.
Let's do number of employees, number retained and then this is greater than 80, A greater than B employees and greater than B retained.
All right! Over here, number of employees 2006-2007, there were 493 employees.
That's from right there and 411 were retained.
379 and then 337 same thing and I won't make you watch this was just going to type the numbers from down here.
419, okay! So, everything's typed.
Now, to build a little formula, type an equal sign and we want to see that 411 divided by 463.
Format that as a percentage back here on the home tab and they want with zero decimal places, I'll go zero decimal places.
Beautiful thing about this is we can copy this down and see the 83, 79, 79.
Alright! So that's all looking good and then the other formula is...
Notice, I use a plus sign works just as well divided by C2.
Let's again format that as a percentage.
Copy down, Okay! So, let's just take a little breath here and and realize what's going on.
Down the left-hand side of my chart range.
First of all this is going to be my chart range, right here.
Everything in yellow, down the left-hand side I have the labels that I want to appear across the bottom of the chart, showing up down there at the bottom.
Across the top I have the two things.
I'm comparing, in other words those gonna be the labels that appear in the legend.
I have one series for each of those, okay!
So, I noticed that you might have wanted to put the years over here but I wanted to put the years next to the data.
That just makes life easier, yes we could use the ctrl key to select non-contiguous ranges.
But if you're new to charting, there's a good chance that selecting non-contiguous ranges, it's gonna make your head spin.
So, I don't want to say the word non-contiguous, not gonna say that.
I'll scare you away.
All right! So, we have this data here.
All right! Now, come back tomorrow, episode 1406.
We'll take a look at creating that chart but we won't be done then.
All right! Hey thanks for stopping by, we'll see you next time for another netcast from MrExcel.
Microsoft Excel 2010 from Q.
This is episode 1405.
Setup Data For Charting.
Hey! Well, welcome back to the MrExcel netcast.
I'm Bill Jelen.
Right here something little bit different, I want to do, a four part podcast.
This is part one, the basics of Excel charting.
Now, I got this data from some of my hometown.
It said, "Hey! I need, never set up a chart in Excel before.
Can you walk me through how to set up a chart?" And I said, "Yeah! Sure send me the data." So, the data came in Microsoft Word.
You know, Word that's that add-in for people who aren't comfortable in Excel and I looked at the data I said okay!
It seems to be that they're looking at, you know the difference if someone scored well in this in the power Excel class.
How many people were there, one year later versus not there.
Alright! So, what I did is I said well, we probably want to have look at it over the over time.
So, I set 2006 -2007, 2007- 2008 and 2008- 2009.
So, that kind of gives us a trim line.
That we're going to look at and then what we're interested in is, we're interested in the percentage of people.
You know, like all employees and then employees who got greater than or equal to 50.
All right! Now, this is the first from right here that greater than or equal to sign is not, is not on my keyboard and some people know that that's greater than or equal to but that's gonna look really bad in the chart.
So, I need to get a real character.
Now, there I'm gonna go to insert symbol.
Alright! And they always start with wing-dings, that's really not what we want to do.
We want to go back to the font that you're using and boy I didn't even bother to check to see what it was, the font that I'm using is calibri of course, what else would it be.
Okay! So, employees, this is where I want the greater than or equal to, click the symbol.
Alright! And then we scroll down looking for some mathematical symbols.
Now, I actually set this up there, today.
So, it's here my recently used but you're gonna have to scroll down.
So, you get into kind of math symbols, there's this to me.
Let's go through this little set here and we'll do mathematical operators.
Good that arrows are down, it's right there.
You can click and then click insert.
Just double click and then close.
Greater than, B in power Excel.
Alright! So, we have those too.
Now, they actually have the percentages here for us but you know, I don't know why, I just don't trust those percentages.
Let's do number of employees, number retained and then this is greater than 80, A greater than B employees and greater than B retained.
All right! Over here, number of employees 2006-2007, there were 493 employees.
That's from right there and 411 were retained.
379 and then 337 same thing and I won't make you watch this was just going to type the numbers from down here.
419, okay! So, everything's typed.
Now, to build a little formula, type an equal sign and we want to see that 411 divided by 463.
Format that as a percentage back here on the home tab and they want with zero decimal places, I'll go zero decimal places.
Beautiful thing about this is we can copy this down and see the 83, 79, 79.
Alright! So that's all looking good and then the other formula is...
Notice, I use a plus sign works just as well divided by C2.
Let's again format that as a percentage.
Copy down, Okay! So, let's just take a little breath here and and realize what's going on.
Down the left-hand side of my chart range.
First of all this is going to be my chart range, right here.
Everything in yellow, down the left-hand side I have the labels that I want to appear across the bottom of the chart, showing up down there at the bottom.
Across the top I have the two things.
I'm comparing, in other words those gonna be the labels that appear in the legend.
I have one series for each of those, okay!
So, I noticed that you might have wanted to put the years over here but I wanted to put the years next to the data.
That just makes life easier, yes we could use the ctrl key to select non-contiguous ranges.
But if you're new to charting, there's a good chance that selecting non-contiguous ranges, it's gonna make your head spin.
So, I don't want to say the word non-contiguous, not gonna say that.
I'll scare you away.
All right! So, we have this data here.
All right! Now, come back tomorrow, episode 1406.
We'll take a look at creating that chart but we won't be done then.
All right! Hey thanks for stopping by, we'll see you next time for another netcast from MrExcel.
