Search and sort data

search and sort data

Any good database has a means for quickly locating certain data, and Excel is no exception. Excel offers several Find methods.

As you saw previously, you can use the data-entry form to find records with certain criteria by using the Criteria button from the form dialog box (Data > Form). You can also use the regular Find command to find data, just like in any other Office application.

You can either find instances of a string one at a time, or all at once. (String in this case can be any combination of letters, numbers, symbols, and punctuation.)

This method is good for browsing through the database to find instances of a particular string. To find using the Find command:

1. (Optional) Click the cell where you want to begin. This is only significant if you want to start your search from a certain place; otherwise, it doesn't matter.
2. Choose Edit > Find. The Find and Replace dialog box opens.
3. Enter the string you want to find.
4. Click the Options button for more find options, and set any of these as desired. For example, you can search by rows or by columns; you can match case; you can limit the search to just one sheet or search all the sheets; and so on, as shown in Figure 6-6.

TIP
There's a lot of complexity to the Find options, and there isn't room to address them all completely here. You may want to explore them on your own. For example, you can click the Format button to find certain formatting.

Figure 6-6: Set any options you want.

5. Click Find Next; the cell cursor jumps to the first instance following the position that it was in when you started.
6. Keep clicking Find Next to move to other instances until you find the one you want.

The Find All method is good if you want to see at a glance how many instances of the specified string appear in the worksheet, and jump freely among them.

1. Perform Steps 1 -- 4 of the preceding section's steps to specify the string you want to find and set any options.
2. Click Find All. A pane appears in the bottom of the dialog box listing all the found instances, as shown in Figure 6-7.
3. Click an instance on the list to jump to it in the worksheet.
4. When you're finished, click Close to close the dialog box.

Figure 6-7: Find All shows you a list of all found instances at once.

For a simple sort by a single field (column), you can use the Sort buttons on the toolbar. (They're the AZ and ZA buttons.) This is useful when you need a quick look at a particular field, and can be a good alternative to using Find to locate what you want. For example, suppose you want to see whether there is anyone in your database with a ZIP Code of 46060. You could sort by ZIP and then scroll down the list to see.

TIP
If you think you may want to get the records back to their original order at some point, add a field (column) containing numbering (use AutoFill), and then you can always re-sort by that column.

To sort by a single column, follow these steps:

1. Click any cell within that column. Do not select a range.
2. Click the Sort Ascending (AZ) or Sort Descending (ZA) button on the toolbar.
3. Excel is smart enough to know that you want the surrounding columns to move along with the selected one, so your data columns don't get out of synch with one another.
4. You can also sort by multiple fields. For example, perhaps you want to sort by Last Name, and then to break the tie with the First Name field in the event that two people have the same last name.
5. To sort by multiple columns, follow these steps:

1. Click anywhere within the data range. Do not select a range.
2. Choose Data > Sort. The Sort dialog box appears.
3. Open the Sort By list and choose the first field to sort by. If you have set up field names in the first row (and you should have), those field names appear on the list. If not, the column letters appear on the list.
4. Open the Then By list and choose the second field to sort by, as shown in Figure 6-8. Continue entering additional layers of sorting as needed.
5. Click OK. The sort occurs.