Sorting concepts and sort menu options

- [Voiceover] Probably the most commonly used data management tool is sort. Let's sort the data. Nearly always in Excel, that means what? Moving rows up and down. Here's a list. It's currently sorted by department. I might want to sort it by hire date, or years of service, job rating, in fact almost any column here. Unlikely perhaps a phone number, but any of the others. And most of the time when we sort, we mean, in effect, let's take this row, as an example. We're going to move this up or down, based on what we see in one of the columns. If we're basing this on column A, this is going to move well down in the list since the name begins with O. So a lot of different reasons for wanting to manipulate the data. It gives us some insight. It's not truly an analytical tool, but it certainly groups the data in a way that makes it easy to find information. Now, anytime you're confronted with a list, and you want to sort it. First of all recognize how big is the list. And first of all, on this worksheet I'm going to scroll to the right a little bit. There's other data out there. You don't necessarily know that ahead of time, but a good habit to get into in a worksheet that you've never seen, or one that you have not seen in a long time, is simply to press Control end. Not the letter N, but the end key. Control end. Anytime you press control end the active cell goes to a location. There is no data anywhere below this row, you can be sure there is no data anywhere to the right of column P. Now that doesn't necessarily tell us everything about this list over here, that we're seeing over here. You can click in this list, you want to go back up top, control home. It always takes us to the upper left corner. Usually that means cell A1. Now, within list itself, could there have been some empty rows? Well there could have been, but if you click a cell within the list, double click it's bottom edge, it's going to take you down to the first empty cell that it sees. So on the list here, there's not an empty row within the list. We can double click the top edge. You can do that from any cell in here, unless that column has blanks in it. So, double click, takes us back up top. Over in column J, there are blanks. We wouldn't be using that column if we wanted to simply move up and down the column quickly. If you don't use sorting very often, you want to be a little methodical about it, and think it out. And a good tip before sorting data, particularly data that you're not too familiar with. Click within it somewhere, press control A. Think of A for all, now that will highlight all the contiguous data, in other words, all the cells with data. Notice all the cells nearby, and all the cells with data are highlighted. And we can scroll up and down, and see the extent of this, or a not so obvious short cut here. Control period, pressed four or five times here. This simply moves the active cell around the corner. So we know that our list here, does not have any empty rows within it, or empty columns. And that's what we want to know ahead of time. The other thing you want to be clear on, is if there's other data in the worksheet, for example data out here, we don't want that data to be sorted along with this. Now if these columns were not here, or if we somehow move the data over here, we're asking for problems. Because if we were to click over here, and sort the data, excel would pick up all the information out to column M, and these rows would get shuffled up and down into locations that we wouldn't really want. So we don't want to have that situation occur. Control Z, back to here. Have at least one empty column, and preferably more, just to make sure. Off to the right here, this is different data. Now you might want to sort this list at a later time, we'll come back to that. But let's say we're focused on the list to the left. Now, if you're ready to sort, you don't have to highlight all the data. Click this button here on the data tab. Now possibly if you're on the home tab, you might see that button way off to the right called sort and filter. You can click that, if you choose custom sort, it takes you to this dialogue box called sort. And I'm going to close that, not that we can't use it. But I want to point out that's the same as going to the data tab, and clicking the larger sort button, takes you to the same location. Always be sensitive to this box here that says my data has headers. Excel nearly always figures this out correctly. It senses that row one as we see it in the data here, is unique. It's not to be treated like all the others. If this is unchecked, and if in the example that we're looking at in the background here. If we were to use some of the other options here, and sort the data, that row's going to be treated like all the others. And so if we were sorting for example, the employee names in column A, Employee names is going to be amongst those names that begin with the letter E, certainly wouldn't want that. There could be times, if you have a list, that has no headings, that's unusual, you would want to un-check the box, but most of the time you want to see this checked. Occasionally Excel doesn't sense that your top row is a header row. Usually that's caused by having an empty field name. Doesn't happen too often. Now, I want to cancel this and point out a couple of other things too. Before sorting, you've got titles, keep them in a single row. Now, what if I really wanted to spell out anniversary month here? I'm spelling it out now, if I press enter, keep it this way, I'm going to have to make that column a lot wider to see the heading. And let's say I don't want to do that. But here's what I can do. I'm going to double click behind the y, I'll press alt enter. That introduces a line break. I'll delete the leading space there, and then enter. And I probably left align this too from the home tab. Make it a little more readable that way. The column doesn't have to be as wide. For whatever reason, need titles with many words in them, use the so called line wrap feature, you can also use wrap text. You see this on the home tab. You can use it either by way of that feature, as you see the button here or by the typing that you saw I pressed alt enter to introduce a line break. Your titles don't have to be literally in row one but often that works best too. If you needed other title information, like the name of the company and other types of information like that you could put this above the data. Make sure you've separated it from the data you want to sort by at least one empty row. So for example here if I insert two empty rows here above the data, maybe I'll put the company name up here, and some other information, that's OK but it still is likely to be easier that when you sort, and Excel will understand here that this is your data from here downward, not the date up above if there is any up there but make sure the row above this is empty. And I think most of the time it's going to work even better if you don't have information above there. Although you can certainly work around that. So I'm gonna delete those rows. Now what if this data was sent to you, you're in a hurry and someone who has sent this to you, for whatever reason had inserted an empty row. Maybe for Page Break reasons or visual reasons or something like that. If you didn't sense that, in other words maybe you didn't press control A to scope this out a little bit. You click in here, what happens when you go to the data tab and you click the sort button, in the background you see all the data that's highlighted, but you can't see that far. We only can see 18 rows or so in the background, so we could proceed here with sort but what would happen, worse things could happen, but we would only be sorting this data here. From here down to that empty row. We wouldn't be touching the other data and so that would mean for example that at a later time we realize something is a little bit off. It wouldn't have destroyed any data, but it only would have sorted the data down to here. That's why I said from time to time, if you work with a list frequently, or maybe you've never seen this just press control A. You don't do this every single time of course. Press Control Period as I said before, and in this case we'd see clearly something's going on here. So get rid of that. By the way a little bit of a work around there. If you somehow wanted to put in manual page breaks but, were in a hurry and something, maybe you want a page break right here at the beginning of this organization. Click the row number and then drag the bottom edge of the row downward to make it about twice as tall. Now when printed this looks like an empty row. It isn't really, it's just a tall row. So you could work around playing around with that idea little bit. Now let's go back to the very idea of sorting though. If you work with the data frequently, all you need to do is click within it. If you only see it once in a while, click within it. Press control A just to make sure your data's contiguous and then either scroll around or press control period. So we are ready to sort. We click on the single cell, we use the larger button here. Once again, check out the data heading so on. Lets say we wanted to sort this list by employee name. Right now it's sorted by department. So just click here and choose the employee name. And you'll see in a later movie, how to sort on multiple columns. And you can also sort not just based on values, but also on cell color, font color or cell icon. That's a by product of conditional formatting, pops up in a later movie. You can also sort, not necessarily A to Z but you could do reverse. That's unlikely with text but if these were numbers we could sort smallest to largest or largest to smallest. We could also sort on the basis of a custom list. That too is covered in a later movie. So we simply want to rearrange this list here by sorting based on what's in column A. And remember sorting nearly always means move these rows up or down. Now before we do this just to check it out and to make sure we know what's happening here, here's Douglas Ayers in row seven, and we see some information about him here. And let's remember one or two items about him, August 20th of 2005. Let's remember that bit of information as we sort the list here. His name and all of the information about him will move downward of course as we click OK. And in the list here we'll see it further down here, there's Douglas Ayers same information as before. There's that August 20th of 2005. So we simply moved the rows up and down. You'll see in a later movie how you can sort by moving columns left or right. But there's no question that as we look at a list like this there are any number of different reasons for wanting to sort on this column or that column. We could go back to that same dialog box if we want to see this list in order based on hire date, we've got our active cell within the data, we'll go back to that data tab sort, maybe this time sort by hire date. Instead of employee name. There's hire date oldest to newest or newest to oldest. As we prefer we can change the direction there. Excel recognizes this as a date. If it were just a number we'd see smallest to largest, and that too has an opposite. So in this case newest to oldest, oldest to newest. How bout oldest to newest, click okay. The people hired first in this organization appear there first. So we've seen some of the basics of sorting. Remember out of all the tools we use in excel for managing lists, this is probably the most important. The one that used most frequently.