Lessons

Lesson Info

1:00 pm - The Magic of Sorting & Filtering

All right, so we're going to be working with sorting, filtering and conditional formatting in this segment and this is definitely a powerful, powerful tool always a really powerful tools but really, really simple to implement so we're going to be getting into those you're going to see all the all the value of these things as we go forward so let's let's go ahead and work with me just cancel out of some of these other guys I've been working with here and we'll come back here we have just one file for this segment this is going to be a day too session three sorting and filtering data so we just go out and if you were working on the file that home just go ahead and check that out so this is gonna be the one thing that we're going to be working with you sorting it, filtering it and also doing conditional formatting. So what we're talking about sorting we're talking about well, you know what I would like to alphabetize something I would like to find out and chronological order or numerical ...

order you know from one to the most recent or whatever it's going to be so you can do that so going to see that we have the ability two very simply dio I was a good way to start off on the pc side we have the ability to do is a sort on this far right hand side, we have sort and filter and I move my mouth. So that tells me what is sorting, filtering, arrange the data so that it's easier to analyze, you consort the selected data in a sending or descending order, or you can temporarily filter out the specific values. Okay, so we're gonna be able to do just a very, very simple, simple sort. Now, if you're used to the old versions of excel, you probably had the habit or the exercise or the strategy of basically highlighting the whole thing and then being very specific about where you want to do it, and right now we don't have to do that anymore. For the latest versions, you don't actually need to do that any longer. So all you do is simply click anywhere inside the column as far as what you want to actually sorted by. So if I want to sort by column, do you mean by client, all I have to do is simply click inside, see column anywhere in there, and I wanna be able to sort this out alphabetically, okay? So I don't have to highlight anything I don't have to like do anything I'll have to do to simply click. Anywhere inside this column and excel is going to be smart enough to know hey, you want to sort by this and not only that it's going to take all that requisite data with it so what knows? Okay, when I'm gonna have, you know, food and whatever order it's going to take quarter one central and eighteen thousand thirty three hundred with it okay? So it's going to know to do that all right? Because it's all part of it and excel is just that smart so let's go and do that very simple, very straightforward so I'm gonna do it one way I'm going to do it another way because you'll have to choose your preference when you see here I've sort a dizzy sort ziti ai and I also have custom sort and we're gonna get into this custom store in a little bit and we definitely will get into the filter as well, so I'm just going to simply shoes sort a dizzy click on that and then bam just like that, everything now is alphabetized see that and even groups all the ones that are the same all together. All right let's, go ahead and do another one let's go ahead and do now quarter where I can actually alphabetize those but I'm gonna do it in a little bit of a different way this time I'm gonna go to data and this time notice I have an entire group devoted to shorten filter I'm going to do now is again do my a dizzy and now that's sort of just like this. Okay, you see, I have all my quarter one, two, three just like that. Okay, super simple super straightforward, however, really, really sophisticated in terms of what it's doing, you know, I mean it's like we only have to do a couple of clicks, but it's doing a pretty amazing thing to have it all alphabetized like that or doing in chronological order. So the next thing I'll do, I'm going to do working with numbers because we're really concerned well, okay, what's our lowest sales to our highest sales. So what can we do then there's going to simply click anywhere inside of the sales column began once again it's the same thing and you can choose to do it either on this part here we have of data and sort or even go back to home and click on the sort of filter and do the same thing and notice how it actually says something different this time before and said what sort a to z now accelerate aware of the fact that what this is a number right, these are all numbers so it's recognizing that this is not a dizzy this is actually a size this is largest smallest to largest on largest to smallest so it's recognizing that so it makes that says okay, let's do smallest to largest I do that and then bam it brings everything in just like that within half a second, making its smallest to largest okay, so really, really powerful now let's say I want to go a step further let's say I'd like to sort this, but I'd like to start sorting things by different what we called levels all right, so we're talking about levels were talking about different strata within a strata so it's for an example let's say for example, I have I want to start by region, but within each region I want to be able to say, well, what? What what quarter should be sorted within each region and then within each quarter can you sort out by client so it's basically like a hierarchy of different sort ings that you khun do within each level all right, if that doesn't make any sense it's about too, so we're gonna go ahead and watch that all right? So let's, just go in and say I want to sort by region so this is going to be known as a custom sort by level, so how do I do this let's go over to my sort of filter again and it actually this point it doesn't matter where you click just so you know at this point you can click pretty much anywhere inside the whole area doesn't matter what column you click him because we're going to able to specify it using a few drop down menus so I'm gonna go to skip over these two guys here I'm gonna go right to my custom sort I'm going to see here I have all this this new dialog box that pops up so what am I going to do basically says ok what's in my column what I want to be sorting on and then what's the order of how you wanted to be in so what I want to sort by and look how smart exelon's excel is not taken my column headed guess why? Because this right now automatically says my data has headers and it understands that the top part is is actually headers is not part of the data itself so it knows to say hey this is we want to sort by so go ahead and do that so I want to sort by region first I want to sort a dizzy that's great now what I'd like to do is I want to add on a level so I'm going to go ahead and do a little more sophistication of this so I'm going to say I don't want to do by region then I want to do by quarter I'm going to leave it at that for right now. Then we'll come back later on in just a second. I want you to least see what a more simple five version of this is going to do. I was gonna go and click, okay? And I'll see what it does here. Basically, I have all of my central's, which is the first part, right, which is the first earlier in the alphabet compared to central, east, north, south and west. Right? Because it starts with a c. So right. One central ends. Okay, we start over again in terms of a different quarters, so we can see here we have all these things sort of just like that by quarter. One, two, three, four and then we have east and we starts off with quarter one, two, three, four. So as soon as that ends, we go to another regional together, and then we start doing everything within that. Okay, let's, do that another way, let's do the same thing, essentially what we're gonna do this time with quarter and client, we do one more time, I'm going to go. It doesn't matter where you click again, going back to customs sort this time, I'm gonna do quarter, and I'm going to say client and then of course I could change the order if I want to it's really up tio pending on what you want to do I'm going to go and say ok and you can see here here's all my quarters right and it's soon and note how's all alphabetical within this one quarter okay? And then as soon as that quarter ends the alphabet starts all over again see that quarter too and then it starts all over again to a ok so you see how that works now let's do a third level let's do it a little bit differently now let's do quarter one client and then let's add on sales we're going to add on a different type of sorting that we could do a za third level and then maybe we won't do it from the lowest highest to lowest instead okay, so let's go ahead and do that again custom sort and I'm gonna say uh, quarter client and guess what ad level and notice what it does here actually puts it right in the middle but I'm gonna be able to change that in just a second I'm gonna click on that drop down and I'm gonna say sales because I wanted actually to get by a quarter then by client than my sales so I got to do is simply click on this robe and just click on this little down facing arrow and it goes down below it and you do that with any of those if you want to now before I finish I'm gonna go ahead and just say um largest to smallest okay everybody should have something like that I click okay and now all three of those are now sorted all right and again so we have cause quarter one is the is the kind of like parents of the of the relationship and then we have alphabetical within that and then within each of these these air also by numerical right so understand how this works we have quarter which is kind of like the top of it and we have an isi which is the next level so we have within aniseed we have these three okay do you see that and then it stops because we have a new letter coming in so it starts over again and then another one by itself another one and that is when we have multiple ones you see it starts all over again. Okay going from highest to lowest okay, so hopefully that makes sense maybe have some examples of how you do that but see those lots and lots of stuff you can do with that all right? So that's going to be ah custom sort being able to change it based on different levels okay, so let's come over to the max side and I'm gonna open up to our sorting and filtering data file maximize that okay and then you're going to see how a lot of the stuff is going to be relatively similar on here. Okay, so you're going to see that on the mac side we actually have on our toolbar which is our standard toolbar this a dizzy and you'll have a drop down we have a sending descending and we're gonna talk about this cell color and font color in all that stuff we get into our filtering and stuff or conditional formatting but very easily now I can just click on that and then everything sorts on its own okay, so this is one way to find remember how we found it on the pc side? We found it under home the other way you can do that is under data and then you're going to see that sort is the first option right there. This is actually how you get to the advanced sorting like to the customs sword. Okay, so let's just do that one more time so we're going to go ahead. I think there should be a third way also from data let's see? Yeah there's data here and it's also another one right there. So we have this is e t a a dizzy a sending right? You see that so we could do it that way as well all right, so you actually three ways to do it on the apple side okay, so it again depends on where you click that's a really important part again, just like with a lot of those things we've been talking about. Where you click is important because going to determine what column is going to then be sort of okay. So now if you want to do a more advanced sort, you could do that as well, so I can click on this drop down and you're going to see I have customs sort right here. And if I click on this data and sort you going to see it's going, allow me to do this as well. Okay, so it's a little bit different in terms of, like, you know, the words they use a little more icon driven. Let me come out of this for a second. Come over here and show you customs store. And no, just I want you to know that it's the same exact dialogue box so you can do it from here. Or you can do from data and then say sort, okay, either one. All right, so notice right away sat the top thing here. It says my list has headers all right, on the on the pc and said my table as headers and this was going to say, my list has headers so, uh, kind of same idea, but different different terminology, so I want to be able to sort it out by the different things here, so we're going to sort it out this time by region. So as soon as I click on this it's not a drop down thing, but it does fly open when I click on it, I'm going to say region that's great and then notice I have a plus sign and a minus sign here, so I'm going to do now is click on the plus sign says okay, first I'm going to sort by region, then I'm going to sort by quarter, okay? And they're all the same options of the same ok, all the same things right here so I can go ahead and just do that and bam choose any one of those in this humble doozy too, eh? Just kind of play around a little bit and then I could just go from there, click okay, and then there we have it. Okay, so we're doing central this time, which is the first letter in the alphabet of all the regions and then notice we're starting from zero a for all these, which is going from quarter for two quarter one okay, just understand that, so this is all essentially the same thing okay, so when you go from pc to mac, but hopefully it's pretty straightforward for you, okay? So, um, let's, go ahead and move on from that. We have any questions about sorting. You're looking very game. That's. Good. Straight forward. Thanks, david. All right. Excellent. So let's, go ahead and move on, tio. Filtering from now. Okay, so, filtering. Let me just go to make this a little bit bigger. All right? This time we'll start off on the on the backside. So filtering is let's. Say, for example, you on li want to see quarter one and quarter to okay, you only want to see that part of the spreadsheet and you only want to see anything else, so filter is basically like you just sort of like getting rid of some things temporarily so you can just see those parts, but you want to see the other parts at the same time, but you want to come back to those parts at another time without the leading them. So I'm just saying you're filtering them out. You're saying, you know what? I have a certain criteria of what certain factors that should be in there right now, but I don't want to see them right now because a variety of different reasons because you want to see how the data comes in you might want to make a chart out of it you might want to just copy and paste it and you might just want to view it for good to know what the values are just for those for those parameters so very simple all we do is first is we apply the filter all right? We have to apply filter to the entire table or two list or whatever you want to call it so notice here within data I have the filter also notice that I also have this filter icon right there okay, I could do the same way. So either way my click on that you're going to see what happens down on the spreadsheet is that I get these little drop down menus. Okay? So the drop down menus essentially are indicating to me that my my table has now been filtered it's starting to get filtered the drop down menus are amazing because what's happening now is excel has actually recognized the fact that you what you have inside of all these columns what they could be filtered by. So what do I mean by that like look on region right now notice the things it's actually picked up on it picked up on the fact that I have a central east north and south and what do we know about check marks check marks. You're basically a yes or no thing. Do we want this to be shown or not? Essentially we're filtering out by those particular categories. Do we want tohave northeast south? Whatever it's going to be, we can go ahead and do that. So we're going to do right now. So I only want to show the north and south right now. So I have to do it's essentially a gn shack. These someone on shack, central, east and west. Okay, I'm gonna get rid of this committee here, have north and south, and now you can see I only have north and south, all right? And I know that's been filtered because guess what it goes. One, two, three, four, five, two, forty one. And then it goes because these aaron sequence of each other, but if it was the west, there also be a jump in there because alphabetically. Okay, so let's, go in and do that with another one. So let me go out and clear out this filter so you can clear out this particular filter by clicking on the drop down again. And we're just going to say clear filter down here in the lower right, and then it comes back to how it wass let me close out of this okay and you can see there now everything's back but the filter still remains in terms of the ability to create a filter let's do one more just for some practice let's go to quarter you can see what happens here. I have quarter one two three so excels automatically brought those things in there. Right, it's just fantastic. So if I now so you know, I only wanted to quarter one in two I have two uncheck three and four and notice how it actually happens in real time as I do this and actually changes and I do that all right? I have to have something in there. Okay? So you can see how it's changing right there in real time and I can close this and I conceal the work that I've done that's great. Okay, so that's gonna be one type of filter that's going to be very kind of simplified type of filtering, so we can also do new. Multiple types of filters were let's say we want to filter by both the quarter and the region, okay or let's say we want to do ah ah filter of a quarter and sales that's we're going to do in a little bit where we can actually say, you know what let's show quarter one into, but on ly everybody who's over fifty thousand something like that so we can actually go into other kind of filters outside of what's just shown we're going to do that in just a second but first let me show you how we can do duplicate it's the same time or multiple one of the same time so right now the quarter is just showing quarter one and two, but I'd like to show central north and west central, north and west that should be the only ones that air shows him so then I click outsiders just to see what's happening and again I want you to notice that it's going from twenty five to fifty one from sixty to ninety one because they've just been filtered they have not been deleted hey, they've not been erased they're just basically filtered out so there's temporarily hidden so you can see what do you want to see? It just gives you total control over that so this is him that we can copy and paste something we could make charts out of lots of things we can do at this point based on what we're filtering okay? So really really powerful tool. So let's now I'm going to clear up the filter for this one clear the filter porter and I'd like to show you a different kind of filter weaken dio so we're talking about sales so let's just say we want to see all sales that are over forty thousand just something that might come up for you so this is going to be a number filter as opposed to the filter that we just saw which is basically just checking off those boxes to say hey I only want to see these particular categories now we're gonna be able to get a little bit more complex with saying hey excel do the math for me because I could probably done that other stuff myself this is going to be a little bit more sophisticated in terms of what excel is going to do with my filtering so let's go over here now to the drop down and you going to see how it's going to allow me to number one choose all these which I do not want to do it all I'd like to do is click on this dropped in with this choose one and it's gonna allow me to do some filtering that's going to be sort of like a condition or sort of a logical statement worth says it's greater than our lesson or between or in the top ten or something like that top ten essentially saying it's per cent of your bottom ten percent course you can change the number but let's just do a few these let's just start off with a simple one we're going to say greater than just filter ones that are greater than and we're gonna type in forty thousand ok and I hit return clear out of this and lo and behold, everything that shows up now is over forty thousand you can see how it's filtered it out seamlessly I mean the engine just working in the background and just fantastic you can see everything there five seven thirteen did it all that stuff because it's based on ly shown things that are over forty thousand dollars okay, which could be just such a great thing if you have a lot of data and they're able to filter out those particular things and then of course I can go and do another part here where I was going to say ok for clients let's only do certain clients or whatever it is they're certain clients and this is going to be something else where we're going to be saying it begins with or does not end with and things like that we talked about this a second that's what's known as a text filter so you could do all these things right in there all right? But I'm gonna skip this one but I'm gonna go to quarter because I want to basically see what quarter of all these quarter one and two has the ones that are over forty thousand just click on three and four now I'm doing a double filter but not only am I doing a filter that I can actually check the boxes but I'm doing the filter that showing me which ones have a certain criteria that's over forty thousand that's going to show me everything right there so this can really be you know it's a nice way to do some analysis of your data that you may not have thought you could do very quickly and easily so filtering is a very, very powerful tool so now let's just say for example I'm going to go a step further I'm going to do a third one so I can see here I have some of these things here so I want to basically say I want to have everything that starts with the letter c as a cz part of this filter so starts letter c it's in quarter one and two and it's over forty okay just so you can understand how that works in terms of the text filter and you can see here begins with I can't understand that because we're working with a text filter now as opposed to a numeric filter our options are going to be different cause remember before it was like between it was top ten it was always summer things is greater than less than it understand that this is just text in here so I'm going to go ahead and say now begins with the letter c and I should not be case sensitive okay and look what it did automatically right there it did that for me okay totally gets that totally understands it okay and I'm gonna do one more on the number side of things I'm gonna clear out these filters so again click on the drop down go to clear filter click on the drop down clear filter and this would be something that you may be doing more often if you're working with aa lot of stuff and you know what you want to figure out what's going to be the top fifty percentile on something like this right what's going to be the bottom fifty percent bottom forty percent tell whatever is going to be you might want to figure that out but you want to sell to be able to do it for you that's what we're gonna do that top ten percent that I was turned to earlier let's come back here now to the sales let's clear out this filter so we can kind of start from scratch and we clear it out and everything's back to normal but I'd like to do now is I'm gonna choose this top ten list of the top ten and have the bottom ten you also have a few other things above average below average and then all these other numeric ones that you could possibly do as well but I'm going to say top ten and then notice here it says top ten and then under here there's a number over here there's a drop down so I actually want to say not items I want to save percentage because percentage is a lot harder to calculate an item that we can figure it out what percentage when you get a lot of stuff that's really what? We're more concerned because it's going to be all kind of relative to what we're working with so I'm going to go ahead and say top twenty five percent okay? And it already does the math already has it in their automatically understanding where it falls in the percentage we don't have to do it excel doesn't for us but it's still yielding really really valuable information because you want to see who your top ten salespeople your top twenty five sales people or whatever it's going to be your your top twenty five clients whatever it is twenty five percent clients so it's going to be really helpful so instead of going into here we can go into this segment we're working with percentages and also of course for items and the same thing would be for your bottom and all those other things we can specify some more stuff here okay? So really really powerful stuff so this is kind of on the way of going into pivot tables and a few people have asked about pivot tables and we are going to get into pivot tables on the last day so those of you who are looking to learn about pippa tables we are going to get into that tomorrow so this is kind of the next step closer to pivot tables and that seems to be the hot topic these days and I'll show you how easy it is to create pivot tables and really powerful stuff but without a whole lot effort so let's now go over to the pc side you're going to see how um where am I okay go where you are okay so we're gonna work on this one again alright this time again we're going to do filtering so filtering it's going to be very similar but the dialog boxes will be a little bit different options boxes will be a little bit different so I could do that again under my sort and filter and I just simply choose filter right from there another way to do that okay is to do data and then filter right there either one will work either way okay let me just go ahead and bring that up a little bit and you'll see now I get these little drop down menus for me to work with all right very very somewhere if not exactly same as we just did it so I'll just do a very quick just you can understand and the only thing I want you to understand is the fact that the boxes are going to look a little bit different how we work with these things all right so it's going to go ahead and click on region and then notice how it is right there. I mean, really, the main difference is it's it's white and then some of the options like the fly out colors and all that kind of stuff going to be a little bit different so I could just go ahead and just do one of these things here. I do that very simply and then it's done. Okay, so this has the filter on, and I can do another one on top of the other one inch, essentially just check boxes right there. Okay? And then I just have that same thing. So really not a whole lot of difference there. So hopefully now doing this again gives you some review on what we did the first time on. Then again, I'm going to go a little step further with my numbers. My numbers were not going to allow me to do, um, number filters. All right. So instead of the select one thing that we saw on the max side, this is just a fly out many that says number filters. Okay, that's gonna be the main difference it's not a select one thing. It actually says numbers filters and you see, we have a flyout menu, and this shows all the things we want to do, including the top ten, including the greater than all that other stuff that we did before okay, so again, if I do top ten you'll see it's going to be top ten and then items or percentage so essentially the same exact thing savings that concept essentially it's a little bit of a different menu for you to work with but you see it's pretty straightforward I'm working with this and also very similar aiken going to make those changes okay go ahead twenty five all right and I'll do that look okay and then now that's the top twenty five percent not only top twenty five percent of the whole group but because this is filtered and this is filtered it's a top twenty five percent of the east central quarter one into so I'm asking it to a really, really complex, sophisticated thing I'm asking a very complex, sophisticated question and it's given me the answer in a heartbeat which is really, really amazing hey excel what's the top twenty five percent aa quarter one into in the eastern and central regions okay? Gerald ford and alan arkin good job guys all right so we can see that they're all right so that's filtering in a nutshell on dh I hope you found some good use for that because it's definitely very, very valuable tool when you're working with large stuff you want to do a type of data analysis that's not too hard to do but also kind of sophisticated and again we will be getting into pivot tables tomorrow which is going to allow you to do a lot more complex type of analysis but in a really really simple way um okay fantastic. I'm gonna clear out the filter well, I will live on david it's just a quick question about filters from diner saying so while you're in the filter and then you run it are you then able to sort the data from then okay, so we go ahead and do that okay, so this is ahh kind of a crazy ones let me just untie me clear out this filter okay? So if you want to sort this right now would be the same thing right now you can actually sort directly from here actually from the drop down menu sorted a dizzy probably the fastest way to do it but you can see that because the drop down menu right here I'm actually have everything here and I'm going to do sort a dizzy now I not only have the filter but it's now sorted out by everybody okay? Great question great question and rodney forty four says is there a way to turn off the default of select all I have found that I always on check it but I would like to default to non selective filter I think we've all had that thought actually that would be nice not that I know of okay? Yeah