Microsoft Excel Tutorial - Beginners Level 4

hi there jamie keate here today at teachers tech hope you're having a great day today today i'm continuing on with my excel lessons for beginners this is the level four this is the fourth lesson that i've put together i'll put a link down below in the description to the playlist that contains the other lessons too today we're going to be looking at three different topics in this lesson and the first one is going to be conditional formatting and then we're going to move on to data validation or drop down menus and lastly i'm going to show you how to add a slicer or slicers to your information to be able to filter it quickly in a very attractive way so those are the three topics we're covering it today everything will be time stamped below so if you want to skip to a certain section of the video it will be there let's get started today on teachers tech with this lesson for excel beginners i'll link the sheet that i'm using here today just down below in the description too so if you wanted some data to play along with the same stuff that i have it will be there that you can do it so first of all what

is conditional formatting our first topic today and uh just really easily what it does is it allows you to spot trends or important numbers visually so i already have this set up with some conditional formatting and you can see if i look around there's some down red arrows there's some up ones usually it's picking you know depending on what we told it to pick uh it will show kind of the trends in the upward or downward direction or really uh you get to choose what the criteria will be now i'm going to go ahead and then just delete the conditional formatting that i have on this sheet and we're going to start from scratch

so the first thing i'm going to do is actually clear the conditional formatting the rules that i have applied to this information here now to do that make sure you go you're up to home and there's conditional formatting here so if i click down i'll click on this and then go to clear rules i could select the information in the table and then clear the selected cells but i'm just going to clear everything on this sheet so when i click on it all the different rules are gone out of this now so let's start applying some conditional formatting to a different sheet i have and i just have assessment i just have the quarterback names in here and they're doing some quizzes and some uh tests and some mint terms here so i'm going to take a look at this first column and what i want to do so the first thing you're going to need to highlight the area that you need to apply the conditional formatting to so i'm going to i could highlight it like this another way you could do if your lists are getting longer what you can do if you hold ctrl shift and then hit the down arrow on your keyboard it will go right to the bottom one like that so remember that shortcut so now what i'm going to do is just go to conditional formatting and we're just going to work through the different types that you can apply and i'm going to look at the highlight the cell rules and i'm going to go greater than and now if i click on this greater than and it just defaulted to 77 but i can change this so if i was said oh i want anything greater than 95 well maybe i don't want red either and i can drop down and i could select green or i could do different things i can make it could be a text color a red border but to get more you just have to go to custom format and then go through and if there's a certain number uh that you want a font a border a fill you just go select it the ones you want and then you'll have your custom one that you can apply to it now i'm just going to hit okay and i have three different colors added to it so let's say though um someone else entered the class i'll just put my name here and i go ahead and get a 97 whoops not 67 97 and this should turn green if that cell was formatted i didn't format the cell so the one thing you can do is i'm just going to delete that what you can do is if you highlight or just click on the entire column like this you can apply the conditional formatting to it so i'm going to go back to greater than and i'm going to go to 95 again and i'm going to go back to my green and hit ok so let's say now anybody that i add after that you can see it turned green but you probably don't want this to change color change the color on this you can go to this one go back to your conditional formatting and then clear rules and it's just going to be that cell that i have that i'm going to clear the rules to so now it's back to normal and anybody i add that format will add to it so i'm just going to ctrl z to get rid of the last couple things that i did here with my name on it uh the other thing too so we could be looking at a number of different ways now uh if i highlight this row or maybe multiple ones i could go to conditional formatting i could highlight i could go back to my greater than uh rules and then look at it i could change the number uh maybe a lower one maybe a higher one i'll go to 98 if there's one there and it's applying it to the two all the different ones that i've highlighted there i'm just going to go cancel and i wanted you to take a look at and i'm just going to go to one here and under all these and this goes the same with no matter which one i pick there is the more rules button at the bottom and when you click on more rules you can see that you can go format cell specific text and you can go through and make some changes to that right there too so if you wanted it between but these are all going to be through here too so you can see all the different ways that you can just quickly get the information so if i wanted less to make less than maybe i wanted as a certain number to pop out if uh it was less than something so if i was going to say uh we'll go 65 well no one got 65 we'll say 70. and then i noticed two are kind of highlighted so it can give that identify that identification that oh let's take a look closer what could be happening there and if i have more information and then i can see those trends using conditional formatting so i'll just hit okay on there okay so looking at a few different options we have are greater than less than i'm not going to between self-explanatory equal to text that contains so what this can do uh if i'm looking so if i'm just going to highlight this if i go back highlight text that contains so if i click on it and i start typing tom and you can see it found tom brady here and you can look up different so you will be able to look up names and highlight names add that conditional formatting just with text 2. i'm just going to hit cancel on that one the other thing that you can do and i'm going to write tom brady again here and if i highlight this go to conditional formatting look at uh so duplicate value so if also you've typed in something two times and you didn't know if you just wanted that quick visualization uh you could click on it and you'd be like oh i typed his name in twice and you can see if you have that formatting there it will let you know right away if you've already typed it in too so just a little tip there with that you how you could be using it and lastly i just wanted to point out there is dates too so i don't have a date in this one but you could be looking at uh when the dates happened uh to be uh coloring or highlighting different ways to it all right so let's move to another sheet here i'm just gonna move over to uh qbr here and let's say we're going to use i'm going to use my shortcut here and highlight that take a look and we also have top bottom rules and this could be the top 10. so if i go ahead click on the top 10 um maybe i again don't maybe i want green or i could go to custom format there's my top 10. i could adjust this you can see as i bring this down it's about picking the top like the top six now qbr ratings like so uh the other thing i'm just gonna hit cancel go back top 10 rules i could go top 10 percent on it too and again adjust the rules accordingly to what you want from it above average below average and again go to more rules to adjust things and get things just the way you want that those colors to indicate to you now i like it as we're moving down here we have data bars i like uh when we add these uh to it if i just click the solid fill maybe i can make my cells i'll make this a little wider so you can kind of see it a little bit more uh through here it fills up the higher it is so i like it when we can add these visuals like this so if you've ever wondered how to add those that's how quick you can do it with conditional formatting again you can go to your more rules and make your adjustments pick the colors the type of fill that you want in here it to really customize everything that you want i'm just going to go down to color scales now color what the color scales do so i'm going to go back one step and take uh just take these off here and i'm going to go to color scale so color scale will kind of break it in you know it's a breaking into different uh parts kind of the green would be the highest the green the green is and then i depending on what you want again you can customize all these so it's going to break it into you can see five different colors and kind of separate it through here as i go through i'm just going to give you a quick show and again more rules are there the last part of this i just want to point out the icon sets here if i uh i'm going to go back to my this is the sheet i started with the sales data and i'm going to highlight everything so it's different if i highlight one at a time so you can see if i go to this all it's doing is looking at this row across this way here now if i wanted i'm going to highlight everything here and go back and do my icon sets here uh you can see how uh how differently things are so if i you can see how the arrow has changed because it's basting basing it not just on that uh row across anymore it's basing it on all the numbers that i picked so if i highlight everything it's looking at all of this and comparing the numbers uh in these ones so just so you know and the other thing is if you um if you apply let's say and i'm going to go back to uh this again if you drag down i'll show you notice everything changed now to the exact same thing the reason is because if i go back i'm going to go to manage rules here and what i wanted to point out here was when it applies by default everything i will be absolute there so if you look at the different uh you see the dollar signs in there and i talked about this in lesson two it as i copy it down it's not going to move so that's why if you go to copy something down uh from a conditional form and it defaulted to that uh audit so just do be aware if you're trying to copy it down it will change on it all right so that's kind of a walk around conditional formatting you can see from the clearing the rules we can go to manage rules if we want you could add formulas uh in if you're working with it but it gives that really quick way to visualize your work uh and those in the trends like you can be using in so many different ways if you're showing a spreadsheet to somebody else they can just quickly pick up on those important numbers so i hope you like this first tip with conditional formatting let's move on uh to our next one now i want to show

