Sorting data

- Probably the most common tool for data management is called sorting. We got a list on this worksheet called sorting, it's in the workbook 12- Data Management Features The list is in no particular order right now but you could easily image wanting to sort this list based on the employee name in column A, possibly by the department, hire date, in fact almost any column in here could be the basis of sorting. Sorting most of the time for most people means rearranging the order of these rows.

And if we sort, we want all the information for a single person, for example, Larry Weber here. We want this row to move up or down into its appropriate place. Based either on what's in column A or column C or whichever column we want to sort by. Most sorting means rearranging by rows. There is an option to rearrange by moving columns from left to right that's rarely used. We'd like to sort this data. And we can sort it in any number of different ways, and combinations of ways as well. The Data tab in the ribbon has a Sort button.

If you're on the Home tab you'll also see a sort off to the right, Sort & Filter. But before we begin sorting, we should always scope out our data. Maybe somebody sent this data, I took a quick look at it, it looks okay. Is there an empty row down here? Or one below that? Do we have some kind of subtitles are in it? You should know your data well enough or at least check it out. A couple of tips will help here. The idea could be, before sorting maybe we should highlight all of our data. Well that's okay and certainly not wrong, but if you've got thousands of rows that might take a little bit of time.

So a couple of pointers about working with data. Before considering sorting, or filtering, or some of the other features available, make sure the list you're working with has not any empty rows in it or any empty columns. Be sure to get rid of those. And one way to check that out is to simply click in the data and press control a. Now that highlights all the contiguous data, possibly we could scroll down using the scroll bar on the right hand side. We can also press control . a few times.

That just moves the active cell around the corners of the range. If it takes you down to the bottom, it looks like the bottom, but scroll a little bit more. So what that means, and you don't do this all the time, but after doing that a few times, particularly if you work with your data frequently, then when it comes time to sort you do not have to highlight all your data. Just click within it, and you can go to the sort command. Now one other aspect of sorting, make sure that the entry across the top of the list, it doesn't literally have to be row one, but it often might be, get those titles into a single row.

If they're two rows, redesign them somehow. Make them be in a single row. That makes life a lot simpler. Not only with sorting but with using other data management tools as well. There could be data off to the right. And there is in this example. There's a tax table, maybe eventually we're going to look up some tax rates for compensation amounts and so on. This date is important but maybe it's not related to the sorting that you want to do over here. As long as you have one empty column separating the data you want to sort from the other data, you'll be okay.

So we begin the sorting process either from the Home tab, or from the Data tab. Just click within the data, maybe run the Home tab, we'll start over there. Sort & Filter, and then we see actually three sorts here. Choose Custom Sort. Now when you sort data you have the ability to sort based not just on one column, but on many columns. As we look at the background here, there are many different departments. Some of the departments are quite large. One of them might even have over 100 people working in it.

Now if we're going to get a list from people in that department, we probably want to also have them sorted as well. In other words, within each department, let's also sort maybe by status. And in some departments we might have a lot of people with the same status. So in order to track down people and find records more easily, we might have a multi-layer sort. Multi-level here. So we begin by adding a level. We want the major grouping here, and we click right in here, we see our field names, we want it to be by department.

Within each department we want to break that down, or sort it, group it is another way to say it, by status. And if we know there are going to be a lot of people in some situations that are in the same status, same department, let's add another level. Maybe years of service. Now the first two fields were text fields, and you notice off to the right, the order is A to Z, A to Z ascending. Supposed we indicate now we want to put in years of service. That says smallest to largest, it might be more to our liking to put it in largest to smallest.

But we can go either way. In older versions of Excel before 2007, you could only sort at most on three fields at the same time. Now in this list, and you can certainly imagine larger lists than this even, we might have a lot of people with the same years, same status, same department. So let's maybe add another level. We can now sort on up to, believe it or not, 64 levels. I'm only sorting on four here. So I'll make the fourth level perhaps be employee name.

Now one of the things to look at when you click on sort, the box to the right usually is correct. My data has headers. You'll notice in the background that row 1 is not highlighted. Now if I were to uncheck this, it is highlighted, and row 1 is going to be treated like all the others, it's going to end up in the middle there somewhere, we don't know where. But most often you do have headers and this is checked. If for some reason it isn't, and you do have headers, well then by all means, do check it. And the visual usually confirms what's going on.

So we're about to click OK, and we'll see that our list is grouped primarily, or in a major way is another way to say it, by department. Within each department by status, and years, and employee name. Click OK. Here are all the account management people. How are they grouped? We got a bunch of contract people in account management, then full time, and then later we'll have half time, and a few hourly. We see it like that. Within one of these full time list is quite large there.

How are these people in order? There in descending order by column F. We don't see the heading right now, there it is, Years. And so we've got a group of people here. Here are people in the same department, same status, same years. And what order are they in? Alphabetically by name. We see that over there. If you only sort occasionally, use this method, use that button that you can get to either from the Home tab, by way of Sort & Filter, and then Custom Sort. Or on the Data tab.

It's this button right here. Takes us to the same dialog box. Now there's another way to sort and sometimes it's faster. And it usually comes after you've mastered sorting or you feel comfortable with it. Suppose that we want to sort this list right now by employee name. We don't have to go into the full sort feature with this button. Ascending order, A Z right here. Click this. The concern, the worry is, are we going to rearrange only the data in column A? Now if you click column A, possibly you're on the way to sorting only the data in column A.

So click on either A 1, or any cell within here, and if you click A Z, no questions asked, look what happens. If you're not sure about that, be sure ahead of time you check out one of the names. But this has worked, and we now see the list in alphabetical order based on employee name. Now an important aspect of using this button or the one below it, is that is remembers what occurred before. So right now the list is in order by employee name. Supposed a few minutes later, a few hours later, or tomorrow you say I need to sort this by department.

Well we'll click in column C, click the AZ button. It's in order by department, but what do we see within a certain department? They're in order alphabetically by their names. Because that's the order that it had been in before we sorted here. So you could think of these sorts as being cumulative. The last sort overrides the previous order, but it does remember. So for example, if we click in column D, we're about to get a status sort, but within each status they'll be in order by department.

So AZ, all the contract people together, but as we scroll down the list we see they're in order alphabetically by department. And within each department, for example this one, what order are they in? Alphabetically by name because that was the sort that preceded the previous sort, which was by department here. So as you get more comfortable with sorting these buttons often are faster. But of course, it's handy and important sometimes to go back to the full button, check this out, maybe when you're doing multiple levels.

There is an option out here for sorting by columns, we wont cover that. It's not as commonly needed, in fact, probably less than a percent of sorting is based on rearranging the order of columns. So sorting is a powerful feature. Again proceed cautiously if you don't use this very often. And always check the results. And remember, if something has gone awry you can undo the action. No question though that sorting data is something we quickly want to do, and we can get there very fast. Most of the time, sorting seem practically instantaneous.