Microsoft Excel Tutorial - Beginners Level 2

hi there Jamie Keith's here today a teachers tech hope you having a great day today we're taking a look at Microsoft Excel so this is a follow up video from my first video we're kind of going over the beginners of Microsoft Excel and this is still the beginners level but just covering a few more things all the different items that I'm going to be talking about in this video are listed down below in the description so just look at the description and then there what time stamp so you can jump around to different points to get to exactly where you want and make sure you hit that subscribe button in that little Bell notification then you'll get notified with my Nexus Excel videos come out also check for other video tutorials if you're looking for the beginning tutorial one check for the link down below or in the card up above so let's start this level 2 beginners tutorial in Microsoft Excel today

so the first thing I want to talk about to save you some time in Excel is the Auto filling sequence now this is super easy to do and all you have to do because a lot of times is common to right let's say at the day's the week and you don't want to have to type them each time so if I put if I just type the word Monday now Excel knows how to kind of help you out and kind of speed things along for you so what I can do is if I just click on this you can see there's the handle here and if I drag down and you're getting a little hint what's coming off with that little box on the side you can see it just knows right away that there's gonna be a pattern there and it continues through so remember a quick tip for my last tutorial was just those quick adjustments to the column so you can see a Wednesday doesn't fit just double click up here in and adjust everything to fit so if I was going to go to this one and I type just mo and it also knows that these are short for the days the week and we'll continue that pattern like so you can also use this for months so if I type January so it's just even in the short form I could do it in the long form too if I go ahead and just pull it down it will go know who to continue on through it so that can save you a lot of time when entering these and also you can go ahead and if I go with this one if I type I'll just type Monday again or mo n you I've been showing you pulling down but you can pull a clock across to the right like so and so if you're going across that way with your headings it's very easy to just to pull through there I'm just going to delete those now you can also do this with numbers if I just had one number it doesn't recognize a pattern all it's going to do is copy down so if I go ahead and use this and pull down it will just copy the same thing over and over again but if I give it a pattern Excel knows this and they can then it can guess what's coming next so if I put one and then I just put two in here and I know that the increment is just going to be increasing by one you need to make sure you highlight both now and then you can see how the a little icon changed if I drag this down now it will escape Inc the increment will keep increasing by one but if I had a different pattern let's say if I used five and then I put ten in here and then I highlight both so again make sure you highlight both and then drag down you can see that it recognizes the pattern like so now in some other things you can do also I've been just showing you pulling down our to the right with any of these two so if I was gonna I'm just going to delete this and I'll start from our fresh one if I was gonna put Monday I'll just take Monday notice if I drag up it will go backwards so it knows the pattern that way and I can also drag to the left so you don't have to be stuck with I think you can only go one way and it works with numbers too so if I go and put my 1 and I'll put my tooth to make sure it recognizes the pattern and highlight both of these you can see how it will even go into the negative numbers like so and I can do the same thing if I was going across this way it will recognize this pattern make sure you do both and I could drag it back this way and you're gonna get into the negative numbers so those are just some quick tips for autofill I'm just gonna show you real quickly how to make your own custom autofill if you needed to add something like for example names so what you can do is if you know

there's lists that you have that you wanted to create to save you some time you can actually add them to autofill so this is how you do it now I'm just going to go back up to file and click on it and when I go back I'm going to go down to my options here so with options opening up I need to go to advanced and then scroll all the way to the bottom and then what we're gonna have is edit our custom less so I'm just gonna go ahead and click on this so what I could do is go ahead and then just add them name my name so if I was going through you could see I could just write my names down here and I could just hit enter and I could go through and then just keep adding them like so a different way you could do it is just import them so if you did already have them typed up so if I go ahead and click this so let's say this is a list here and I'm just going to highlight and hit enter and then I'm hit import you can see how quickly they came in on it so I'll hit okay and then I'm just gonna hit OK again so in this case if I started typing if I wrote my name down you can see that as I drag down it auto fills with what I created so you could do the same thing with departments if you haven't typed up you can just pick that area or you can just go and type it in one by one but that could save you some time when entering if you are typing the same list over and over again to use that autofill function so now I'm going to show you

