In this video I will show you everything you need to know about sorting in Microsoft Excel. We'll look at the basics and beyond. So here I have a spreadsheet with a list of NBA basketball players, specifically some of the top guards in the NBA, and I would like to sort this data based on different things, whether it be their name, the team they play for, the number of points per game they averaged, etc.
So how would I go about doing that? Well let's first look at the simplest type of sort. Let's say I just want to sort all of this data based on the player's first name. I'll just click here anywhere in column A and then here on the Home tab on the Home ribbon in the editing group I have a sort and filter button.
Now this may look a little different in your version of Excel but I can click on this sort and filter button and then choose to sort the data from A to Z. I click and all of the data in the entire spreadsheet is reoriented, resorted based on the first name of the player. Now the first time you do this, you may feel like you're about to ruin the data.
You're going to mix it up so that, for example, Damien Lillard's data isn't really going to match up to him anymore. But if you look at it, this does match up. It is his data.
So that worked very well. I could do a similar thing, but this time with points per game. So I'm going to click anywhere in the points per game column. And again, I could go here. to the editing group on the home ribbon, click sort and filter, and then I could again click A to Z, but notice what it's saying now.
It says sort smallest to largest. Excel can tell that this data is numerical, and so it's changed the label of this button to show that it'll be the largest number to the smallest number, and that's actually what I want. I don't want smallest to largest in this case.
I want largest to smallest, so I click there, and now all of the data is resorted based on the highest points per game to the lowest points per game. Now notice that the top row is not being included in that sort. Excel is smart and it has guessed that this row, row number one, should not be sorted with the rest of the data.
So that's a really good thing about sorting in Microsoft Excel. Now there's a problem that you might run into when you're sorting this way. Let's say off to the right of my data, maybe I'd like to make a list of my favorite players.
But I don't really consider this data to be part of the statistics. This is just my own list of favorite players. But watch what happens if, let's say, I decide to sort the data based on team.
I click in column B, I do my sort, A to Z. Now look what's happened to my list. My list of favorite players has also been sorted.
The way you prevent that problem is by making sure that there's a blank column between the actual data that you want sorted and any other information in the spreadsheet. So now that I have a column between them, if I sort based on blocks per game, smallest to largest, it doesn't change my list over here on the right. What if I do largest to smallest? It still doesn't affect my list on the right. Now that's also true of information that I put below the data.
So if I want some information here below, it's important that I have a row or two that's blank between my data and any other information that I type into the spreadsheet. So now when I do my sort, it doesn't affect this data that's below. One trick that you should know to help you to check any information that may be around your data is to just click inside the data somewhere, hold control and tap A. That will select all of the data that's together.
So notice it did not include my list of favorite players. It didn't include anything below the data at all. Once you've done that, you can hold control and tap the period button.
and that will take you to the upper left corner and highlight that cell. So A1 is highlighted, I tap it again, control period, and it shows the upper right corner, tap it again, lower right corner, tap it again, lower left corner. And so that's a good way to just double check and establish in your own mind, okay, what is my data that's going to be sorted?
Is there anything directly below it, to the right of it, that I need to be careful of? Now there's one other kind of sort that you should be aware of. What if I want to sort this data based on two aspects of the data or three or more? How would I do that?
What I could do is again click anywhere inside the data and then go here in the editing group, click on sort and filter, and then go down to custom sort. Now another way to get to the same place is instead of going to the home tab, you can go to the data tab and just click sort. So this sort is the same thing as a custom sort. And here's that same A to Z button, Z to A button. But when you go to the Data tab, the buttons are bigger.
You don't have to worry about filter. Filter's over here. So in some cases, it's easier and faster just to go to the Data tab. But either way, just click on Sort or Custom Sort, and you'll get a pop-up that will help you to set up your custom sort.
Now, based on the cell that I had selected, Excel thinks that I want to sort based on two-point percentage, but I can definitely change that. So I'll click here and I'll switch it to team. So my first level of sorting is going to be just based on the team name.
Yes, I want to sort based on the cell values, the information that's in each cell in that column. If I wanted to, I instead could sort based on cell color or font color, conditional formatting icon. So there are different options that you have there. In many cases, you'll just want to keep it at cell values. And then over here on order, yes, I want alphabetical order, but if you want to, you can change that up.
So that's my first level of sorting, but there are some NBA teams listed here that have more than one top guard in the NBA that's on this list. So if there's a tie like that, two players from the LA Lakers, two players from the New York Knicks, or whatever it might be, then what's the next level of sorting? Well, I can just click add level, so it'll sort by team, then by... I could say player name. Let's try that.
Again, A to Z. I click OK. Now all of the data is reorganized, re-sorted, first of all based on the team that the players are on, and then secondly based on the name of the player.
If I want to do that again, I can click sort again, and I could change it. Maybe the second level sort should be based on points per game. Let's try that, and we'll go with largest to smallest.
I click OK. So now The teams are still in alphabetical order, but the players are no longer alphabetical by first name. Instead, it's based on points per game. So let's look at Boston, for example. Marcus Smart and Kemba Walker.
Smart is listed first because he averaged more points per game. Now when you do a custom sort, it's important to notice this option here. My data has headers. In this case, I do have a header row. I give it a blue background to make it stand out.
If you don't have a header row, it's important that you uncheck that. In my case, that would ruin my data. It would then sort my header row, so I'm going to undo that. But that's an important setting to be aware of. Thanks for watching.
I hope you found this tutorial to be helpful. If you did, please like, follow, and subscribe. And when you do, click the bell so you'll be notified when I post another video.
If you'd like to support my channel, consider clicking the Thanks button, or supporting me on Patreon, or buying channel merch. and you'll find information about those options in the description below the video.