you how to create a drop down list in microsoft excel using data validation now this might sound complicated but even if you're a beginner to microsoft excel this is still something you can add very quickly and easily to your spreadsheets to add a lot of functional functionality now what it allows you to do is quickly go down a list like this and pick and you know you're not going to have to type things in and quickly uh just kind of pre program what you wanted to say in those boxes and i'll even show you how to make some adjustments to it if the list changes so i'm going to show you two different ways today

today so i have some pretty simple lists that i'm going to be showing in this example today just so you get an idea how to create these but you can apply this to any type of sheets now in this first example what i want to have happen is so i have a list of sports and when i use my drop down i all i want to be able to select is it played indoor outdoor or both on it so this might not be the most accurate one when i select what the answer is but you'll see the point how i make it now the first thing i need to do is select the area where the drop downs are going to be so right here in this area i'm going to go and highlight these here and then all i need to do is go up to data make sure you have that selected you can see where it is up top and find data validation and it's right here i'm going to go ahead and click on this one and now i have this little window the data validation window pop up and you can see right now the cells are set to any value what that means is you can type any number words letters gibberish it doesn't matter you can put anything you want but we want to be able to force it to only select uh select from the drop down list on it so what i want to do is if i drop down and then pick list right here and you can see there's other options but in this video i'm just focusing on the list and the drop down but let me know if you're looking at some of these other ones too if you want a tutorial i'm going to go ahead and pick list and now i can give my options what i want so i said is it going to be played indoor and then i put a comma on it outdoor so depending on what your list is going to be and then i'm going to just type both so those that's my small list on it just to give this example and i'm just going to go ahead and hit ok now so what happened was this little arrow uh popped up here so if i drop down you can see indoor outdoor or both so if i go ahead and pick football both um we'll go to golf and if i'm just thinking about game play we'll just go to outdoor again i can pick different examples depending on this but you can go through and pick these and uh just kind of quickly go through and you know you're not going to make any spelling errors because you've already told it what it wants to be now this is a simple way to create a drop-down list and the next way it's the way i prefer so keep on watching on that one and i'll show you how to kind of make a list on the side that you can type in and increase it based on a certain range on your spreadsheet so i'm on sheet2 now and in this case i

