Sorting Data

This chapter starts by looking at two background basics: the sorting order Numbers uses for letters, numbers, and blank cells; and Special Sorting Issues—how sorting interacts with headers, footers, merged cells, and hidden rows.

Next, I show you how to get Instant Sort Gratification using a table’s Column menu, and how to do more-sophisticated sorting in Sorting with the Inspector. Finally, you’ll learn how to Unsort Your Data and even Sort Unsortables.

Sorting Order

If you think sorting order is simple and obvious, then you need more variety in your sheets. Here’s how Numbers determines sorting order:

• Numbers: It’s no surprise that numbers get VIP treatment. Data formatting is ignored, so $2 comes after 1 and before 3, while 3% (since it’s .03) comes before 1.
• Dates: If some dates are identical, they are sorted according to their “hidden” time stamps (see Types of Data).
• Punctuation symbols: Standard punctuation symbols—those in the ASCII set—are sorted according to their codes (listed in the dec column of the printable code chart on that page).
• Alphabetic characters: Uppercase versions are more important than lowercase, so the sorting order is A, a, B, b, and so on.
• Numeric characters: These are numerals that aren’t used as numbers, as in: a combination like 1-a, numerals in merged cells, or numbers in cells formatted to hold text. The numeric characters still sort before alphabetical ones, but they come after “real” numbers, so you can get a sort order of 4, 1, 2, 3 if the latter three are in text-formatted or merged cells.
• Boolean values: When sorting cells that contain TRUE or FALSE as a result of an IF statement, a checkbox, or direct text entry, Numbers puts FALSE before TRUE. This might be philosophically upsetting, but it’s because FALSE is considered zero and TRUE is a one (that’s more comforting), so they’re in numerical order.
• Blank cells: Blank cells aren’t exactly part of the sorting order. Blanks always end up at the bottom whether you do an ascending or a descending sort.
• Formula cells: Cells with formulas in them are sorted according to their results—what’s displayed in the cell.

Note: When it comes to Option characters—characters you get by pressing Option and another key (with or without Shift), they are mostly interspersed among the punctuation and the alphabetic characters, with the remainder coming after the alphabet.

Tip: If your sort order isn’t working—for example, a 5 is coming after a 10 in a simple ascending sort—check the cell formatting in the Format Inspector’s Cell pane, in the Data Format pop-up menu. The 5 may be in a cell that’s formatted to hold text, so it’s being treated as text instead of a number.

Special Sorting Issues

My job is not only to explain the things you want to know, but also the things you didn’t know you wanted to know. This roundup is from the latter category:

• Headers and footers: These are excluded from a sort even if you’ve specifically selected an entire column.
• Hidden rows: These are included in a sort if you’re sorting the rows above and below them (Hide and Show Rows and Columns).
• Hidden columns: You can’t access the Column menu for a hidden column, but you can sort by a hidden column using the Sort & Filter Inspector. (Why would you want to? See Sort Unsortables.)
• Merged column (vertical) cells: You can’t sort any column, or selected area, that has vertically merged cells, since they combine cells from different rows. The sorting commands in the Column menu (that’s just ahead) are dimmed, as is the Sort Now button in the Sort & Filter Inspector. You can sort within a column that has merged cells if you select an area with no merges.
• Merged row (horizontal) cells: You can sort columns that contain merged cells that span other columns. Keep these points in mind:
  • If you try to select a portion of the table that includes a merged cell, Numbers automatically increases the selection to include cells from all columns spanned by the merged cell.
  • Numbers always sorts a merged cell according to its combined contents—which Numbers always treats as text even if it’s numerals. When you sort a column that contains the first (leftmost) cell of a merged cell, it’s obvious where that cell will fall in the sort. Less obvious is the fact that the leftmost part of the cell is still the sort criterion for any column it spans (even though those areas might look blank).
• Unsorting: You can’t unsort—you can only sort in a different way. You can undo a sort, of course, but you first must undo everything you did since the sort (such as, perhaps, data entry). This isn’t entirely unreasonable—Numbers assumes you’ll look at your data with various sort criteria, but not in any particular unsorted order. Sort Unsortables explains how to return to an unsorted order.
• Formulas: Formulas in headers or footers aren’t affected by sorting, because headers and footers aren’t included in the sorting process. A formula that refers to other cells in its own row is fine because the entire row moves in the sort. Everything else is unreliable whether it’s the formula that moves, or the cells it references.

Instant Sort Gratification

Instant sorts are a cinch. With the table active or selected, open any Column menu, and choose Sort Ascending or Sort Descending to sort all the rows in the table (Figure 77) by that column.

Figure 77: The Column menu’s sort commands.

