Join Dennis Taylor for an in-depth discussion in this video Sorting based on the order of data in custom lists, part of Managing and Analyzing Data in Excel 2010.
- View Offline
Sometimes you need to sort data based not on alphabetical or numerical order, but on the order of data as it appears in a list. And it could be one of Excel's built-in lists or it could be a list that you've created. As we look at this particular worksheet, we might want to sort the data based on the information in column H. Column H contains formulas. Take a look at cell H2. So I double-click it. It looks like it's a rather complex formula but that's actually pretty easy. It simply pulls out the month of each of these Hire Dates.
And perhaps what we're aiming for here is a list that's sorted alphabetically by month so we can just know when someone is having an anniversary month we want to print that data out. So we'd like to sort based on the data in column H, and if we're in a hurry we can certainly use the AZ button as we see it on the Data tab here. It's going to sort the data based on the data in column H. And there we are and April comes first. But wait a minute here! Aren't we thinking, shouldn't January be first? Well, no, this is an alphabetic sort. We see April, then we see August, then December. We didn't do anything special to indicate to Excel that there is anything unusual here, but we do have at our fingertips a way to sort this data chronologically.
We must use the Sort command. It's on the Data tab just to the right of the AZ, ZA buttons. Let's click this again. We do want to sort by Month, we want to sort on Values, but not that A to Z order, because built into Excel is a custom list and we do see the months abbreviated and full spellings. We have full spellings here. This is the one we'll use. We want to sort chronologically, not alphabetically, and by making this choice and clicking OK, that's exactly what happens. Easy and fast.
So anytime you need to sort on a column that's got monthly names, either abbreviations or full spellings or possibly day of the week, you probably want to chronological sort in both cases. That's what we've done here with these custom lists. Now, there are other situations too where you might need to sort in a particular order. I'm going to sort this data based on what we see in column F. A quick AZ sort again from the Ribbon on the Data tab and the Contract people come first. Remember, there are also, as you see this here on the screen, Full Time, Half-Time, Hourly.
Well, I think a lot of us will want to say, well, let's put the full time people first. They run the company and I want to see them first here. If we did a reverse sort, ZA, that's not going to help either. We'd like to sort in a particular order. On another sheet, and you don't necessarily have to have the data be displayed here, I've got the actual statuses listed here in the order that I would like to have them appear. I'm thinking ahead too that I might need this in the future. So what I'm about to do is to take this data and make it be an entry in a custom list along with the days of the week and the months of the year that we've already seen.
So with this data highlighted and you don't really have to do it this way and you might be prepared to type this yourself. And if you're using Excel 2010, click the File tab in the ribbon and then choose Options. If you're using Excel 2007, click the Office button, and then click Excel Options, and in 2007 you'll see a choice right about here on the screen that indicates Custom Lists. 2010, we need to go to the Advanced tab and scroll way down to the bottom of this and you'll see this box, Edit Custom Lists.
Let's click it and here are those built-in lists that we saw earlier. Let's now add by importing the data that we've highlighted. If you didn't have those items on a worksheet like we have here, you would type them right here where it says List Entries. In other words, type Full Time, press Enter, Half-Time, and Enter and so on. Now we can just import them. Now, this list will stay here indefinitely. I'm going to click OK. If the next exit that I do from Excel is a normal one, we won't worry about it.
That list is around forever. What it means is when we want to sort data in a particular way, like here, we will use the Sort command. We do want to sort by Status, we want to sort by Values, but not that A to Z order. Click the drop-arrow, go to Custom List, and that latest entry in our Custom Lists, the one we put in, Full- Time, Half-Time, Hourly, Contract. That's the order I want. Click OK, click OK, and the full-time people come first.
So whenever necessary, you can override standard sorting order by creating a custom list or referring to one of the built-in custom lists in Excel.
- Multiple key sorting
- Single and multiple column numeric filters
- Creating a top-ten list with values or percentages
- Setting up subtotals
- Creating multiple-field criteria filters
- Creating unique lists from repeating field data
- Using the Remove Duplicates command
- Finding duplicate data with specialized arrays
- Counting the number of unique items in a list
- Using SUMIF and COUNTIF functions
- Working with the database functions such as DSUM and DMAX