Hi, this is Wayne again with a topic “Sorting in Excel – Basics and Beyond”.
In this article, 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 is 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 ctrl 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 ctrl 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 filters 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 resorted 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. Mark is smart and kimbo 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 gave 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 .