Problem: I have sales data in a worksheet. I would like to sort the data by product within customer.
- Sort by product within customer.
Strategy: Here's what you do:
- Select one cell within your data. The one cell can be in the heading row or any data row. Select Data, Sort. Whereas Excel 2003 only allowed sorting by three fields, Excel now offers up to 64 sort levels. Rather than the old dialog with the three fields, you now start with one field and add levels as necessary.
- Choose Sort By dropdown, Customer.
- Click the Add Level button. A new row will appear in the Sort dialog. Choose Then By dropdown, Product.
- Build as many sort levels as necessary.
- Leave the Sort On and Order dropdowns at their default values. If, for some reason, you wanted the customers sorted in descending alphabetical order, you could change A to Z to Z to A. That might make more sense if you were sorting by revenue, but it is not likely that you need the customers sorted in reverse alphabetical sequence. If your data is set up correctly as outlined in "How to Set up Your Data for Easy Sorting and Subtotals," Excel will properly guess that your list has a header row.
- Click OK to sort. Because Customer was the first sort key, all the records for "ABC Company" will sort to the top. Records for "ABC GMbH" will appear next.
- The data is sorted.
Additional Details: When there is a tie-for example, the four records for "ABC GMbH"-those records will be sorted in ascending order by the product field. For instance, the ABC product record appears before the DEF product field. If there is still a tie, the records will remain in their original sequence from before the sort.
Alternate Strategy: If your data is properly set up in list format, you can select a single cell in the data and then use the AZ or ZA buttons on the Data tab.
Note that these same icons are also in the Sort & Filter dropdown on the Home tab. If you don't want the extra click of opening the dropdown or going to the Data tab, you can add the icons to the Quick Access Toolbar.
- Sorting icons on the Data tab.
If you use either method, Excel will sort the data by the column in which the cell pointer is currently located. Because Excel resolves ties by leaving the previous sequence in place, you can sort by product within customer. First, you select a cell in the Product field and click AZ to sort by product. Next, you select a cell in the Customer field and click AZ to sort by customer. The data will be sorted by customer, with ties sorted by product.
You can click the ZA button to sort in descending order.
Gotcha: Before you try any sort operation, you must select either the entire range or a single cell in that range. If you mistakenly choose two cells in a range, Excel will sort just those selected cells, resulting in a few cells of your data being sorted within records-a disastrous result. Excel now warns you when you attempt to sort a subset of the cells.