Lessons

Lesson Info

3:00 pm - Auto Sums & Inserting Rows

So let's now I'm gonna go to the to the max side and I'm going to open up now to auto some so day one session for auto some I need to see here it's essentially the same exact thing we've been working with but auto some is going to save the day so we don't have to do it manually we're going to do it automatically okay, so I'm going to show you a few different ways to do it and then we're going to show you the even faster way to do it all right so how do we do this? Let me just go ahead and zoom in a bit so auto some number one is gonna exist as this little icon right there you're going to see that it's like a little sigma for summing up so you should see that right there from the pc it should also be kind of in the upper right and you'll also notice that it has a drop down also that shows you other things that you can in fact to automatically so auto some is just going to just save the day so let's do auto someone a couple of different ways let's first just click on see ten because that...

's where I wanted to end up so I'm going to do now is simply click on the auto some icon and all right I see oops what happened there that should just come in automatically. Okay. There. You all right? So now what it does, is it smart enough to say, hey, you know what? I think this is what you want to add up? Because there's a bunch of numbers above me. So I'm just gonna go ahead and put in this formula and before you actually show you anything, I just want to confirm so it's kind of like, ok, great. And then it shows me equal sign some open front, deceased e five thirty nine closed parentheses all that stuff that it took us a long time to do on her own. X l did it for us. Let me do that one more time. You do that, click on that. And then auto some that happens. And then one of finished, I simply hit enter a return and the math is done. Okay. And again, if we look back at our formula, we will see there it is. All right. Like magic. Now, another way I could do this is to simply highlight all the things I want. And they give a little space in terms of er wanted to go that's actually going to save me a couple of steps. Because I know exactly what I want is gonna highlight everything go down to here and then I do auto some and then bam just gives birth to a little baby boy. Twenty six thousand pound baby boy. Okay, and then there we go. All right, now, a faster way to do all this let's go ahead and just get rid of both of these. What I'd like to do is actually d'oh all four of these at the same time, so you could probably guess I'm just gonna highlight all four of them and make sure you kind of get that extra row in there because you want to kind of give it a little bit. Reinforcement comes where you want it to end up. We're going to see the same thing when we add up these guys. So let's go in and do that. Highlight everything to auto, some bam done. And that could be for any amount of columns that you have and you'll be able to do that. Okay. All right, now, let's, undo that one more time. I'm just going to command your control z and I'm just going to highlight now not only from sea five all the way to see tan, but I'm gonna go all the way across to column g. So now I've left a little bit of open space for all my formula to land so I can kind of give birth to all these things and basically telling excel this is where you should put yourselves right? I'm giving a little bit hence so I'd like to think about this is kind of like you're sort of siphoning the hose a little bit where you're saying hey let's let's do this thing right to that to those parts essentially okay, so and all you do now is simply hit some and that's it and see it does it for all of them okay, so I think that's pretty extraordinary and knows exactly what to do, which is one click any questions on that people here in the studio you felt feel pretty confident that everybody is looking good, okay? Good and now it's too um average? Well, actually we'll have to kind of readjust it because because number because they're not kind of continuous with each other let's do average this time let's click on c twelve and we're going to click on the drop down this time instead of this the signal itself we're going to look on the drop down and we're gonna say average and see it does that it kind of takes all the numbers above it which we don't necessarily want to remember I showed you in the previous segment we can re kind of sculpt this selection but coming right to the corners you see that where I am if you get that kind of diagonal arrow and just click and drag and do it that way now I have what I want selected I do that and that's done I can go out and do the same thing clicking and dragging across and that's done as well okay, so looking at the formula can save you a lot of time you can see equal sign average they're all that kind of stuff that we didn't have to do that so big big timesaver working with the auto some feature so let's just go ahead and do now maximum minimum go ahead and say minimum for this one I'm gonna have to adjust this one so it could be a little bit trickier because they're not next to each other I do that ok? And now what I'm gonna do this time, huh? Um let's go ahead and I'm going to copy this just so you can see how this works. You can also copy formulas I wanted to type it and I was going to highlight this formula for minimum just do command see how just a command cr control see and then come over to hear when I hit the escape key after I'm finished with it pace but then just changed this to max okay, so essentially just text if you think about it I just copied this and then pasted the same thing in there but I just changed minimum men to max because it's a lot easier to do that than have to like re sculpt at our selection allover again because that was a bit of a pain to have to do that so now all I gotta do is just changed a couple letters and I'm good to go okay? And then you could probably guess the same thing now with our count count numbers all right, so essentially the same thing okay, so really, really nice feature you'll use auto some a lot a lot a lot um so let's see any questions up until this point out auto some nothing from their college is looking good. All right. Um all right, excellence um let me see where we can go from here. Let me come back to our files. All right? So we've done the basis. Yes. Uh, is it very important to check your spelling? Like if you kept looks on when you're talking in formula or if you have a space between is a very important to follow the rules or okay that's a good question. So the the capital's do not matter inside formulas the space bar space may like if you have a colon space and things like that it may actually affect it but sometimes not it really depends on which part that you're doing which part of the formula you're doing but the caps don't matter at all it recognizes the same thing and eventually put it in there is caps thank you okay all right so we're going to go back to our files here and we're gonna work with actually we're gonna go back to this inserting rose right here this is actually from session three so I want to show you this actually should be session for so that's fine we're going back to here and then just go ahead and double click and open it up and we're going to kind of switch gears a tiny tiny bit and we're going to do ah few things number one I want to introduce you to something called absolute references when you're doing formulas remember before we were talking about how you can click and drag the auto fill it allowed us to do that because of relative references. Now we're going to do an absolute reference because in this case we need to do an absolute reference if you're going to see exactly why but we're going to try it first as a relative reference and you're going to see it's going to be all messed up and what it's messed up we're gonna have to figure out why it's messed up because we're going to do things the way that we sort of I always do it which is just a relative reference and now we're going to do it a different way but you'll also see a good example of how you might be using certain formulas so we have our four people here we want to figure out how much commission they should get so we have these four totals and we have their commission rate off four percent so that should be very easy formula which essentially going to be this times this is gonna allow us to know how much commission they should get all right pretty straightforward so then we're going to be all fancy about it and we know auto fill and we're going to see wow aren't I cool and they're going to see how that backfires because they were going to learn about absolute references all right so how do we do that so let's go tio r formula so we're gonna go ahead and just hit equal sign and then we're just gonna add it what our formula is gonna be so it's gonna be a tch five multiplied by which is just going to be our asterix and then just simply click here on I two hit enter a return and then beautiful magic that's amazing right it did it for me that's so great and I wanna be able to do it to the next ones as well so how am I going to do that very simply just do my auto fill right it should work let's go in and do that oops oops soups soups auto fill where'd you go okay auto why you sliding away okay there we go I do that and then what is going on here? Okay, why didn't do that? What does it look like? This not pretty is it you stump the students and he guesses remember it's a relative reference yeah I think it's not enough space you should drop well that's for this one potentially so go ahead and click on each individual cell and students at home also click on each individual cell and analyze what's going on here h five times I too oh yeah that makes sense xs on this one h six times I three so I'm multiplying this twenty four thousand times nothing I guess I'm getting nothing I mix I guess that makes sense that's what I asked it to do because it's a relative reference it moved it up one more row but let me just make sure what's going on here oh okay so I have eye to eye three and I four because it's a relative reference excel automatically assumes that you want to keep going down another row as you do your auto fill down this is an example when you want to do an absolute reference an absolute reference okay, so let's go ahead and um undo all that's seven inches highlight it and we can go ahead in tow and all we gotta do now is run the equal sign ok, which is going to be a five multiplied by I too but we're going to do now as we want to make this an absolute reference excel knows that something is an absolute reference by these little dollar signs it puts in there and we're going to do this manually. All right, so it's gonna be a dollar sign I dollar sign too that's how are you going to do it? And there may be a shortcut for this on the pc it's f or this is another version for rewarding use usage for f for um and it may be a foreign this to let me see look into that so I click on this and you f or now only f four comes up okay, so get away ok? Multiply this times this and then again it's gonna be okay so now understand what it was an absolute references. I like to think about an absolute reference as like an anchored reference that's I like to call it more of an anchor reference than I do in absolute reference because basically anchoring to that I too so when I drag it down that stays anchored down and it always remains as I to it doesn't go I three eye for eye five et cetera so I'm going to go and click on one at a time in a tap on return on my keyboard and that's fine that's exactly what I got before, but then I'm going to click and drag down to do my auto fill and it knows now toe anchor I too, for both of us for all of those coming down so it knows to stay as four percent and that's because of those dollar signs dollar sign, dollar sign in between to those okay, so if you're just beginning so you may not see those very often, but you may start to because if you're working with people who are already a little bit more advanced, it could help you understand what's actually going on why they would choose an absolute reference for this. All right, let me just show you on the pc side of things before the f four I don't know there's a shortcut key on them on the mat on the mac to do the f or me to show you on this side and hopefully it works. Okay? All right, so again, I'm just going tio tio equal sign h five times excuse me, I too if you're on a pc at home, try f or it might actually work on the mack also on your mac, anybody getting the eff ort of work okay it's something that I could certainly look up and get back to you on that but if you're on the piece of the f four should definitely work this keyboard that I have because to a platform it has other purposes other features for the keys so those star kind of dominating anyway, so that means I have to just type in my dollar signs those silly cem cem good review dollars sinai dollar sign and now I can just double click and that just come straight in okay, so that is going to be working with absolute references and you see how it comes in nicely okay, so that's absolute references in a nutshell, just some basic absolute references now we're working with this particular spreadsheet I want to show us how we can just insert rose and insert columns and things like that so somebody asked a good question earlier just about if you're getting sort of row, how doesn't mess with things so this is going to be an example of how to actually do it, so this might be some review for you. All right? So how do we do that? So very simply we're going to go to our rows over here on the far left and we're going to right click on the road that we want to move down so we want to move good old wink martindale down below so all I do that is simply right click on his robe and you going to see I have insert that pops up on automatically inserts that david way just got a message from david t in the chat rooms he said teo, try apple tea on the mac he just googled in f or definitely does not work on the mac is what david is saying okay doing and saying that what do apple tea teo apple tea yes ok, great. Yeah, I know there's there's got to be one out there I just don't know it something come back too here um I mean, actually just get rid of this and we're going to just say that and then apple t lovely thank you, david okay, so that's apple tea. Okay, very good. Sounds delicious apple to cover some of that right now. Okay? All right. Very good. So that's that's in my memory banks good. You know, not all things they're super intuitive like with demonic, like four eyes it f or apple tea why's it apple tv rights just sometimes we just have to use our brains and remember stuff but thank you for that that's awesome. Okay, good. So we're gonna come back and we're going to do are inserting of rose we already did that and we can see we have a new road right there. We can go ahead and put all of our stuff in there really straightforward again. We just right click and then we have all that stuff here, so ah lot of people, your instinct might be to do tow highlight right here to do that first as opposed to write clicking on the number row and most people when they're first starting will do this way. Right click and notice you get this insert dot dot dot and then you get a dialog box and it says, ok, what do you want to do? What you want to shift cells right down tire row? You know, I don't know too many choices. Oh, my god. So you know, I could do this one and it's like okay, just did that that's great or I could very easily just right. Click here and choose answer and it just does for me that's. Why I recommend people just by clicking on the row on the far end of the road to be able to do that as opposed to highlighting everything is just going to save a lot more time. Lot more confusion. You know exactly what you're going to get right on dh then if you wantto insert a column that's very easy to do as well so I want to do a column between h and I I'm going right click on I just insert that column and make I move over to the right hand side so it's candice because the other one was moving moving it down this is moving it to the rights how I do that very simply right click on I and there's insert same exact thing and notice that keeps the formatting also its very smart you're inserting a new row however it maintains that there's those borders there for us to work with. So those are some nice um some nice features to work with and there's other ways you could do some of these things like you have this insert and you could do some of these other things, insert cells and start rows and columns but I my approach is that it takes too long to remember where they are on the toolbar and then and takes too long to actually execute these commands. But if you are more of a tool bar person you could always come up here and do those things number one and number two you know it's really just up to you, whatever your comfort level is, you know whatever you like you like to work with so those your options to do that on the mac it's going to be the same? The same thing