that powerful sword feature inside Microsoft Excel now I have the top 10 grossing movies of all time here can't believe endgames gonna end up on the top of the list it's only been out for a little while here but what I need to do first is I'm gonna highlight this list so I'm just gonna go from here down to the bottom and I'm gonna sort this and I'm going to show you the quick sorts but it's not going to give me what I want right away so if I just go to the A to Z and click all it does is put it in the alphabetical order you can see from Avatar Avengers and it just sorted this out all the numbers stayed correctly with it so it's sorted out all the correct rows with it but I want to make sure I get this with the value this being the worldwide value being the thing that I want to sort it by the other quick option is the Z to a you can see but it's not what I want what I need to do is go into custom now I'm gonna go up back up here again and I'm going to go to custom sort so when I I have some options here notice that my data has headers so I'm gonna collect this and that's gonna be in the top one but right now what I'm gonna do is sort by well I want world wide so world wide and do I want it on you can look at the sword on well it's going to be values on this one so I'm gonna leave that a values do I want at large as the smallest their smallest to largest well I want endgame to be on top so I'm gonna go largest to smallest I hit okay and you can see a adventures endgame is now on the top I have all these sorted out through it just the way I want so it's a quick way to do that customs search now you can go add more levels to your sort I said search but I meant sort let's say if we go to these two right we'll go between yeah these two right here so if we are gonna go I'm gonna copy this down so now Jurassic world is a little tied with the Avengers and I'm just gonna make sure I'm gonna change this right here so I'm going to change one number and you can see when it's domestic actually it's a little less so what I want to tell the custom sort is sort by worldwide first but then sort by domestic and how you do this so I'm gonna go back up here I'm going to make sure I have my what I want selected and I'm gonna go up to my custom sword again custom sort and this time you can see I have my one level here I can add another level so if I go at a level and this time for the second level I want it to be sorted by domestic same things cell values still wanted largest to smallest now what should happen is Jurassic Marvel the Avengers should change place so if I go ahead and sort it you can see they did because these were two were tied already so it put them in order so it went to the next level to check this sort so remember you can add those in other levels to your sort to get very specific in case there's the things that are the same so make sure you take advantage of this of the sort feature inside Microsoft Excel so I just showed

you how this sort feature works if you like that you're really going to like the filter option inside Microsoft Excel now I'm going to use the same data I know this is a small set to work from but it shows how things can work very easily now I'm gonna go ahead and add a filter to the top of these I made a space in here so I'm just gonna highlight I could highlight the whole row but I'm just going to highlight where I want the filters just through there so I select where I want it I go back up to you see sort and filter and I'm gonna go ahead and click filter and now you can see all these dropdowns are added to above each one so what this can do is real quickly I'll just show you from the first column here where the title if I drop down you can see everything is selected if I just wanted to see one of the things so if I go ahead I'm gonna go and deselect everything and I just want to see Black Panther and I go ahead and hit okay and then it just filters everything out but based on the one I had so if you did have a duplicate of something in it it's not going to show up twice on the list but when you do your filter it will show you in all the duplicates so if I had two Black Panthers it would show it underneath so I can go back to it and I can select all again and add it and then they're back how I like to use this in this case would be maybe under studio you can see that I have different studios here if I wanted to see maybe how Universal is doing so I go ahead and click under this column now and I'm just gonna deselect everything and I'm gonna pick Universal select hit OK and it just shows me how different things are doing within that so you can quickly see get that picture of the exact data that you need so you can analyze it even faster so I'm just going to put this back actually at any time you want to clear any of your filters you can go back up to here and you can see that we can go ahead and hit clear and everything goes back to normal so let's look at a few other options inside the filter here I'm gonna work on the world wide here so if I drop down you can see again all the numbers but let's do some other things so let's do some custom so if I go to number filters you can see as I go through I could go in pick I could pick greater than so I could put a number in between a number I could do above average so let's just see which ones are above average so when I click it found the average and then it's going to make sure it shows me all the ones above average so I have five of them that are above average there and again I can go back and I can clear these here so I can go back to clear and then I can drop down and I can do other types of filters I'm not going to show you all of them but once you see all the different things you can do if you want to do a custom let's say if I wanted to be if I say is greater than so if I go here is greater than 2000 and then I'm gonna say is less then 2,500 these numbers are actually all in billions I don't have the billion numbers so but that's why if you're looking at them wondering why those are so small they actually are representing billions I just didn't write that on my table so I hit ok and you can see now it found all the ones based on the filter that when I customized it I said everything within this range and I can see Titanic Star Wars and The Adventures all fit within that so the filters and another powerful way to sort through your data remember word it clear where to add them and the custom one I really like doing the custom to find exactly what you need and if you have large lists like I said this is a very small list I have but if you have a large list you can find the exact things that you need we can save you a lot of time by using this filter function so for this next lesson I just

want to show you the routing function so this could be handy if you have a large set of data that you want to apply some rounding to and I'm just going to show you the three different ways that you can do this so if I was going to go to round first and remember when you're starting these functions I'm just gonna put an equal sign in and I'm gonna start typing and round in this one so I'm gonna click on this one and it says I need to have the number and then a comma and then the digit so the digits going to be how where it's going to round to and I'll change it in a moment I'm just gonna start with zero but I click on this and then my comma and then it's just zero so then I'm just gonna close that and hit enter you can see it took the four point eight three and put it to five I can copy this down and you can see how everything round so in this one it was at eight point one it went to eight for state of four six point three two six and nine point five to ten now I could force everything to round up so in this case again go back to my equal sign first I'm going to start typing I'm gonna go round up so I click on this one click my number comma and then zero again and close my bracket and you can see we'll see which ones are different here this one went up to nine because we were rounding up on it even though it was at eight point one and the same thing with all of these down here and lastly we have our round down so if I go ahead and type this one in and then click again on it comma zero and then like so you can see how everything rounds down now what I'm going to do is just change the 0 you can see what it does so if I go over here and then you can see the formula straight up here and I'm just gonna go and make a change in put my 1 in there and now it's to the one place so if I was going to go back to it again I'm gonna just and maybe I'll put 3 and you can see now where it's rounding to so it makes those adjustments I could copy these all down like this and then change each one I would have to go through back I'm just gonna go ctrl Z back and I'm just going to show you a quick way that you can make an adjustment if I highlight everything like this and I'm just going to go to ctrl F which is the Find and Replace and I have the highlighted area and what I'm gonna do is find what so I'm gonna say everything that is comma 0 I wanted to change 2 comma 2 so I'm just gonna go replace all and hit OK and close and you can see now it went through and changed it turned that 0 to 1 so that's just a quick way to use the Find and Replace tool just a little tip that you can use it in other places too but do remember that's there for you so that's the round rounding functions hopefully that can help you out with some of the data that you're working

