Join Dennis Taylor for an in-depth discussion in this video Sorting data, part of Excel Essential Training (Office 365).
- [Instructor] As you work with lists in Excel, it's not uncommon to want to rearrange the order of rows within a list; we call that sorting. And although there is a way to sort by columns, moving columns left and right, nearly always sort means let's rearrange the order of the rows based on what we see in one or more columns. As we look at this worksheet here, it's called Sorting, it's in the workbook 10 - Data Management Features, it appears to be sorted by either column H or J descending order. We might want to rearrange it by Department. Any time you're about to sort a list, if there's other data on the same worksheet, I do have data off to the right, I need it there, perhaps I've got more way off to the right too I need that there but I certainly don't want to sort it along with the other rows.
So before sorting a list, always make sure that your list is delineated by at least an empty column on the right, and if there happens to be date below it, make sure you've got a few empty rows separating that data as well. When we're ready to sort we can highlight the data or if we know that we have no empty rows or columns within it, we can just click within the data and start using the sort commands. It's not a bad idea, particularly if you haven't seen the data in a while or someone else sent it to you, click within the data press Control + A, think of A for all, then press Control + . a few times.
This will simply move the active cell around the corners of the range. What you don't want to have happen is to end up sorting, and then realize that only some of your data got sorted. We go down to the bottom here, keep pressing Control + ., and again, you don't have to do that very often, just a quick reminder. Okay, we're ready to sort, we can keep the data highlighted or click within it. On the Home tab, you will see Sort & Filter, you can click there and then go into custom sort or, you can go to the Data tab, and use the larger, the Sort buttons right here. Sort, this doesn't say custom sort but it means the same thing.
Two small buttons here we'll get to in a minute. Sort, Excel highlights the data and if there's a header row, Excel nearly always figures it out, if that's the case, you wanna make sure this box is checked, we don't want Row 1 treated like all the others. Rare times would you not have a header, of course this should be unchecked. We can sort on the basis of what's in one column, two columns, three columns, older versions of sort only let us work on the basis of three, we can now go as high as 64. We want our major grouping here, let's say by Department.
So Sort by, click the drop arrow, it shows our field names, Department now in some departments there are gonna be a lot of people there, so as we're looking at that department, we might want to add a level. Let's Sort by Status. And we might even, in some departments, have a lot of people with the same status and same department, so we can add another level maybe even another level. If it's a text field, you probably want these to be in alphabetical order. If it's a numeric field, say, that third level in we might want this to be by Years, consider whether you want it to be smallest to largest, or largest to smallest.
And we have a fourth level here by Employee Name. Sorting is very fast, click OK, sometimes it seems practically instantaneous. We're about to see a new list, sorted by Department, within each department in order by Status, within that by Years within that by Employee Name. And there it is. There are a bunch of Contract people Account Management, here are the Full Time people. Quite a few of them here. Full Time people Account Management. Notice that in column F, although we're not seeing the heading, that's years of service. So they're in order by years of service.
And in this group right here, they have the same status, years of service, department, they're in order alphabetical. You quickly get the feel of what sorting does for you. It simply moves rows up and down, nearly all the time. At a later time, if you simply wanted to re-sort this list by Employee Name, you could click somewhere in column A, click the simple A Z button right here, gonna rearrange the entire list by Employee Name. Now, if the next sort you do, either immediately or a few hours or days later if we click in column C, and then choose A Z, we're now rearranging the data based on what's in column C, but Excel does remember that right now it's in order by Employee Name so now within each Department it's in order by Employee Name.
And if at a later time I come back and click in column D, I'm gonna sort by Status, Excel remembers the previous orders. So sometimes when you're sorting on multiple levels if you remember that the last sort always overrides but remembers the previous order you can even use these buttons in succession as you get more comfortable with sorting. Also from time to time, there are special options in here, you can sort on Cell Color, we don't have any on the screen here, or by Font Color, or if you're familiar with Conditional Formatting icons you can sort on that basis as well, too.
If there is that occasional but rare need to sort by moving columns left and right, you'll jump into Options, and you'll see that choice, Sort left to right. So sorting is generally foolproof, fast, relatively easy and it's absolutely indispensable as you work with large lists of data.
- Working with the Excel interface
- Entering data
- Creating formulas and functions
- Formatting your data
- Adjusting rows and columns
- Finding and replacing data
- Inserting and deleting sheets
- Sorting and filtering data
- Creating charts and PivotTables
- Printing and sharing worksheets
- Protecting worksheets and workbooks
Skill Level Beginner
Q: This course was updated on 1/7/2019. What changed?
A: A new video was added that covers working with Excel Ideas.