want to create a drop down list when i come over here i drop down and i want to be able to pull from the information that i typed over here but i also want to be able to show you how if that list changes or grows how you can quickly add it so the first thing i want to do in this case and a lot of people kind of over uh kind of don't do it this way but i think it's better i'm going to create a table over here so i'm going to go ahead and highlight this and just go up to insert table and click on that and i do have a heading on there so i'm going to say my table has the heading i'm just going to hit okay just like that so i've created a table from there and i'll explain why i'm doing it i'll show you uh a different way that you can do it but i just find it adds a little bit of work if you're changing around the table now this is very similar to what we did before i need to select the area that i want the data validation in so i need to go highlight this area and then go up to the data up top find my data validation now this time i still need to change it to list like i did before but i need to change the source on it so when i go ahead and click this little arrow here i can highlight the source so i wanted it to be limited full exempt and then i can go ahead and pick this and now hit ok so if i was going to go to practice status and you can see limited full exempt it's pulling from the list beside it so i can go through from this now let's say if something changes if i want to add something to this list if i go in and i'll just give an example here and maybe the person's out so i'm just going to go and type out and right away if i go over here notice it's out on it now if i didn't create that table at first that would automatically happen there'd be a different way so if i just go back a bit here and i'm just going to go back and take off the table so the table's off i you can see that it doesn't have the drop down it's just back to normal information in the on the sheet now i could go back here and i'm going to go uh this time i'll just go back to my data validation again and i'm going to pick this right through here hit enter and hit ok so now if same thing it seems to be working but now if i type this like this time and i go through notice out isn't there what i'd have to do is the way i would want to put out in there i'd actually have to put it in between uh what's there so i could add if i added a i'm just going to insert a row here and i'm just going to cut this and paste this over here so now if i go back over here you can see out is there and um and the way i had it set up here i put a spot in here so i'd have to move everything out so i just find that just adds a little bit extra work if you create that table at first it was just easy to add and keep going on there now maybe uh if you were sharing this sheet out you wouldn't want people to see this part here you can go ahead and hide this spot so you can right click up here you can see the hide option um and maybe so then they would just see this here as they go through but as i said i would recommend just create that table first in the first part that i showed you can also create uh it in different places so if i wanted to have the information in a new sheet so if i just go back here and put this back here i could create this information i could take it and so it wasn't even on this sheet so i could just go down below maybe i just pasted here but the one thing you'd have to do if this is where your information is coming from you would have to update if i go back in my validation you would have to make sure that you go back here and update your source so if i anytime you move this if this is going to be my source now i'd have to update it here and you can see it's on a different sheet especially if you've moved it if you started from the first place it doesn't matter but you can put it in different places to maybe hide the list that you if you were sharing it to and if you didn't want people to see so the third lesson today

