Excel Spreadsheet Refresher

So this video is to explain how to do graphs in Excel. And I already have some data here. It's area of Arctic sea ice, each number’s a different year. So I'm just gonna, it doesn't matter what your date is, but I'm just gonna select it here, just the numbers. And I'm gonna click on chart wizard. You can also do it under insert chart. But I'm gonna do it by clicking on this shortcut button up here, Chart Wizard. And then I'm gonna go to line, and I choose this one, and I click Next. And there, I'm done. This could be the end of the video. You click finish and you'd have that nice chart there. But there's a few more things we could do. Like this down here, we really want the year down here. So I'm gonna hit cancel and let's type “year”. And I forget when this data started, but it ended in 2006, and this one was 2005. And now Excel can determine the pattern. I can just click on this little right hand corner and drag, and it'll see the pattern and it'll continue it. So I just want to go up here already it's staying on 2004, right? 2003, right? and I say yeah. So I drag up here, let go, and there we have it We’ve got all our years perfectly. So Excel will look for a pattern. There's all kinds of patterns that Excel can recognize, and you just click on this thing in the corner and drag and it'll take care of that for you. And now this number here, 22,000, that's in pixels. I really want it in square kilometers and I happen to know that there’s 625 square kilometers per pixel. So what i'm doing here is i'm gonna say, Star a 2. So the equals means a formula’s coming up, the 625 means 625, the star means times and a2 means this cell over here. This is cell a2. Okay and there's the formula, and so it did it, did the multiplication. Oh, but it's 14 million, that's too big. So let's divide by a million. because that's just too big of a number for people's brains to deal with, too many digits and I'm gonna say million square kilometers. And there, 14, much better. So I'm gonna copy this, and I'm gonna paste it into all these cells down here. Paste. And there it looks great. And actually, I think I'd like to format this. Although, if you're just doing a graph and nobody's gonna see these numbers, it doesn't really matter. But I'm gonna say number, and I'm gonna say two decimal places, say okay. Now it looks nicer. We didn't have to do that for graphing purposes, just if you're gonna print this data out. So here's the data we want to graph. And this is gonna be our x axis, this is gonna be our y axis. Actually, I'm going to select even this text the top. Excel graphing often does really well with labels too. And if it doesn’t, we can always start over. So we're gonna graph this and we're going to use the XY graph. The year is gonna be the x axis, in this kilometers of Arctic ice will be the y axis. So hit chart wizard, and so now, instead of doing a line graph we're going to an XY scatter. And I don't like this curvy one, I like the straight line version. So I'm gonna click Next. And there it is. It's beautiful. Here, I'll click Next, and you can see that you can type in a title like Arctic Sea Ice. You could say that the x-value is the year, if you want. And a lot of people like to click “new sheet” and you a nice big graph and you can print it out. It would be full page paper. But I'm just gonna just gonna take the default here. I usually don't even type any of this stuff. I usually just click finish, and then I edit it later. So here it is. Arctic sea ice, year. Oh look at that, doesn't that look nice. Million square kilometers. I think I’ll get rid of this. You can just click this and delete it if you want. And you can change the title to you can mention that it's in millions of square kilometers. Put up there instead, I guess. Now the first time I did this graph, I'm gonna make it a little smaller here. The first time I did this graph, it looked a little different. I’m just gonna quickly show you what it looked like. I did this in somebody else's computer once, and we’ll come back to this in a sec, and it looked like this. And notice how there's a huge gap down here. And it's really hard to see the trend. The trend’s not as obvious as it was a few seconds ago. So the problem is that it's using this zero value. So if you get this, when you do your Excel plot, but you don't want to have all this blank space down here, what you need to do is change the Y minimum to a higher value. I see this all the time on other versions of Excel. So we could change it to 12 or 13, or I can put the mouse over here and I can see, oh its 13.32, that point. So I could even go as high as 13. Let's go to 13. So I'm gonna right click on here. If you have a Macintosh you do control mouse click, unless you have a two-button mouse on your Mac. But if you have a one-button mouse on your Macintosh, you want to do control click. Or PC people, do right click. Format access and normally by default this says Auto and it's supposed to pick a value, but some versions of Excel always pick 0. I don't know why. Even if you have that checked, if you type of value here, it'll uncheck it for you because it's saying I want 13 to be the minimum. I click OK, and there 13 is our minimum. And now you can really see the trend nicely. In fact, this can be a little deceptive. If people think this is 0, they might think oh looks like there'll be 0 Arctic ice in the year two thousand seven and-a-half, 2008, something like that. Well that's obviously not true, cuz it's already 2008, and they're still Arctic ice. Well what they don't see is the bottom is 13. Yeah, maybe there's gonna be 13 million square kilometers, but 0 is way down, down there somewhere. You can fake people out even worse by making this minimum 14 say. Now the chart goes off the scale. Or make it 13 and a half because it went down to thirteen point three. So let's try thirteen point five. There, now you say the charts off the graph. Literally, it's off the chart. The graph goes off the chart. So this wouldn't be a very good good idea. So there you have it. This is good for XY plots and I showed you briefly, simpler plots. Let me show you an even simpler one. Let's just do, let's say Ann, Beth, Clara and let's say Ann sells three house, Beth sells five, and Clara sells three. We could select this, and we could do a plot of this data. Basically, if anything you can you select data on the screen, often it's really easy to plot. So let's try the very first plot column next. And there you get a plot like that. If I click finish, it will look like that, but bigger. Or, we could do…a pie graph would be appropriate for this kind of thing. And you can see clearly how Beth is making the most sales here. And you can do 3d plots also. Anyway, play with it.