Mastering Excel. . .For Beginners: #1 Personal/Family Budget Project

1. Class/Personal Intro: everybody. My name is Joel. And through these classes I'm gonna teach you guys how to use Excel. It's a really cool program that can do a lot of things that a lot of people don't know about that can help you out in your daily life as well as just a lot of really fun things you can do with it and goof around with. I've been using Excel for a long time. I'm a structural engineer, so I use it pretty much every day. But there's a lot of things that you can dio if this is if you've never used excel before, you can use that just in your life, with budgets, our calendars or appointment setting things like that. I'm gonna teach you all about that. We're going to start at the very, very basics for people who have never even opened Excel. That's where we're going to start. We're gonna move up all the way to even using VB a coding to do some really cool stuff with it. So I hope you guys enjoy it a little about me. I live in Utah. Um, this is my dog cedar hanging out here. I married my wife Paula is wonderful, but enough about me. Let's get to see in some things that you can do with Excel. 2. Where We Are Headed: All right, I'm gonna show you some quick examples of things that we're gonna be able to do with Excel . Kind of the end goal, what we're working towards, um, this is a personal finance trucker that I made for me and my wife. Um, as you can see down this side, it's set up by week. At the end of every week, we would get together and look at our expenses for the week before whether it was receipts or looking at our online statement, Um, and then we would come in and we would get this dialog box, and we put in, you know, all of our expenses be able to submit that, um, and it would create this line item right here, uh, pull up weekly totals on which we had left for the months, depending on what we had for our income. Um, it creates averages down here for all of our, um, different expenses. So there's a lot to the spreadsheet. A lot of that's actually a lot of vb a coating, but we're gonna learn how to do all this stuff. Another example that I found I found this online. Um, this is something else that you can do with Excel that we're gonna be able to get to learn how to dio not specifically this. But you'll learn the skills necessary to be able to create something like this. This person made a snake game. So you press start and it starts going and I'm pressing. You know, my up and down left and right arrows. And we made a snake game in Excel. How crazy is that? So this is just goes to show you the capabilities of excel, um, and that you can use it for real life benefit, like a budget or calendars or appointment, scheduling or lists. Or you can use it to make games. Um, the possibilities are endless with excel. So let's get started learning about excel. 3. Getting to Know Excel: welcome Teoh Excel. We've opened the program and now we're gonna get to know Excel a little bit. Excel is a Microsoft program, and so is very similar to word and power point in the sense of formatting. We have found size and color and bold and all that jazz and justification. But in Excel we have what's called cells and in these cells is where we're gonna put all of our information and do all the things that we need to dio to make awesome spreadsheets. There's four different types of information that we can put in cells. First is text we can put. Second is numbers. Third is dates January 6. See how it switched it to sixth of January? It recognized that it was a date, and the fourth is formulas. So I can write, you know, equals three plus three. And it will do that math for me. We're gonna use formulas a lot as we learn more, Um, and we'll talk about those later. But for now, these are the four types of import inputs that we have on and we can like I said, format them however we want do texts we can do underline, weaken do size waken do make it a different color. Um, if I say hello everyone. Well, that looks stupid. So there's a couple of other things we can do in Excel. The reason that's happening is because we have a lot of text that can't fit in that tiny little cell so we can come appear and see how it turns into the arrow thing. Weaken, click and drag and make the so bigger so that it fits like we want. But maybe you don't want these ones to be bigger because, I don't know, maybe that looks dumb. It really depends on what you're what you're doing. So maybe we want those to be small, so we could also highlight all of those three and a format. All of those or another option. We can click and drag, and we can merge them. I can make 123 cells into one cell. So now let's just one. And it's big enough to fit that. It doesn't affect anything done there. So just really depends on what you're trying to dio. But there's lots of different ways that you conform at things. Another, some other functions that Excel has that are pretty useful. It can recognize lists and patterns, for example, for making a number. The list we need to get, like, one through 15 or something instead of having to type 1234 all the way to 15 we can say 123 and then we can click and drag. Highlight that. And then this little guy pops up that little lightning bolt that is excel recognizing. Hey, this looks like some sort of a pattern that you're trying to dio when that is what we're turning to us so we can hover over that tiny little square right there and our cursor turns to a black plus. And if we click and drag down that number, you can see it's keeping track of where we're at now in our list. It's keeps going up and up as we go down. So if you own 1 to 15 now, we have a list of 1 to 15. That's great. That was way easier than typing out. 15 letters are 15 numbers. Those are numbers, not letters. We can do this. It can recognize lots of different lists. Weaken Dio months. We can see January. Every very do the same thing. A little lightning bolt. I recognize you doing a list weaken do days of the week. You can do time. There's lots of different types of lists that you can do, and it will do it in different increments off. So if I say one oclock two o'clock, you recognize that I'm going by full hours. I mean granted and went into military time. But, um, if I then went and did 1 15 1 30 1 45 and 15 minute increments, I can highlight that. And it will keep going in that 15 minute increment and sees that it sees the pattern and that duplicates it as you pull it down. You can also dio like, repeat numbers. So if I, for some reason need to write 200 a lot, whatever that would be. Four. You can highlight that. And since hey, that looks like a pattern, you can drag it down, and now we just have a bunch of to hundreds. I mean, you could do that with a name, drag that down in hours, a bunch of Jim's. So there's a lot of different tricks that Excel has this is just one of them. We're gonna learn a lot of these so that we can be excel, masters and do everything that we want. One thing that we need to understand before we move on and that will help us a lot in the future is that each cell has an address. For example, if you think of it like playing battleship, this cell right here is cell D 10 d 10. This one's B two, sea 11. Another address that it can have is column three ro 11. Even though these air letters well, we're still gonna be using that for right now. Just understand that there's each cell has an address, and there's different ways to reference that address. 4. Intro to Functions: All right, so now we're gonna learn about formulas and functions that X Elkan Dio don't get scared. It sounds a lot worse than it is. But if you stay with me, what, it's gonna all be very useful, and it's gonna help us learn a lot about how to get really good with excel. So formulas like I showed in the last video can be a simple as doing three plus three. And that'll give us six in order for us to tell to write a formula. What that basically is is I'm telling this sell. I'm telling Mike Excel that I want this specific cell to be equal to. And then whatever I right after that. So that's how you start. Any function is you start with equals and then you put a bunch of things and then then it'll make that sell equal toe. Whatever you put for example, we did that one already with three plus three. But we conduce a multiplication. If we want, we can do 60 times. The asterisk is the multiplication function 60 times 10 600 division we do 900 divided by 1.987 whatever you know, it can do crazy things, you know, I'll figure it out for you. We can do subtract by all the all of those in there. And it's basically saying, we're saying we want this cell that were writing in to be equal to whatever we right, and this can start to get really cool. Um, as you just saw, we can start to reference other cells. So let's do an example or real quick. All right. So in this cell, we're gonna make this one equal. Teoh this one and she can see up here. This is our formula bar. This is showing that what is written in whatever. So we've highlighted. So in this one we've said equals G five, which is this. So that's where that address think comes in that we talked about last time g five. So if we stopped right there, you know, press enter. We just told a micro storm we didn't. We're told excel that we want this one to be equal to this one, which is what it is. And if I change this 1 to 10 it will automatically update this. This becomes super powerful and super useful to us. This automatic update feature of Excel that I could change it and it will automatically update other things, because all I said was making this one equal to that one doesn't matter what it is. I could say hello, and it just made it equal toe what it waas they make it the same. So now let's go in and edit this formula. So instead of having to delete this and redo it, I can just go up to the formula bar, clicking here and then add in other things that I want to dio. So let's add all these together. So if I say plus this one g six plus G seven plus G eight and see how they're all color coded, that makes it easy for you when you look up here, say, All right, G five. That's the blue one. Okay, let's blue. All right, that's the one that it's referencing because maybe maybe accidentally really wrote that. And so it's showing Hey, this is the cell that that's referring tunes like, Well, that's not what we want. We want that one g five. So if we press enter now, we've added all of those together, which is awesome. And if we change any of these, it will automatically update This becomes very useful in, like, budgets and invoices and billings. You know, counting things like this is becomes very, very useful adding these things up, but we only had add up four things I didn't take very long. What if we had add up like 100 things that would take forever to just click plus click plus click that would take super long and not be useful at all. So instead, we're gonna say that we're gonna use these formulas. So we want this Celt equal the some and then we have to open a parentheses. And then everything within that parentheses is going to get added up. So the some we're in a click and drag. CSO said g 53 g eight. Everything in there is being added together. Close the parentheses, press enter. And we just did the same thing with the some function. So we want to some those up and again. If we change these at all, it's gonna update here, and we can do this. There's so many different formulas that we can that we can use. We could say find the average. There's the average we could say Find the minimum. We could find the maximum we could find the mode. I mean, they're not just those. There are so many different formulas that you confined, which will learn about later. We're not gonna go through him all right now, but there are so many that we can use that are very, very useful and can help us do a lot of things. So that's what we got right there. Now we're gonna learn about, um some other functions will be less useful to you. But maybe you're doing some math homework. You can. You can use the sine function you can use co sign. You can use tangent. You can. You can write pie in there and that automatically put in 3.14159 Um, but for anybody that's not doing math homework, help there. And we're just learning this to help us in our daily lives. Do some other things. Those signing coastline things are gonna be as useful to us 5. Personal Budget Project: All right, everybody. Now we're gonna work on our ending project. And what that's going to be is just gonna be a very simple monthly budget that you can immediately start using if you want. This is where me and my wife started just simply keeping track of our expenses on our income in seeing how things matched up. So this is our ending product. Um, let's go ahead and get started. So open up a blank sheet and first thing we're gonna do is give it a title. So ready status is Jim and Pam's monthly budget. I'm gonna want the I wanted to be pretty big, So let's merge all of those cells into one like we learned previously, and we're gonna make it a little bit bigger. And let's give it a nice background color. Yeah, that looks good. And now we need to input how much we're bringing in. So we have Jim and Pam. We're both bringing in some money, and then we're gonna get some totals. Um, we are going to have a bi weekly amount because they get paid every two weeks. I assume, uh, and then we also want Teoh figure out how much they're getting per month. Um, I have a little bit of space between the top, this title and this. So I'm going to highlight that and say control X click right there, control V and move it down a little bit because I didn't want it to be that close. So now we're gonna put in gyms. Um, bi weekly check amount. We're just going to say that that $600 I have no idea what they're actually bringing in Pam's pretty cool eso. Let's say she brings in 7 50 on. We want total that up. So, like we learned last time, we're gonna use the function we're gonna do equals. And then we want to say this G eight plus g nine enter. And that's what we got so far. Now, let's go over to the month. I mean, we can assume you can always assume we're getting two paychecks a month. Sometimes it's awesome, and we get three. But let's go conservative and just say reading too. So we're gonna do a formula again. I'm going to say equals two paychecks a month, times that amount per paycheck, G eight enter, and we're gonna do the same thing to times Pam's paycheck. So that's how much they're both bringing in per month. And now we want to add it together and let's do it the other way. We learned last time we learned about the some functions. So let's do equals the some and drag these close the prince sees enter. Now. That wasn't much more efficient than just writing, you know, one So plus the other cell. But, you know, let's practice now we want to turn all these into dollar amounts. So let's highlight all of those and click our dollar amount so we can see that it's money. And now let's make it look pretty. So let's highlight those and will do the same yellow, I guess on we'll highlight those and maybe we'll do blue. Oh, great. We specifically want to see. I mean, the real number we want to know is how much we're bringing in. So let's highlight that and green, because that's what's important is how much we're bringing in now. We need to work on the expenses, so let's do expenses, and it's same thing. I'm gonna move it over right there, and it control X and then control V. Now let's type in some of the expenses we have per month way have rent and food, and Jim and Pam like to go on dates with anybody. For that, they have. Utility is I don't think they dry very much. We'll say they use gas. They might have a gym pass who knows miscellaneous and then will total up. I mean, you can put in obviously whatever you want, so have fun with that portion. So let's put in some values here. I'll say they're doing $1200 a month. They I don't know the eat out. Sometimes in 100 50 bucks between the two of, um, they get crazy and going $50 dates the utility than, uh, $300 $120 gas, maybe $100 gym membership and then $150 fluff. So now this some function is gonna be a lot more helpful because we have a big handful of things we wanna add up together. So we're gonna do another functions we're gonna say equals. I'm going to say some open parentheses, and then we're gonna click and drag the things we want to add up, close parentheses, enter and that's how much we got again. Let's turn these into dollar amounts. So it looks better because we're doing a budget and let's make it look pretty. Do you again kind of match things if we want Blue looks, great expenses, those air, we want to minimize those. So let's put let's put those in red. Okay, so we have some income and we have some expenses. Now let's add one more thing because we want to find out. Obviously, these aren't the same. So let's see how much we have leftover every month. Maybe we can put into a savings account. Maybe we just want to blow it all on. You know, something crazy. So let's do extra and we're going to do another formula so we'll do equals and we want to do our income or monthly income minus our monthly expenses. Enter. There we go. We got $630 extra every month. Just also has a lot for Jim and Pam. They can, you know, annoyed. White, Maybe. Uh huh. But the great thing about this budget that we've now created is that it's completely functioning and you guys can use it. Anything that's changing, you can adjust. So say, I don't know Jim lost his job and now he's only making 100 bucks a week. So see how this turned into parentheses. That means it's negative there. Their income is less than there, um, expenses. And when I changed that bi weekly check amount, it automatically updated everything else and told me I remember losing $370 a month so that we can come over here and be like, Well, I guess we're gonna have to start eating less, not gonna get to go out on dates as much, or at least do cheaper dates from enough to start saving on utilities. Maybe we'll get a bus pass. Nobody needs to go work out right. And we're gonna have toe cuts of miscellaneous things so that we can keep our budget above , um, above negative. So now we've created on awesome spread she eso have fun making your own make it fun for amount of however you want, Um, and hopefully you guys come up with some really awesome and very useful monthly budgets