You can subsort using Column menus, too (although you can do more sophisticated subsorts; see Sorting with the Inspector, just ahead).

Try it: Use the 13·Sort & Subsort sheet of The Hands-on Example Spreadsheet.

Make it: Create a table that contains three columns similar to those in the next figure: a Region column with cells that contain A, B, or C; a Team column that contains Green, Red, or Blue (don’t worry about the text color); and a Name column (single names are enough) with some alphabetic variety.

To subsort a table, start with the least important column and work your way up to the most important one. So, in the example table, for a final sort order of Region:Team:Name, you’d first sort by Name, then by Team, and then by Region (Figure 78).

Figure 78: Left: The table sorted by Name. Center: A subsequent sort by Team. Right: A final sort by Region.

Color-name Tricks

In the figure above, team names are sorted alphabetically, since a text format like font color isn’t a sortable quality; Sort Unsortables, however, explains how to customize such sort orders. The colors were applied to each team’s color name through conditional highlighting; see Conditional Highlighting to learn how that works generally, and the 2·Team Color Names sheet in The Hands-on Example Spreadsheet for the specifics of this color trick.

Sorting with the Inspector

Instant sorting isn’t your only option; you can also sort through the Sort & Filter Inspector. The simplest sort you can set up in the Inspector is a straightforward ascending or descending sort for the whole table based on one column. Sound familiar? Yes, that’s what you do when you instant-sort from a Column menu. But with the Sort & Filter Inspector, you can also:

• Sort the entire table or a subset of rows within it.
• Subsort by other columns without having to fuss with Column menus (and remembering to open them in reverse order).
• Store a multi-column sort to use, or edit, later.
• Sort by hidden columns.

Try it: This section refers to the 14·Sort Criteria sheet in The Hands-on Example Spreadsheet.

Make it: Create a table that contains three columns similar to those in the figures on the next few pages: a Region column with the letter A, B, or C in each row; a Team column with Blue, Green, or Red in each cell (you don’t need colored text); and a Name column with some names (full names aren’t necessary).

Sort Criteria

Setting up sort criteria in the Sort & Filter Inspector is a easy: specify a column and whether you want it sorted in ascending or descending order. With your table active or selected:

1. Go to the Sort & Filter Inspector’s Sort pane.
2. Click Add a Column and choose a column from its pop-up menu (Region, in the example table).

   If the table doesn’t have a header row, or the header cell is blank, the columns are listed by their labels—Column A, Column B, and so on.

The table is immediately sorted by the default Ascending, and a new section appears in the Sort pane defining the criterion you just created: one pop-up menu identifies the Sort By column and the other specifies an ascending or descending sort (Figure 79).

Figure 79: Left to right: The data as entered, the criterion in the Sort & Filter Inspector, and the sorted result.

For subsorts, define additional criteria in the Sort pane:

1. Click Add a Column again and choose another column. Once again, the sort is done instantly. (In your example table, choose Team, and the data is sorted by Region with a subsort by Team.)

   Note: While subsorting with Column menus is done “backward,” with the primary sort group sorted last (Team and then Region, for example), in the Sort & Filter Inspector, the primary group comes first (so you sort by Region, and then Team).

2. Add another subsort by once again clicking Add a Column and choosing another column. (In the example table, choose Name, as shown in Figure 80, for a final sort order of Region:Team:Name.)

Figure 80: Left: The result of a sort by Region with a subsort by Team. Center: The three criteria. Right: The result of all three sort criteria.

Try it: For anyone who just wants to see a sort criteria setup, the second table in 14·Sort Criteria, found in The Hands-on Example Spreadsheet, has everything already done.

Edit Sort Criteria

It’s easy to alter the criteria in the Sort & Filter Inspector’s Sort pane:

• Change the sort direction: Choose from the second pop-up menu for the criterion to swap Ascending and Descending.
• Delete a criterion: Hover your pointer near the top of the criterion until you see the trash button, and click it.
• Redefine subsorts: Hover your pointer over a criterion until you see the drag control at the upper left, and drag it to re-position the criterion in the list (Figure 81).

Figure 81: Left: Sorted by Region, with a subsort by Team. Middle: Reordering criteria by dragging. Right: After the swap, Team is the primary sort.

Tip: When you sort data, it’s a good idea to put descriptive column names in a header row (read Headers and Footers) even if you don’t otherwise need them. It’s much easier to select a column name from a Sort By menu than it is to keep double-checking whether you wanted to sort by column J, K, or whatever.

The Sort Now Button

If you’ve been following along in this section, or have used the Sort & Filter Inspector before, you may have noticed that the Big Blue Button—that is, the Sort Now button—seems extraneous, since all the sorts were done automatically as you created the sort criteria.

