Sort concepts and Sort menu options - Microsoft Excel for Mac Tutorial

- [Instructor] Probably the most common data management tool on Excel is sort. We've got a list on this worksheet called concepts. It's in the file called chapter one. You can see pretty readily that the data is organized based on what we see in column A. Alphabetical by employee name. And I think you could imagine at different times wanting to sort this data based on perhaps, the information column G, the hire date. Or column H, years of service. Maybe column J. And so at different times when you work with lists, the objective is let's rearrange the rows. And most of the time, in Excel, well over 99% of the time, sorting means readjust the rows of information that you see within a list. There is an option which you'll see later in a later movie as to how you might occasionally want to move columns left or right. Before you sort a list, you want to make sure also, that on this worksheet, if there is other data, it should be separate or isolated. As I scroll to the right on this worksheet, there is other data out in columns N, and O, and possibly other data off to the right. But make sure that data is isolated from the data you're about to sort by at least one empty column. Now, if we want to move these rows up and down, first order of business is to make sure that the data that we're starting with has no empty rows within it or empty columns within it. If you work with a data often, you probably know that's not the case anyway. But if it's data you're not too familiar with, a quick little reminder, and you can do this at anytime as you click within a list. Click on any cell, first of all press Command + A. That will highlight not only that cell but all the contiguous cells around it. And now, an unlikely follow up, press Control + period four or five times. This will simply move the active cell around the corners of this highlighted range. Control + period, moves it to the upper left corner, that's in cell A1, I'll press Control + period again, we're at cell J1, again, down to the bottom. Now is that truly the bottom? If you are unfamiliar with the list, you might want to scroll just a bit further to see how it's looking. Looks okay. Control + period, Control + period. I would stress, you don't need to do that very often if you work with a list frequently. But do remind yourself that from time to time when you get data from other sources, others might have inserted some empty rows, possibly for printing reasons, or maybe they deleted the data in a row and didn't finish it. Just make sure you have no empty rows. It's highly less likely that you would have any empty columns but make sure they're gone too. Also, working with Excel, not only for sorting but also for filtering, and other data commands, make sure your titles are in a single row. That makes life a lot simpler and easier as well, too. If you do need to have extra data in that text, in for example, row one, maybe I want years of service here. In cell H1. I'm going to double click in cell H1, and right after the letter S, I'm going to type space O-F, and I want the word service to appear here. But I don't want the column to be really wide. So I'll hold down Command and Control as I press Return and then type service. And then return. Now, it does make the row taller, and I may or may not like that. But if I do want that text in there, this is much better than having this in rows one and two. Make sure your titles are in a single row. Now, as it turns out, maybe I really don't want to keep that. I'm going to press Control + Z, and we're back to this display. So, most of the time, well over 99% of the time, what does sort mean? Rearrange the rows, or to be a bit more specific, rearrange the partial rows that we see in this list. Remember, any other data in the worksheet should be separated by at least one column as it is here, and we've got that other data off to the right. And if you're a little bit nervous about sorting, remember, you will be able to undo if somehow you think something has gone wrong. The big concern would be something along the lines of could we somehow sort and rearrange only the data in one column. That would really cause a disaster here. That's very difficult to do. Let's also focus on the idea that if we are about to sort the data, we might keep our eye on the information in one row and see where it ends up and make sure that everything in that row has been moved along with it. So if we are ready to sort, just click within the data, and there are at least three ways to get started. From the home tab, far right button, Sort and Filter, we could click that button. And then click Custom Sort. It will lead to this dialog box here. Notice that in the background it's highlighted all that contiguous data. Now, I don't want to do this sorting from here. So I'll just choose Cancel. But remember that screen look. That dialog box. Another way to start the process is with the active cell within the data. We can go to the menu at the very top of the screen and choose Data, Sort. That takes us to that same dialog box. Once again, I'll choose Cancel. And the third way, probably the recommended way, because it's a bit more direct. Go to the Data tab within the ribbon. And choose the A-Z, the larger of the three sort buttons here. That, too, activates the same dialog box. If we really do want to sort this list, for example by hire date, in this dialog box here under column, here's an arrow. We'll choose hire date. And you wouldn't necessarily know at first how these are going to be ordered. But this suggestion here oldest to newest does make sense. We want the people who have been here longest to appear first. So, oldest to newest makes sense. Now, Bob Acosta, who's in row three there, he's probably one of those people that's going to turn up on the list pretty early. He was hired back in 97. We might remember one or two other things about him. He's in the West building, he's got a job rating of five, and we remember that maybe he was hired in April of 97. So, let's just click OK, remember, what sorting means. Most of the time, simply move those rows up and down, in this case, based on what we see in column G. And there it is. And Robert Acosta's in the list, he's in row seven now, but we see the same information about him has moved along with, not only his name, building, department, these other numbers here, 20 years of service, job rating, so on. Everything the same. That's what sorting means most of the time. And again, the basic idea is even though there is that one exception where you occasionally might want to sort by moving columns left and right, sorting nearly always means move rows up and down. Prepare your data ahead of time. Once again, if you work with it frequently, you don't have to worry about that very often. Make sure you have no empty rows or empty columns within the list, get those titles in a single row, and sorting much of the time is fast, easy, and extremely reliable.