is about slicers now before i show you how to put a slicer into your excel worksheet i just want to show you what it does now these right here are slicers and it's going to help me filter the information from the table that i have over here so for instance i have by different conference the different quarterbacks i want to see the afc quarterbacks i click on this like so and it brings me those afc quarterbacks i can even uh break it down more i can hit south and then i have uh i then i have the three remaining and it shows me the teams here that are remaining i can't click on the ones that it's not including and maybe i want to check out the colts and then i get to that part so that's what i'm going to show you how to add to a table here as our third lesson so i have the

information here that i've showed you that where i've added the slicers to and i've just deleted it back to normal and it's not a table or anything yet and actually that's the first step that you need to create or this needs to become a table and if you don't know how to make it a table all you really need to do you could i could select uh the area that will include but even if i click in here somewhere so a cell and under insert up top here if i go to table it kind of knows where the table is looking i could highlight it if i wanted to but i can see it selected the right area my table has headers yes they do hit ok and now this is a table i could go through if you wanted to you could go through and design it differently you can see as i click on different ones just to adjust it but i'll let you do that play with that yourself now i'm going to add the slicer to this so again if i just click inside here and i'm going to go to insert and where slicer is so i'm going to go over here right here so filters and then i'm going to click on slicer now it's going to ask what do i want to be kind of the filters that on it and so i chose before where i had conference division and team on it i could add more if you want but for me if i was searching through this would probably give me what i want those three criteria for filtering and i'm just going to go ahead hit ok and right away you notice so i have three different slicers i can go through and make if i click on it you can see each one i can adjust the colors and i can do different things through here to stylize these slicers the way i want i'll just make sure i'm going to go actually you know what i'm going to keep it the same color here on each of these and you can see how i click on these two oops i'm going to make sure i click on the right one i can move them around maybe i'll put the conference first just like i showed you in the example i can stretch these out if i want to uh you can see how it adjusts like this i can i'm just going to put them just like this and i'm going to make them a little bit smaller move them up and just place them all kind of in a row like this to get them straight that'll be straight enough and here so now all i have to do if i click on and i give you the example before if i click on afc there's my afc these are providing the filters so again south and i can look through and it's only going to highlight the ones that it's a possible search and they know as i mentioned this is these are the ones that they know they can search so what do you do when you want to search again well you can just clear this so if i clear these are clearing it so it goes back to normal you can select multiple ones too so if i could uh i could just hold down shift and select multiple ones too but you can see as i say select and i turn off i click on it again it goes back and forth so you can click multiple ones so maybe i wanted to i'm going to put them all back on here again you'll see if i go back maybe i wanted to get a couple of teams in here if i hit multiple teams now i have three different teams so i can compare the teams that i want this way to maybe see what the quarterbacks did with passing yards or td passes uh again makes a simple nice way to filter your information remember you need to make this a table first and uh you can go through and stylize it the way that you want and then if you hand that off to somebody if they were just clicking on it makes them easy uh it makes it easy to kind of get what they're to ask the questions that they want out of the information to give you that feedback so i hope these three lessons today have helped you out with your excel let me know what other things you're looking to learn just write them down below in the uh in the comments down below i always read those to make sure uh that i can make more videos to help people with certain topics and i'll keep on continuing on with these uh different lessons and levels uh and i'll put together a fifth one uh very soon thanks for watching this week on teachers tech i'll see you next time with more tech tips and tutorials