Transcripts
1. Introduction: Hello. Welcome to the manufacturing Academy. My name is Ray Harkins, and this is the introductory video to this skill share class titled Microsoft Excel from zero to hero in 58 minutes. And that's exactly what I'm going to deliver to you here if you're the person who has never really jumped into the Excel pool. Or maybe you've just maybe entered some numbers, but you're not really sure how it all works. This is the place for you. I start at the very beginning. I literally show you how toe bring up Excel on your computer. It's the very beginning. I started the beginning and I show you how to enter data how to format cells, how to make it look right. But you don't. I don't I don't just stop there. I keep building and even show you some intermediate tools, things like data validation and even something called like a data model. Don't let those terms intimidate you. Now it's just a It's a stair step. By the time I show you how toe write your own formulas and and how toe reference cells and and give you these building blocks, you'll be able to bring all these blocks together and make your own Excel spreadsheet. In fact, this class toward the end actually walk through a case study is if you're a small business owner at a like an auto parts stores is the model. And I showed you how Teoh kind of make a little pricing model. I want to make it really practical, very useful for you so that in your projects, maybe you're in school and you want to build a your class schedule. Maybe you're just a regular person and you wanna build a maybe a spreadsheet for your personal finances or your home budget, or you're thinking about starting a small business. Maybe you're out on Etsy rebate and you want to track your expenses and your sales and a few other things excels a fabulous tool. And this is a fabulous class to get you started. So what I'm offering you here is seven video modules that take a step by step building one on top of the other through some fundamental excel skills. You're not gonna be an expert in this class, but you will be well positioned to to bring your your newfound skills together into something that's helpful for you and as this will be a foundation for further learning if you want to, it's a great place to start. I designed it for people like myself some years ago. I'm designing for people like you that just want to get started. So you get seven separate lectures. I take my time walking through all the important steps of Excel, and then at the end, all the way at the end of this class, I'm going to give you some projects. Suggestions. Learning is great, but it's really become solidified when you put it in practice. When you do it for your budget, for your class schedule for your Etsy store, for whatever you're doing, that's when you build it for yourself. That's when it becomes real. So anyway, you have nothing to lose. Sign up for the class, and if you know anything about me and the other classes that I teach, I love hearing from students from people to take my class accounted as an honor. If you would sign up for this class and kind of take this educational journey with me, send me a note I love to hear from you love to help you. If you have any problems or questions, send me a note through skill share. I'll try to get back to you as soon as possible. So thank you for checking out this class. Sign up and let's take this journey together. Thank you.
2. Getting Started: Okay. Well, like I promised in my introductory video, I'm starting at the very beginning. This is just my computer desktop. I haven't even opened Excel yet, but I'd like anything in computing. There's going to be a bunch of ways to kind of do the same thing. So toe open Excel. I have, ah, desktop icon right here. Excel 2016. I also have an ex excel icon down here on my toolbar. And then in my start menu, I have excel pinned to my start menu. Or I could go through all programs Microsoft office, and then I'll find Excel in there. So where how you do it, it doesn't matter as long as you get to the same spot. So I'm gonna open up excel by double clicking on the icon and seeing what this opening screen looks like. Okay, so once your screen is open here, over here on the left. Now, if you're new to excel, you may not have nothing right here, but if you've used it for a while, you're going to see these air. Your recent spreadsheets that you've been working on so again years might be blank. If this is your first journey into Excel. But after you work on it for a while, you'll start populating this with with older templates and spreadsheets that you've been working on. So I'm gonna skip this first line for a second. I want to come back to because I want to show you what's down here Now. This will show you all sorts of templates that you could use an excel. So these air common projects like your school schedule and academic calendar. I think there's a family budget in here. Here's a simply monthly budget and all sorts of stuff that might be useful to you an invoice of your small business owner, etcetera. So after you dig into Excel a little bit, some of these templates may be useful to you. They're excellent and their formatted exactly for the purpose that they were designed. So back up to our top line here. Now we are going to dig into the blank workbook. We're going to just start from scratch, but I want to point out these two also, so this icon is just a full tour of excel. It's going to show you all the basic features, and it's going to show you some features that are new to 2016. Now what I am going to go over isn't necessarily specific to 2016 version of Excel. Most of stuff is pretty, pretty easy or, you know, just starter type commands and uses of excel. So even if he had, like, the 10 4013 I think it's gonna work out fine for you, so don't worry about that. But when you're done with this Siri's that you're working on right now, take a look at this button right here that take a tour that will be very helpful. Then, when you're done with that click over here getting started with formulas, The rial power in Excel is in its formulas. You know you can enter text, you can enter numbers. But what you do with those Texan numbers is what makes Excel so amazing. That's where formulas come in. All the easy ones, like, you know, addition, subtraction, multiplication. But it digs way deep into finance statistics, all sorts of complicated formulas. Maybe you're into trigonometry or geometry. It's got all sorts of mathematical finance, logical formulas, all types of stuff. So this will be a great, great introduction for you into the world of formula. So let's get started with our blank workbook, and I just want to show you some basics in this first video here. So up here, this section right here that I'm pointing out is called the Ribbon. So on the ribbon are icons for commonly used commands, and they're grouped under these various headers like home and insert and draw and Review etcetera. So on the home ribbon you'll find, Gosh, she's air really like the most common, most used commands, and they turn them into icons. Because instead of typing in a command or something like that, you can just click a button and cause some action. So as you get under these various like insert, it does charts and tables and graphs and all sorts of maps you can insert into your spreadsheet drawing and its centre. Here's a formulas tab. This is all sorts of those formulas you can access there, and you get the idea view. It shows you the different how the pages laid out and this type of thing. So there's all sorts of very useful commands, and they're organized under these headers, so I just want to point out something here. This is called What we're working on right here is called a workbook. Notice we have an open. It's called book number one. So, like any book, it has a lot of pages. Or in the case of Excel, they're called work sheets. So this is a workbook, and then this This piece right here is called a work sheet. So look down here. This is called sheet number one, so you can add sheets to your workbook. Add these air worksheets. You can add worksheets just by clicking that plus button. Now you have six sheets in your workbook, and you can add a whole bunch of these. But for most of your very beginner workbooks, you probably just have one or two sheets. Maybe. But as you build your acceptance of skills, your spreadsheets, they're gonna get more, more complicated. They're gonna expand multiple sheets, and there's just a tremendous amount of power in linking various sheets together. You can do a tremendous amount so but for right now, just so you know, they're sheets, there's a workbook of worksheets. And then what else do we have? inside. Here we have columns which are all designated by letters, and then we have rows that are all designated by numbers. So then the intersection of each column and row is a cell. So if I click on that cell, this is the intersection of be column and one row. Therefore, we call this be one, and if you look at this window right here, it shows be one. So I'm just moving my mouse around here. But if I click on anything like right there, I'll click on this cell. What cell is this? F four? Because it's the F column and the fourth row. And as you click de 12 d 12 as you click you can, this will automatically update. And this is how cells are referenced. A lot of times you're looking at instructions or a video on how Excel works. They make very abundant use of cell references, so get used to sell references. Now you see me, I'm moving my mouse and I can click on various cells. The other thing I could do is highlight a group of cells and we'll dig in a little bit more why you might want to do this, but you can highlight any group of cells. And all I'm doing is I'm clicking my right button and then I'm dragging over, dragging down. That's what I'm doing to highlight different cells. That's all I'm doing. Another way to highlight cells would be to highlight the entire column. So if I click on this E, I'm highlighting the entire E column. I can also highlight a series of columns. I could do the same thing with Rose. I can highlight a series of rose and this goes over. You could see up through s here, but it goes way out like zzz or something. I don't even know how far goes. But as far as it goes, isas faras the highlight goes so so I can highlight individual cells, entire rose, entire columns, groups of cells. And I'm gonna show you one more trick to now. If you look at the lower right hand corner of your keyboard, you have a control button. It's a Ctrl using the control button. So I'm gonna click on this cell D five that I'm gonna hold my control button down G five g 10 de 10 so I can highlight what are called non contiguous sells those air cells that are not touching each other. That's what non contiguous means. And I could do that by highlighting my control by holding my control button while I'm highlighting it so I can highlight this hit control and highlight another section. Highlight another section. I can do that. So if I need to apply, for instance, the same format or say my same formula or something like that to these various cells, I can do that. So last point about moving around is I've used my mouse a lot, and you've seen that I can also use my arrow buttons in the lower right hand corner of my screen. And there's my body down Arrow, right? Laughed. I can move around with my arrows just as easily. The other thing I could do is any time I hit my return key. It automatically has Thehyperfix Cell Index down one row. So those are the basics on opening up the spreadsheet and getting acclimated to its main features.
3. Data Entry and Formatting: Okay, Now that you've learned your way around the workbook and worksheet, let's start doing something here. So we're gonna talk about data entry so the cells of an excel worksheet are capable of holding all sorts of different types of information. And the two we're going to focus on today are pretty much text, which might be people's names like Bob, Mary, Jill and Ed, for instance. Or it could be numbers like 12 44 2 and one. Those air just numbers and texts. So you might be maybe making ah, How do I say in order sheet, maybe your child is selling Girl Scout cookies and you want to know the names of the people on how many boxes they order, And right there, you just made your first spreadsheet. So that's pretty straightforward so it can accept texts. It can accept numbers. Now let's get in a little bit too formatting the worksheet to look like you want it to look . And there's a big emphasis on formulas, and we're gonna talk about meant some of the powerful things you can do. But if you're in a work setting or a community setting where you want your presentations to look good. It's important not just to have the facts right, But you want to present it right, too. So even just with this very small spreadsheet, I have some options here and we're going to refer back to Are our ribbon up here? So what are we doing here? We're gonna highlight are small spreadsheet that we've made so far. And let's just say no, this box right here, that's the font. And this is the font size. So this happens. The name of the spot happens to be calibri. It just opened up that way. And here's the size 11 so I can make this bigger, like a 20 or smaller, like an eight. And whatever I wanted to look like, that's pretty much what I could do. So in addition to changing the size, I can also change the font type. So again, this is a calibri. But if I drop down this menu, I can see all sorts of stuff like Batang. Maybe I like the way that looks better, or I can scroll down further and each of these show me what it's gonna look like like elephant. That's what it's gonna look like. So I have options to change the font name font type, Like that one. Maybe I like that one. And then the font size. So if I like 14 maybe like a 12 and that's what I want. So I've just done that. Now, another thing I can do here is I can add what are called Borders. So this little drop down here gives me the basic formatting options for borders. Okay, so I can drop this menu down right here, and you can do all sorts of stuff. Maybe you want borders all the way around. That's a pretty common thing to actually, or maybe you. I don't want any borders. Of course you can get rid of those. Maybe you just like a stick outside border. That looks kind of nice. One of things I like to do is ah, I like to apply all borders. Then I like to apply thick outside border, and I kind of gives a nice look. So anyway, play around that and you see what you want to there and then naturally and again, I'm just highlighting this text again. I can make my font bold. I can add in italics. I can underline it. Or I can do a combination of all any combination. I feel like I can do. Maybe I want to bold the names and leave that regular. Okay, Now I'm back to having all my cells highlighted here. What else can I do? This button allows me to change the color of the fun. Maybe I want a blue fund or this gold fonts, red fund, whatever I want to do. And again, you're just going for what? Look is best with you. That's all you're trying to do. So in a business publication, maybe you want to stick with black. But if you're doing something for your kids scouting group, maybe a red or ah, orange or some fun color would be better. It's completely up to you. Okay, so we've we can apply a combination of font colors and fonts, styles, font types and the size. What else can we do? So over here I can do what's called an alignment. They used to call this justification over in the old back in the old typing days you could right, justify. Now it's called a line so you can align right? A line left or line in the center. And right now we're referring to horizontal alignment side to side. Now, this is gonna be a little tricky. This is vertical alignment bottom, middle top. And, you know, it probably didn't even notice it moving. But take a look here if I highlight these cells. And here's another little trick. I'm gonna show you all the way down in the corner. See my little plus button here, I'm gonna plus plus. Plus, this allows me to zoom in so I get a little closer. The action. This is a little zoom slider. It's the easiest, quickest way will say to zoom in and out so you can zoom right there. Okay. Back to our page here. So what I can do is highlight the rose and then watch what I'm doing here. See that little symbol that shows up right there? It's a horizontal line with an arrow that passes vertically through it. I can click right there. Notice it says height 16.5. So I can open this up if I want to be a little bit bigger. Maybe I wanted to be 30 and look what has happened. I just made all of those rose 30. Now, if I highlight my section now, I can see my center. Vertical alignment Top vertical Lyman. So again, I can do a combination of any of these that I want. Just like that. Maybe I want this to be centered. And this to be right aligned. It's completely up to you. Ah, a lot of times I like to make things centered within the cells. It just gives it a nicer look. Here is my number formatting. So maybe this is the Maybe this is the number of dollars they contributed to our community fundraiser. So really quickly. I could just hit that button and it converts it into dollars like That's really amazing. Maybe it's not dollars, but it's ah percentage. So, you know, in this could be anything Ah, percentage increase of something. It's completely up to you. So if you want dollars, you click that If you want a percent, you click this. Okay, so maybe you want something special. Okay, well, under the number formatting or alignment or font. See, this little arrow points down into the right. You can click on any of these and what you're gonna open here. This is a very powerful box. The format cells box notice we clicked that down arrow in the number section. Now I've got the number tab open and I can do all sorts of stuff. Fractions, accounting, the dates, text. I could do any number of things here, so maybe I want a number with one decimal place. Well, there you go. I can just There it iss I just did it. So back to my same place I got so there's number formatting alignment, formatting. So that's my horizontal center left, right, vertical. And then there's so many more options. This actually allows me to make the text angled within the box. And then look at my next one font. So this section over here, the font section of my ribbon is just a small selection of commands inside here. Now I've got all sorts of stuff. Bold italics, all these fonts, colors, strike throughs, maybe for editing and superscripts sub scripts. You see that mathematics and other things. So there's all sorts of commands in here. Borders, same thing. There's all sorts of different lines colors, placement of those borders, even diagonals, so you can like cross out the box like so it looks like you could do this so it looks like you've Xed out the box. All sorts of opportunity there fill. This has to do with the fill of the box itself. So if I were toe click on that knows how, if it filled it in with this gray. So again I can click here to get to my format cells box. And here's my filled tab. I can change these colors. I can do anything I want. Just like that, there's an introduction to formatting cells. Here's the basics. Click on anyone that one of these little down airs and you get your format cells tab. Use it. There's a lot of stuff in there to play with.
4. Copy, Cut, Paste and more Formatting: Okay, Now we're going to focus our energy on this little tiny box over here called Clipboard. Okay, So what I've done for you had a time. I just built a little What do we got here? Four column wide by 10 rows deep, just a table of data. And I've highlighted it and then selected the gray background color and font size. I've centered everything. So I've done some formatting to this. Okay, let's pretend I want to copy this table of data this entire table. Now, look at this. I'm gonna go down to my zoom slider and back this off just a little bit Now, maybe I want a copy this entire table of data and put it over your copy of it. So if I hit this little button right here, copy. Click right there and then paced. See what I just did? I just duplicated that entire table of data from here to there. Okay, Now I'm gonna back up. See this arrow? Any time I wanna undo you. See that says undo paste. I can undo it. And I'm right back to where I started, okay? No. Let's pretend instead of copying, I actually want to get rid of this one and move it over there. Now I'm going to hit, cut, click paced. You see what happened there? The difference. So instead of duplicating, I kind of just moved it. I cut it from one place and put it somewhere else, and I'm gonna back this up again. Okay? So now, naturally, Aiken, do that with just a single cell. If I hit cut, I could pace it. They're just very quickly. I can also copy a little section like this. Copy and hit paste over here. Now, notice what's happening every time I've done that. What it did was it not only copied the numbers in the cell, but it copied the format as well. Did you notice that? So over here, not only do I have my numbers, but I've got the gray background and I've got the border. So let me back that up here. Okay? Now, let's imagine that I want to copy this row, so I hit copy, but I'm not interested. All the formatting stuff. I just want the raw numbers and you're going to use this drop down era right here. All this stuff in here these air all of your pasting options you never would have thought. There's so many pasting options, you know, But there really is. So here's what I do. I highlight this a copy. Now I click where I want to put it And maybe I don't want all the formats. I just want the values paste values Click there. Now look what I have. I know I just have those raw numbers. Look at this one. If I click this one notice it's 10.560 a copy. Now watch what happens when I do that paste. It made it 0.56 Why? Because the number formatting itself allows for a certain number of digits after the decimal place. So if I highlight this and I'll just show you that real quick, this number formatting I could make mawr or less digits. So that's part of the formatting itself. So if I want to make that a three digit, I just open it up right there. That's my part of my number formatting. So there's paste pay special. Now, let me show you something. Let's just pretend these numbers were sitting over here and I say, Gosh, I really like the way these numbers look, The formatting of it. I can click here, hit this one. This is called format Painter, so I like the way it looks. I can click here, and I can highlight that. Notice how? Notice what that did There it just transferred. Didn't change the number. It just applied the format to where I'm going. So that's an interesting way you're pasting, not the number, but you're pacing the format and you can do that. Anytime you can hit, you could do differently. You can hit copy, and then maybe I want to click on this and then I go paste and format like that. So look what I did there. I pasted the format from this cell onto here. Now I've got the great background on the borders, naturally, any year for many whatever Color, font, color, background, all that stuffs part of the format, it'll transfer over just like I'm showing you right now. Now, here's one more thing I'm going to show you that has to do with copying and pasting, and I can copy this. Now check this out paced, and then I go all the way down here Oops. Right there. Picture. So now what I have here is a look at this table. This is actually a picture, so I can't edit the cells anymore. I'm trying to, but I can't because it's no longer sells in rows and columns. Now it's just a graphic. This is handy. Sometimes if you're pasting tables of data from Excel into maybe a power point presentation or word, Doc, you don't want all that numbers and formulas in there. You just want the No, you just want a photo of it, you know, So that's a great way to do that. So definitely dig into this a little bit. The paste button that cut is pretty straightforward. The copy. You can actually copy it as a picture, but once you copy something, here's where the real power is. I can copy, but then look at this. All of it. I can pace just the formulas. I can paste it graphically. I can carry over the formatting. I can do all sorts of stuff here. So here's a man you definitely want to explore. Last thing pay special. This allows you again a whole bunch of features. Some of these air mathematical. You can transpose the table, which is like two rotated nine degrees. You can do a lot of stuff like that, so play around with paste cut copy paste, very powerful tools.
5. Formulas and Data Models: Okay. Now I want to do a quick introduction to formulas. Formulas really bring the power into using Excel. So let's just start very simply, I'm just going to click on a cell. And then I hit the equal button on my keyboard. And if I just said, equals four plus four, there's my answer. Eight. You could do that any time equals nine times five. There's my answer right there. So anything you enter in a cell in terms of a mathematical formula, it will calculate it for you. So this is really the simplest way to do formula so you could do. Plus, we did applause. We could subtract. We could divide you, get the idea using our slash sign. That is the very easiest way to do formulas. So let's just pretend, though, that we want to make a more general model out of our formulas and take the specific numbers out of the cells. Let me explain what I'm trying to do here, so let's pretend we have Ah, parameter number will call this a and we'll call this V, and we'll put in a number like 12 and a number like seven right there. So I want to. Maybe I want to know the product or what? It is multiplied together. So I can say now, instead of typing in here equals 12 times seven what I can do instead of using exact number , I can make it refer to a cell. So again, I think I hit my equals button, which is just to the left of the backspace on much most keyboards equals Now I can just click on that cell hit my ass tricks my shift eight Or if you have a keypad, the little multiply sign And there it ISS So I've just done a formula by multiplying these two things together. So the neat thing and I would refer to this as a model is that you can change the input parameters very easily and not have to redo your whole formula. So, in other words, of a suddenly becomes 11 it changes. If b becomes 12. It changes already. So we've just built our first model. So if I want that to be our product, maybe I want this to be, um I could call it a squared. So this equals a times a So there's my A squared and again if I I'm just adding my borders your real quick see how fast that is. So any time I changed my a, both of them change. So I have my a squared and and I'm just making these up. I don't know exactly why you would want to do that, but let's just then say are maybe here we want our products divided by a square That will be our header now noticed. I ran out of room there. So just gonna open it up a little bit so I can read it a little better. Maybe something like that, like we were doing before. Now what I can do is this is my product divided by a squared. So now I could say equals this divided by that. And there's my answer right there. So I have inputs right here that maybe I'll highlight in Ah, yellow. So those air my inputs and these air my output. So any time I changed this, I get different answers here. And this is called a model in excel. So it has inputs. It has outputs now, in terms of the formulas themselves. I showed you some pretty simple ones multiplication. We saw division those air. Very simple, but if you want to get into complicated formulas, I definitely recommend you dig into this tab. I can't go over all these. There's hundreds and hundreds of different formula for financial. There's of these text based ones. Math in Trig, this one you could work in for years. I don't think you'd ever get to the end of this. There's all sorts of functions or formulas that you can apply to any numbers or groups of numbers, etcetera. So think about the formulas you want to use, and any of those will work in here, too. So here's your formulas, and here's your first Excel model.
6. More Models and IF Statements: Okay, let's advance this idea of building models a little bit here. And I've built a few models here for you. And this is just one application I picked up. I'm trying to determine the volume of a solid in this case, a rectangular solid. I also picked a cone and a picked a sphere. Now I have a hard time remembering my geometry, too. So I inserted photos out of a textbook that show us the formulas for the volume of these various objects. I did that. You could do this on your spare time. Insert illustrations, pictures and then you select a picture and you can insert it wherever you want. So I inserted thes three photos. So let's just go simple here with the volume of a rectangular solid. So the volume is length, times with times height. So I built this model on notice I four men at the cells with borders and different colors just to make it a little easier to read. So we'll say I have a length of 10. I have a with of 10 and I have a depth of five. That'll be my rectangular solid. The volume is 500 Now, you'll probably notice that this block change over here. Let's ignore that for just one second, cause I'm gonna talk about if statements when I get to the bottom and I'm gonna show you what I did there. So how did I do this? I entered my parameters. 10 10 and five. Now, naturally, I could change these two, like, 31 then it changes. Or it could change that to seven. And so you can see how my my volume here changes. So let me click on this. Let's see what's going on here. So what did I do? I just made this cell equal to B three. Time C three times d three. That's it. So anytime I put a new number in here like 11 then that number changes. If I put a number like that like one it changes. Noticed this change over here. I'll get back to that in a second, so I could do the same thing for a cone. So 1/3 pie R squared times height. Okay, so there r is the radius, so pretend to have a radius of five and the height of 10. There's my volume of to 67.77. And what did I do here? Notice my formula equals 1/3. So I just went 0.3 through the three equals point through there the other eight times. This This is how you enter the number for pi? 3.14159 etcetera. You just put pie open parentheses. Close France's. Oops. So pi times be 10 times be 10. That's radius square time. See 10. That equals my volume there. That's how I got that. So naturally I can change us toe like a seven and it changes. I can change out to a 12. You get the idea? Noticed this numbers changing over here. I'll get back to that as well. Okay, My last one is simply the volume of a sphere and the formulas. Very easy. 4/3 times pi times are cubed and you see what I did there for thirds times pi times b 17. But see, instead of saying times B 17 time to be 17 3 times I used this symbol right here, which is called the carrot. If you look on your keyboard, it shift six. It's the little symbol above the six and then I put That's for exponents. So it's the same as saying be seven teams be 17 times B 17 times B 17. It same is saying cubed, You say Cari three. So if I enter a radius of 10 there's my answer right there. Okay, So let me introduce you now that we're working with models and you got that whole idea, let me introduce you to a very powerful tool called the If statement. And it included a little example of an if statement here. So watch what happens here. Now if I enter a number, let's say 12. This says less than or equal to 50. But if I under 233 it says greater than 50. So it's like a little computer program that's analyzing what's in this cell and then out putting something to this self. So how am I getting there? So I'm gonna click on here. Let's look up here. I have this formula where I start with equals. If so, this called an if statement and then my next piece of the puzzle here is what's called a logical statement here. Okay, It says if B 23 B 23 is less than or equal to 50. So that's the question here. The question is, is B 23 less center equal to 50 and then the 1st 1 Now here I'm using quotes is if the answer is yes and the second is if the answer's not yes, so So what do I do here? Let's just put a 12 back in there. So if we look at the formula if B 23 is less than 50 which it is, it's 12 then the answer's yes. So right. And that's what the quotations air telling you less than or equal to 50. If not, then put something else. And so if I put 6 77 it says greater than 50. So going back to my formula. If, yes, put that if no, put this, so that's how I do that. So this is just a really example and and by the way, it says less than or equal to 50. So if I put 50 it says less center equal to 50 have put 50.1 ah, greater than 50. See how that goes. OK, so these are remarkably versatile tools, so take a look at what I've done over here. I have a All these green boxes are if statements. So I'm referring to each of these formulas these models that I built before. So in here I say this G three, I say, if e three my volume is greater than or equal to 100 right greater than 100 if not less than honor. So here's my volume 77. So it says less than under. Now, if I change this to a 12 what happens? My volume goes up to 9 24 9 24 is, in fact, greater than or equal to 100. So I right greater than honored. See how that works. So it doesn't just have to be text like I've shown you these last two cases. It could be some mathematical formula, so I just made this one up. So what did I say here? Referring to the conical volume right here F 10 I say if d 10 de 10 So it's my volume is less than 100. Then multiply it by 2.5. If not, multiply it by negative 2.5. So watch what happens here if I make this a one. So it's a very skinny tall cone. My volume is 12.75 So what did I do then? I took my volume 12.75 and multiplied because answers Yes, it is less than honor multiplied by 2.5 if it is greater than 100. So this is a very big, large diameter cone. That's kind of short. It's huge number, so certainly it's greater than its not less than 100. It's much greater than that when you do multiplied by negative 2.5. That's how I have a negative number there. So essentially it's gonna be a negative number. It's over. It's over 100. It will be a positive number if it's below 100 I can prove that out again and again. There is a positive number because it's less than 100 and there's a negative number cause it's greater than outer. So there's just another example. So let's look at this now. Here's an interesting thing. So here's a different if statement So now I'm referring to So here's my e 17 and I say, if c 17 So the volume my sphere is greater than d 10 then say greater than come. Or if not, then less than code. So So what if I do this less than Cone greater than Cone? So I'm comparing it two. I'm comparing this to another metric over here, so that says greater than Cone. But if I change, so I'm not going to touch this. I'm just gonna change this if I change this. Look what happened here less than Cone. So you can. You can do all sorts of stuff with this. If statement, it's incredibly powerful. I'm gonna show you something right now. Formulas. Logical. So these air the logical formulas. If I'm gonna click on this now, here's a little cheat box. You can enter your logical test here and your true value in your false value. You can use this this little, um, wizard, you might call it OK, but that's not why I brought your help on this function. Click on this button and Excel provides you extensive support on using the if function naturally, all the functions too, but certainly the if function. So as you scroll through here, you learn all about logical tests. Some of these air literally the ones we did like using texts. And it gets somewhat Mawr advanced, comparing things. And then there's related. I'm just scrolling through here very quickly. You can do things called nested ifs, and there are related commands like Count. If some if etcetera very, very powerful, I would encourage you to check it out.
7. Data Validation and Drop Down Menus: Okay, We've covered a ton of material so far, and I want to bring this all together under one roof and do a case study for you bringing together all the different stuff that we learned here. So I built a lot of this stuff ahead of time. So I have tables, and I'm just imagining that I'm running my own business. May I'm running one of those junk yards where you go in and, yeah, find the car you need and you pull the part off. So I have these different tables and I'm trying to come up with pricing for the various car parts that I'm going to sell through my business now. Obviously, I can't identify every single car in year and make and all the stuff. So I want to come up with a model that I can price the components that people are pulling off the cars. So I start with standard components and we've got the driver's door in the hood and the door handle in the horn. And you could imagine this This could go on quite a bit further. Okay, so I have component names, and then I have a standard or what I'm calling a base selling price for each one of those components. So I have this table here. Okay, so then what I have here, I wanna have a certain premium mark ups or discounts based on the make of the car and the year of the car. So if you look at the make of the car, let's just say a Chevy is kind of the middle of the road. So if I have a Chevy, uh, driver's door, you know, I'm not trying to market up or discounted at all. Now, if I have a Mercedes, well, those were going to be a lot more rare. I have a lot fewer those on them on the lot. So I'm gonna mark that up substantially. In this case, 280%. Multiply it by 2.8. And then let's just say I got the Mazda while those air kind of common, I've got a bunch of those on a lot. Ah, and I want to move them, so I'm gonna give people discounts on that. So I wanna multiply my standard price by 0.7, or, in other words, give it kind of a discount. Okay, So then I have the year premium to. So the idea is, if it's an older car, well, there's gonna be less demand for it. The parts and probably are gonna be is quite a good condition. So I want to give this a discount. Okay, so I want to multiply it by a quote, premium or effectively, It's a discount, but by 0.65 So I want to discount it to tryto move that older inventory incentivize people to move that now, as the parts get newer, newer. Look at these 2009 10 11 By the time we get to 15 4018 I want to mark those up substantially because one, they're probably in good condition. They're in high demand. If if you know, if you wreck your 2017 car while you're gonna be willing to pay a little bit more for that , the parts are in better condition. So I want to mark that up. Okay. And then I have salesman on the floor and these air My salesman and I want to identify who actually sold the part. Okay, so now I want to introduce you to two new concepts that are actually fairly advanced. So one of them is called data validation. And the other one is called the look up or vertical Look up in the called V. Look up for short. Okay, so I'm gonna start with data validation. Okay, Now, this section right here is my pricing sheet. So a customer comes in and this is the form I'm going to fill out, and then my model is going to calculate the selling price for that part. So let's check it out. So let before we get to that. But let's just go with this data validation idea. So what data validation is is a means of limiting what can be entered into any given cell. Let me just explain this to year. So part name. So our customer comes in and they're gonna select one of these standard parts. And again, this list could be a lot longer. But let's say they're going to come in and pick a hood. Okay, Well, I could type the word hood in there, but instead of typing all that in there, I want to be able to pick from this list over here. So watch this trick. Okay, I'm gonna highlight this section. Then I'm gonna go toe data my dad ribbon. Okay, now it's kind of an obscure symbol, but look at this. Data tools. And then this guy right here is the data validation buttons. I'm gonna click that, and I get this box right here. Validation. Criterion. These air fancy words. I know you don't use them a lot, but it's just limiting what's allowed to go in that box. Okay, so what do I want to allow? I want to picket a list and then the source so it once and okay, I want to pick a list. Well, where's the list? That's what sources. So I click this button and then I highlight this list over here. See what I did there. Then I hit enter, and then I just hit. Okay, Now watch this. So now when I click here, I get this little arrow, See, wherever I click, I get this little arrow to the right. There's my list. So now, instead of typing hood, I just center hood, maybe wiper arms. Maybe it's a driver's door. See how that works. So and I can delete those just like I do any other text? Okay, so So we have the part name now. What do I gotta do now? Let's just repeat the process. So for the make of the car, I want to apply my doubt. Day of data Validation. Making a list and select my manufacturers center. Enter. So now just verify there's my list of manufacturers, like click on any box. Okay, Now, same thing year. Data validation. Pick list source. My list of years just like that. Enter. Enter. So now what I can do is I could say, pick for my list. Okay? The customer a comes in, he has a tail light off of a Honda. And the year of the Honda is a 2014. There you go. That's how we do it. Okay. Two things I did between videos. Here's I just filled in all of the part names, makes and years I left this one last one. Open, So thes air drop down menus for all of these. So I can pick a tail light from a Mazda and pick my year, and the customer brings in a 2017 and then notice this column over here. I have a sales rep column. I did the same thing, the data validation list, And I have a salesman list over here. And maybe that was HH h. OK, so here's the cut. Their sales reps that are managing these customers that come in. Okay, so now we're gonna talk about V, look up. Very, very powerful tool. Certainly when used in combination with data validation and some of these other tools we've been working at. Okay, So here's the idea to calculate the price of the component were selling instead of having the thousands and thousands of different years and makes and components, we just wanna have a PSA model that we can follow, that'll that'll give us the price of the part. So here's how we're going to do it. We have a base selling price by component. We have a premium that marks up or discounts based on the manufacturer. And we have a second premium that marks up or discounts based on the year of the component . We're gonna multiply these three things together to come up with the price. Okay, Now, we don't want to type all this stuff in and look over tables and all the stuff we want to write. A formula that captures the correct price inserts it into our cell, Then we're going to do some formulations, so
8. Vlookup and More Formulas: So this is where the V Look up. Command really shines. So let's get started here, and we're going to use this V Look up. OK, so I must go equals V. Look up. Open parentheses. Okay, That's how we start our formula. The first thing we're looking for is the base selling price for the tail light. So it's asking me for a look up value. I want to look up the tail light that cell. Okay, so then it wants to know. Okay, from what table array are you talking about? So I want to look at my standard prices table right here. Okay? So I'm gonna highlight that entire table. I'm gonna show you a little trick here. I just click right here. Anywhere in here will do it inside this array. A three to be 13. And on my keyboard, I'm hitting the F four button. It's just above the number four button. And then see how these dollar science enter there. Dollar signs entered there. Okay. Basically, the reason I did that is because I'm gonna be copying and pasting this formula in the subsequent Rose. If I don't put that down to what That does is it locks down this table. If I don't do that, the formulas getting messed up Okay, so I could explain that. It's if you want to look it up yourself. It's called absolute cell references versus relative cell references, and what the dollar signs does for me has allows me to use an absolute cell reference. In other words, no matter where I copy and paste this formula to, it's always going to refer to a three through B 13. Okay, absolute cell reference comma. Now it's asking me the column index. Okay, so all that means is OK, I have is a race selected. What column on my talking about. OK, so in our case, it's column to you could have 10 15,000 columns, but so it's just the number over. So I want I'm referring to column number two. I want to grab my price from hit comma Common. Now it's asking for true or false. Okay, now we've used data validation. We know we pulled from this table to begin with. We want an exact match. An approximate match gives you a little flexibility there. We don't want that. We want the exact match I double click it close. Parentheses hit. Enter. So what I got here? $11.50. So a tail light base price tail light $11. 50 cents. So, so far, it's working. OK, so now I want my make premium. So I'm gonna do the same thing. Equals V. Look up, open parentheses. And then I wanna What am I gonna look up my make of my car? So it's my look up value is the Honda in this case M three comma. Now it's looking for a table array. So I highlight my make premium table D three through E 10. Men do the same thing. I'm just gonna click there, hit a four click there, hit a four. See the dollar signs. Okay, Absolute cell reference, comma. Now it's asking for the column index. Just like the other one. I only have two columns here, and I want the second column and then comma and then ah, false For an exact match. Close their ago. Okay. Same thing with the year. It starts getting pretty easy. Pretty repetitive. After a while. My look up value is the year comma, my year, premium table or array as it's called hit F four to make this an absolute cell reference and then the column again. It's the second column over comma, false, close parentheses. Okay, so what do we have here so far? My based selling prices $11.50. There's a slight discount because of the make. There's a slight premium because of the year. So now in here, I just say equals this times this times this boom. $13.46. Now. Previously, I formatted both of these columns as, um, currency right there. So anyway, that's why it's formatted like that. Okay, here's what I want to do now. Everything seems to be working good. We can double check make premium for a Honda Honda. 90%. See how that pulled that in year. Premium for 2014. 130%. So everything looks good. So I'm gonna highlight this. A copy. Highlight the section I want a copy of to now. Remember I said, how many weight all these different paste functions and you're gonna look through some of those We want paste formula. So I click on pace formula and what has happened here. So I've pasted all my V lookups down here now noticed. Let's just look at base selling. Remember, I put those dollar signs in there, so I'm gonna just scroll down through base selling and you know, it's the only thing that's changing is the value that I'm looking up, the array, that I'm looking it up in a staying the same. The column second over is staying the same, and it always wants an exact match. See how that doesn't watch this one on makes premium. It's on Lee the look up value that's changing. So then I multiply all these over and here's what I got now my model is generating the selling price for that component. That's pretty neat, huh? So anyway, so now I have my sales wrapped. I developed this model, and then I was just gonna add one more thing. And here we talked about If statements and we have these sales reps, let's just say part of our business model to incentivize our reps is to have a progressive incentive system. So I just noted it right here. So if the component you sell is less than $50 you get a 5% Commission. If it's greater than $50 you get a 10% commission now just scrolling over my thing here. I think we need toe work on her guys a little bit. Our sales reps, or maybe girls, will have one two if I'm looking at correctly, that are over $50. So we already know about If statements don't me, let's write in if statement that will generate a commission based on our commission structure that we've set up for sales reps. How's that sound? So if I type equals V, I'm sorry equals if we want to type in if statement open parentheses. If this cell is less than 50 comma, multiply this same selling price. Times 0.5 If not, multiply this times 0.10 close parentheses. So let's just make sure we know I'm appear now. If our three, which is are selling price is less than 50 then are three times 30.5 That's our 5% commission. That's a yes if it's a no than our three times a 10% commission, cause if it's not less than 50 that means it must be greater than 50. So then give him a 10% commission. Let's see what happens. So that turned it into 0.67 to 5. Now this is currency. Here we can apply the currency and look. See it knows there's two decimal places, so it made it a dollar sign and put put the two decimal places there. Now we can highlight this. A copy highlight paced formula. Now let's just see if it worked. We knew we had to over two items that were over $50 so that would be 10% and $10.50 and that that's 10% to and then the rest of them. And you could do the math in your head. The rest of these are in fact, 5% commissions. So we drew in a ton of tools there. In this particular case study, we've done a lot of formatting with colors and borders and bold. We have done, Ah, very sophisticated data validation, which is a nice tool. I would even put it into, like, Intermediate Excel. So we use this data validation command so we didn't have to re type. Instead, we just created a bunch of drop down menus. That's really nice we created a series of drop down menus. Then we built this model so we didn't have toe look up thousands and thousands of different prices. Note. We just build a model based on a base selling price, a make premium and a year premium. And then we calculated a sales commission based on the value of the selling price. So if you put those together data validation with V, look up with an if statement that is those air very powerful tools in creating a model for your business, for your department, at work, for some sort of program that you're trying to develop these air. Wonderful tools. Plus, by the way, this looks really nice. We have nice color coding. We have nice borders. It's very presentable. You could do this in front of an office or staff or community committee. This will sell because it looks good. So here's all your tools. This is the end of the lecture. I'm going to give you this spreadsheet and a couple of the other ones we did so you can experiment and practice with him. Good luck. Thanks.
9. 09 Skillshare Conclusion and Project Intro: Well, unfortunately, we're now at the end of this skill share class. Microsoft excel from zero to hero in 58 minutes. Congratulations on completing this course. And congratulations on investing in yourself. I guarantee you that if you step through this class, if you've learned the information and start to practice it, practice it. You are now developing skill that will pay dividends over and over again. Whether you're out as a professional workplace, whether you're trying to start a business, your student, you're just working on your home finances. You have captured the fundamentals of a skill that will pay dividends over and over again. Congratulations. I sure hope you enjoyed yourself, and I sure hope that you've learned a lot. So thank you for entrusting me with the last 58 minutes of your video watching time. But you know, we're not quite at the end here in skill share. Now it's your turn to put these skills. These the Excel formulas, the formatting, the data validation. Now it's time for you to put it into practice for something that's important to you. And that's what the next section and the end of every skill share class is all about. It's a project. It's something that you can work on. So what you want to do it it's is diverse is the world itself. But what I'm offering here in the next section, which is the project, is I'm gonna offer you too Excel templates that I've built myself and I'm going to give those to you can download them and use them. If they're helpful to you. You can modify and add it. Add to him they're yours. You can do whatever you want with him. And the 1st 1 is geared toward a simple, home based business where each month and the months would represent columns each month You have revenues, you have sales you're bringing income in. And then during those same months, you have expenses. Maybe you're purchasing raw materials to make your crafts that you sell or your purchasing maybe digital assets or equipment or whatever it ISS. So you have revenues, money you're bringing in and you have expenses, money that is going out and then each month you have kind of ah, net. You know your net revenues. You might say you're your total revenues minus your expenses, and that is your net revenues. And then you do this every month. And then at the end of the year, it all tallies up on the bottom so that template would work. Great. If you're just starting with a with a home business that work fantastic. Obviously you can add rows. You can change descriptions you can do all. Format it however you like, make it your own. The second template. I've put together more for someone's personal finances. Maybe you're looking at getting out of debt. Or maybe you just want to do a better track. Better tracking of your assets and your liabilities. So the second Temple isn't asset and liabilities work. She so an s set is something that has value. Like your checking account. Your savings account. Your home. Maybe you have a vehicle. These are the things that are worth something. Your liabilities are your debts. So I have assets on the left hand side of the page. Your liabilities or your deaths, your mortgage, your credit cards, your student loan payments, car payments. Those air on the right. What? You Oh, so what you have as an asset minus what you owe your debts equals your net worth. So it's a spreadsheet for calculating your net worth. And if you do this every month, if it's your goal, to maybe get out of debt and you you Every month you duplicate this spreadsheet. So, in other words, you dio, let's just say January January's asset and liability and net worth spread she's completed well during January, you're making house payments, you're making car payments, you're earning money. And this Matt, well, February is gonna look a little different. Hopefully, little better and a little better March April May. So if you want to track your progress out of indebtedness, that would be a great tool to do it. So these are just two examples of millions that you could come up with yourself, and they're just starting points for you. So make this riel make these skills useful in your life. These templates will get just started or build your own. And the beauty of skill share is that you can post comments, feedback post the results of some of your project at the end of this class. Other students, potential students that might be interested or fellow students that are also taking this class can look at your work and feel inspired or help them understand better your experience in this class. So all of that helps. Thank you for your positive feedback. Thank you for your constructive comments. You can always contact me through skill share. I would love to hear from you. And mostly thank you for taking this journey with me. I count it as a privilege that you would spend this time in my class. So thank you. God bless you. And congratulations on advancing your skills.