20. Sorting Data
This chapter shows you the various ways you can sort your data, even by color. Sorting data is a significant capability in Excel, enabling you to view data from least to greatest, greatest to least, by color, or even by your own customized sort listing.
Sorting Excel Data
Excel lets you sort your cell data in a variety of ways: by value, cell color, font color, or conditional formatting.
Preparing Data
Your data should adhere to a few basic formatting guidelines to make the most of Excel’s sorting capabilities:
• There should be no blank rows or columns. The occasional blank cell is acceptable.
• Every column should have a header.
• Headers should be in only one row.
If these guidelines aren’t followed, Excel can get confused and is unable to find the entire table or header row on its own. Also, Excel can only work with one header row—any rows after the first header row get treated like data.
Opening the Sort Dialog Box
The Sort dialog box allows up to 64 sort levels. Through the dialog box, you can sort multiple columns by values, cell color, font color, or by conditional formatting cell icons. The sort order can be ascending, descending, or by a custom list. If your data has headers, they are listed in the Sort By drop-down list; otherwise, the column headings (A, B, C, and so on) are used.
There are four ways to access the Sort dialog box:
• On the Home tab, select Custom Sort from the Sort & Filter drop-down list.
• On the Data tab, select Sort from the Sort & Filter drop-down list.
• Right-click any cell and select Sort, Custom Sort.
• From a filter or Table drop-down list, select Sort by Color, Custom Sort.
Sorting by Values
When you use the Sort dialog box, Excel applies each sort in the order it appears in the list. In Figure 20.1, the Region column is sorted first. The Customer column is sorted second, as outlined in the following steps:
1. Ensure that the data has no blank rows or columns and that each column has a one-row header.
2. Select a cell in the data. Excel uses this cell to determine the location and size of the data table.
3. On the Home tab, select Custom Sort from the Sort & Filter drop-down list to open the Sort dialog box.
4. Make sure the My Data Has Headers check box is selected. Excel does not select the headers themselves, only the data.
5. Make sure all the data’s columns and rows are selected. If they are not all selected, a blank column or row exists, confusing Excel as to the size of your table. Exit from the Sort dialog box, delete the blank columns and rows, and start the process again.
Note
If for some reason you can’t delete the blank columns or rows, then preselect the entire table before opening the Sort dialog box.
6. From the Sort By drop-down list, select the first column header, Region, to sort by.
7. From the Sort On drop-down list, select Values.
8. From the Order drop-down list, select the order by which the column’s data should be sorted, A to Z.
9. To add another sort column, this time for Customer, click Add Level and repeat steps 6 to 8. Repeat these steps until all the columns to sort by are configured, as shown in Figure 20.1.
10. If you realize that a field is in the wrong position, use the up or down arrows at the top of the dialog box to move the field to the correct location.
11. Click OK to sort the data.
When you look at the data after it is sorted, notice the regions are grouped together; for example, Central is at the top of the list. Within Central, the customer names are alphabetized. If you scroll down to the next region, East, the customer names are alphabetized within that region. If the data should have listed the customers and then the regions, the two sort fields need to be switched so that Excel sorts the Customer field first and the Region field second.
Sorting by Color or Icon
Although sorting by values is the most typical use of sorting, Excel can also sort data by fill color, font color, or an icon set from conditional formatting. You can apply fill and font colors through conditional formatting or the cell format icons.
In addition to sorting colors and icons through the Sort dialog box, the following options are also available when you right-click a cell and select Sort from the context menu:
• Put Selected Cell Color on Top
• Put Selected Font Color on Top
• Put Selected Cell Icon on Top
If you use one of the preceding options to sort more than one color or icon, the most recent selection is placed above the previous selection. So, if yellow rows should be placed before the red rows, sort the red rows first, and then sort the yellow rows.
In Figure 20.2, conditional formatting was used to highlight in red the top 10 profit record and yellow was used to highlight the ones in the bottom 10%.
FIGURE 20.2 You can use the Sort dialog box to sort cells by more than just their values, such as the cell color.
The data was then sorted using the following steps so that the red cells are at the top and the yellow directly beneath:
1. Ensure that the data has no blank rows or columns and that each column has a one-row header.
2. Select a cell in the data. Excel uses this cell to determine the location and size of the data and highlights what it sees as the data table.
3. Right-click the cell and select Sort, Custom Sort.
4. Make sure the My Data Has Headers check box in the upper-right corner is selected. Excel does not select the headers themselves.
5. Make sure all the data’s columns are selected. If they are not all selected, a blank column exists, confusing Excel as to the size of your table.
6. From the Sort By drop-down list, select the first column header to sort by, Profit.
7. From the Sort On drop-down list, select Cell Color.
8. From the first Order drop-down list, select the color by which the column’s data should be sorted.
9. From the second Order drop-down list, select whether the color should be sorted to the top or bottom of the data. If you select multiple colors to sort at the top of the data, the colors still appear in the order chosen.
10. Click Add Level to include the yellow Profit cells in the sort and repeat steps 6 to 9. Repeat these steps until all the columns to sort by are configured.
11. If you realize a field is in the wrong order, use the up or down arrows to move it to the correct location.
12. Click OK to sort the data.
Tip
If your data is formatted as a Table (Insert, Tables, Table), you don’t have to go through the Custom Sort dialog box. Instead, click the arrow in the header, go to Sort by Color and select the color you want sorted to the top of the table.
Performing a Quick Sort
If your sorting needs are simple, you can perform a quick sort on one or more columns.
Using the Quick Sort Buttons
Caution
Sort options are retained for a sheet during a session. So if you set up a custom sort with the Case Sensitive option turned on and then do a quick sort, the quick sort is case sensitive.
The quick sort buttons offer one-click access to sorting cell values. They do not work with colors or icons. There are four ways to get to the quick sort buttons:
• On the Home tab, select Sort A to Z or Sort Z to A from the Sort & Filter drop-down list.
• On the Data tab, select AZ or ZA from the Sort & Filter drop-down list.
• Right-click any cell and select Sort, Sort A to Z, or Sort Z to A.
• From a filter drop-down list, select Sort A to Z or Sort Z to A.
The quick sort buttons are very useful when sorting a single column. When sorting just one column, make sure you select just one cell in the column. If you select more than one cell, Excel sorts the selection, not the column. It prompts to verify that this is the action you want taken before doing it. Also ensure there are no adjacent columns or Excel will want to include them in the sort. If there are adjacent columns, select the entire column before sorting.
If you use the quick sort buttons to sort a table of more than one column, Excel sorts the entire table automatically. Because there is no dialog box, it’s very important that every column have a header. If just one header is missing, Excel does not treat the header row as such and includes it in the sorted data.
Tip
If you have filters turned on for the table, Excel automatically treats the row where the filter arrows are as the header row.
Caution
The actual button text might change depending on the type of data in the cell. For example, if the column contains values, the text is Sort Smallest to Largest. If the column contains text, it is Sort A to Z.
Quick Sorting Multiple Columns
If you keep in mind that Excel keeps previously organized columns in order as new columns are sorted, you can use this to sort multiple columns. For example, if the Customer column is organized, Excel doesn’t randomize the data in that column when the Region column is sorted. Instead, Customer retains its order to the degree it falls within the Region sort. The trick is to apply the sorts in reverse to how they would be set up in the Sort dialog box.
To manually perform the “Sort by Values” example shown in Figure 20.2, follow these steps:
1. Make sure all columns have headers. If even one column header is missing, Excel does not sort the data properly.
2. Select a cell in the column that should be sorted last, the Customer column.
3. Click the desired quick sort button in the Sort & Filter group on the Data tab.
4. Select a cell in the next column, Region, to be sorted.
5. Click the desired quick sort button in the Sort & Filter group on the Data tab.
6. The table is now sorted by Region and then Customers within each region.
Fixing Sort Problems
If it looks like the data did not sort properly, refer to the following list of possibilities:
• Make sure no hidden rows or columns exist.
• Use a single row for headers. If you need a multiline header, either wrap the text in the cell or use Alt+Enter to force line breaks in the cell.
• If the headers were sorted into the data, there was probably at least one column without a header.
• Column data should be of the same type. This might not be obvious in a column of ZIP Codes where some, such as 57057 are numbers, but others that start with 0 are actually text. To solve this problem, convert the entire column to text.
• If sorting by a column containing a formula, Excel recalculates the column after the sort. If the values change after the recalculation, such as with RAND, it might appear that the sort did not work properly, but it did.