When you edit the sorted data, you’ll need the Sort Now button to trigger an updated sort. Open the Sort & Filter Inspector’s Sort pane and click the button.

Try it: The second table in the 14·Sort Criteria tab of The Hands-on Example Spreadsheet is waiting for you to click its Sort Now button.

Sorting Only Part of a Table

You’ll probably use the default Sort Entire Table option at the top of the Sort pane 99% of the time. For the other 1%, select the data you want sorted within the table, set the pop-up menu to Sort Selected Rows, and click Sort Now.

It’s usually easiest to select the rows by dragging across their labels, but you can select any contiguous cells that span several rows—you don’t have to select the whole row. The sort, however, affects the entire row, so your data stays together the way it should.

Special Solutions for Special Issues

Two sorting problems frequently rear their ugly little heads when you start sorting your data in various ways. One is when you want to return the data to the order it was in before you sorted it, but there’s no column that can sort it that way. The other is the need to sort by a column whose contents can’t be sorted “naturally,” such as grades that include A, A-, and A+.

Luckily, there are solutions for both situations coming right up.

Unsort Your Data

Try it: Use 15·Unsort sheet in The Hands-on Example Spreadsheet.

Make it: Use any table you’ve made, or from one of Numbers’ templates, that has sortable data—which is probably 99.99% of tables. The data doesn’t need to be sub-sortable.

Hogwarts doesn’t have an Unsorting Hat, but you do, sort of: you can unsort your data if you plan ahead. To get back to a specific, yet not sortable, order (perhaps the order in which you entered your data, or maybe in a personal-preference order, as with categories in an expense table), you start by making a column that keeps track of how your rows were arranged before you do any sorting:

1. Create a column at the end of the table with a click of the Column handle, and type Unsort in its header cell.
2. Autofill the column with consecutive numbers starting at 1. (If you later add rows to your table, you’ll need to continue filling down the numbers in the Unsort column—after you unsort what’s already there so that the numbers will still be properly consecutive.)
3. Hide the column by choosing Hide Column from its menu.

When you sort the visible columns in your table, the Unsort column keeps track of the original order (Figure 82).

Figure 82: Left: An Unsort column has been added to a table and filled with consecutive numbers. Right: The same table, after sorting and subsorting the data columns.

To return the table to its unsorted state, you can:

• Use the C0lumn menu: Choose Unhide All Columns from any Column menu so you can see the Unsort column, and then sort the table using the Unsort column’s menu).
• Use the Sort & Filter Inspector: If you already have sort criteria for your table, use the Inspector to add a criterion that sorts by the Unsort column. It will go to the bottom of the list, where it won’t have any effect on the current sort: it acts as a final subsort, and since each of its values is unique, nothing can be subsorted.

  To trigger the unsort, hover over the criterion to reveal the drag control, and drag it to the top of the list, where it becomes the primary sort. To return the original sort/subsort order, drag the Unsort criterion down to the bottom again, where it will wait patiently until you need it again.

Sort Unsortables

An “unsortable?” Say you have a table of student grades ranging from F to A+. You want to sort by grade, but Numbers’ default sort order is A, A-, A+ and so on, which is not very helpful. Or you want to sort your baseball card collection according to the condition of each item, which can range from Excellent through Very Good, Good, Poor and Very Poor. Imagine sorting that alphabetically instead of logically!

Even the example I used in this chapter for subsorts could be problematic in real life: the colors sort only alphabetically—Blue, Green, Red. If team colors or other elements in a table have some ordered meaning in the context of your data, you might need to sort them by importance.

These, and many other situations, call for hidden column sorting, similar to the “unsorting” column described just above. The difference is that the sort-by column you’re going to hide uses some formula to rank unsortable items instead of autofilled consecutive numbers.

There are many ways to assign numeric values to data in a table. Consider, for instance, the table used as an example in Instant Sort Gratification, earlier, where team names are Blue, Green, and Red and by default sort alphabetically. There are two easy ways you could assign a different sort order:

• Use word length: This technique takes advantage of the coincidence of Red, Blue, and Green having different word lengths, and sorts them by letter count using the LEN (length) text function, described in Split Text Strings. The formula is simply LEN(A2), where the cell reference points to the column where the team name is stored.
• Use a nested IF statement: This method assigns a value to a cell based on what’s in the Team column to achieve any desired order. The formula IF(A2="Green",1,IF(A2="Blue",2,3)) basically says “If A2 is Green, use a 1; if it’s Blue, use a 2; otherwise use a 3” (which means Red is assigned a 3). IF statements are covered in IF Statements, and nested functions in both Nested Functionsand Nested IF Statements.

Try it: The 16·Color-Sort Formulas sheet in The Hands-on Example Spreadsheet shows these two approaches in action.