Join Dennis Taylor for an in-depth discussion in this video Sorting from menu icons, part of Excel 2013: Managing and Analyzing Data.
- View Offline
If you need to sort data on the basis of information in just one column or two columns, it's often faster to achieve sorting by way of these two separate buttons on the Data tab, the AZ button sorts in ascending order, or lowest to highest, ZA button highest to lowest. In this example here, in this particular worksheet called Icon Sort, right now the data is in order by employee name. If we would like to rearrange the order, in other words, rearrange all the rows so that it's in order by department, we could simply have the active cell somewhere in the column.
Now, you can click C1 or C5, doesn't make any difference which one of these. Do not click the column. In a bit, we'll explain why. Come back here, click on any one of these cells, and click the AZ button for an ascending order sort. The entire list is reordered. Now, recognize that not only did we sort the data here based on what was in column C, but the previous order of the data is still active in the sense that within this group here for department called ADC, the names are in alphabetical order.
And within this group and all subsequent groups too, the department admin training, the names are in alphabetical order. So, when you click either of these buttons, the AZ or ZA button, it means the data is going to be rearranged on the basis of information in that column. But the previous order of the sort is retained or remembered you might say. So, if we want to sort this list by status right now, I'll click somewhere in column F, then as I press AZ, keep in mind that right now the data is in order by department.
So, if click AZ, all the contract people are together, but within this list here of contract people you can see that they are in order by department. And sure enough, within each department, they are in order alphabetically by name, so that was the previous sort. So, if you sort frequently, using these AZ and occasionally, ZA buttons are handy and the key thought is that the last use of this overrides the previous ones. So, if we need to sort this data on the basis of what's in two columns.
Remember, the last sort will override on all previous ones. So, if someone says, I'd like to have a list of names by departments here, but I want to make sure that within each department, it's an order by most years of service. Then what we first do is click in column I in this example. We'll do a descending sort here, that means high to low, ZA. And for the moment, the entire list is in order based on years. If we now click in column C, and choose AZ, alphabetically, then we've got all information grouped by department.
And within each department, the list is in order, descending order, based on years of service. So, there it is for the ADC group. Here it is for Admin Training. Now, if you clicked the entire column, as I suggested earlier you do not, if you then click either of these buttons, you get a warning. Now, you would have to click the second button to cause some real concern here but if you do click the second button it says, continue with the current selection. If you were to rearrange only the order of one of these columns, I think in nearly all cases here, it would be chaotic.
There are some exceptions to that rule. What we're talking about here is, we don't want to really rearrange just column C. In older versions of Excel, back in Excel 97, you could do this somewhat easily, and it was a big problem at times. There are rare exceptions to the idea that you might want to do this. So, nearly always in this situation, you do not want to sort that column only. So, to start the process quickly, just click on a single cell. So, sorting by using the buttons here is a lot faster if you're pretty adept at sorting, you've gotten familiar with the concepts and you sort frequently.
This might be the better way to go. Certainly, if you're sorting by one column and that's it, fine, this is much faster than going through the full fledged Sort command.
- Multiple key sorting
- Filtering single and multiple columns
- 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
- Converting lists to tables