with so right before I start this next part I just want to mention I have a different video coming out about the shortcuts in Excel for beginners so take a look for that in the description down below for a link or in the card up above but so for this part I want to try to explain cell positioning with relative and absolute and why you might not need to be using this inside your spreadsheets so I'm going to start with relative here so I'm gonna go and just create a simple formula for this one up here and if I go ahead so I'll put my equal sign and I'll just click on this and I'm gonna multiply this just by a 1 here and I should get 10 so if I copy this down you can see all the simple multiplication that takes place through these I'm gonna check each cell so as I said if I go through and just hit f2 you can see it's B 1 times a 1 if I click on a different cell the next one at f2 you can see it's B 2 times a 2 and then it's B 3 times a 3 so it's shifting down and it's changing both places so if I went and copy this entire row this way you can see and the numbers get big pretty quickly but if I go and click in a spot again and I just go back to and hit my f2 you can see it's d4 c4 so it's shifting over to the right now when I use absolute I can tie it to a certain place and that's what I'm going to show you in the next example I can change which one I want to be relative in absolute alright so now what we're gonna

do is add a dollar sign to our formula and you're gonna see how it turns into absolute positioning to we're gonna do column a so if I do my formula again click and I'll just click here and get my multiplied by this and what I want to do now when I got this selected I'm going to use f4 so if I hit f4 once you can see the dollar sign means absolute a absolute 1 so if I hit it again you can see it's a absolute 1 there and if I hit it again it's absolute a 1 so this will lock it into position for that first column and you're gonna see what happens when I do this so if I hit enter you're not going to notice any difference with the equation here because everything's just locked into place but now if I drag this over to the different spot here what you're going to be able to see though is how it differs here so if I let's drag this a few more over here too so I'm gonna drag this just over a few more times and if I check so remember hitting f2 I'm just going to go ahead and hit f2 notice that it didn't move out of the column a it's locked into that now the other one is relative as I move across you can see now it's in d7 that is multiplying but I've locked it into this spot so this would be the absolute a in relative one that I picked before you can see absolute a relative 787 on this one so that lock that with that dollar sign in front of that one that locks it into the road let's take another look at adjusting it to a

different positioning so let's look at absolute here so if I I've changed up my numbers a little bit here but if I go to this cell and I'm just gonna do my formula again this time I want this and I want this multiplied by this top spot right here so a1 but I need to change that a 1 so what I wanted to do is I'm going to hit f4 and I want to go absolute so I'm gonna hit enter and now it's 7 times 2 is 14 you can see that's what we want I'm gonna drag this down here and then at that point if I check these we're using f2 again I'm gonna hit f2 notice that this one moved down on it so we have a 6 is moved down from over the top it's moving down but a 1 we made that absolute that stays the same on all of it so even if I was gonna copy this the other way across and if I any of these I click in and hit f2 again you can see it stays in the spot so if there's one number you want to adjust so if this number was changing to something else so if I go ahead and adjust this I'll put 5 you can see how everything adjust to it because that's the absolute reference to part of that formula part of that equation that I created so lastly with

this reference in cell referencing I just wanna lock a row in place so what I'm gonna do is create my simple formula like I've done before and I'm just going to put my equals and I'm gonna multiply this here by this so I'm gonna go to my f4 key and I'm gonna click it that's my absolute because the dollar sign shows that it's absolute for both but I don't want that I want just the row so then this would be the one I want is it's going to be a absolute one here and I'm just gonna hit enter on this one so if I go and move this across now you can see how everything works but now if I was going to take this whole row and move it down now if I can check with my f2 functions so if I go through here f2 you can see how this is moving here but then notice that the row is still up and c1 here so it's the relative to grow the row is absolute to it and everything else is varying as I move it down but this stays in place so if I click on this one over here and hit f2 you can see the formula the two that are connected on this one so those are just different positioning depending what you want remember to use the f4 key the dollar sign in front will mean the absolute if there's nothing that's going to be the relative so if you get if you set up your spreadsheets with it correct it can save you a lot of time when managing all of your data so I hope you like this tutorial here this level 2 beginners tutorial as I said I'll have some more coming out let me know in the comments down below what you're looking for and I can create some more I'm looking at doing some on pivot tables and a few other ones coming up here but do let me know if this is useful for you thanks for watching this week on teachers tech I'll see you next time