Filtering Data | Collecting and Cleaning Up Data

Problem

You have a spreadsheet that contains a large table of data and you'd like to be able to filter the data so you can view smaller subsets at a time.

Solution

Use Excel's built-in filter capability.

Discussion

Figure 3-19 shows a spreadsheet that contains atomic weights for all elements and their isotopes.

The data used in this example was downloaded from the freely available Atomic Weights and Isotopic Compositions database on the National Institute of Standards (NIST) and Technology web site, http://www.nist.gov. The data shown here is an abridged version of the full dataset prepared by J. S. Coursey, D. J. Schwab, and R. A. Dragoset, which is available on the NIST web site.

Figure 3-19. Unfiltered data

This dataset consists of nearly 3,000 records. Clearly it would be cumbersome to search for a particular element by having to manually scroll through all of the data. To make looking up specific records easier, you can filter the data.

The easiest way to filter data like this is to simply select Data images/U2192.jpg border=0> Filter images/U2192.jpg border=0> AutoFilter from the main menu bar. Excel will automatically scan your data and set up drop-down listboxes above each column, allowing you to filter the dataset. Figure 3-20 shows the spreadsheet with autofiltering turned on.

Notice the little down arrow buttons located adjacent to the column headings. Excel populates these drop-down listboxes with filter options automatically determined from your data. For example, Figure 3-21 shows what's in the listbox for the Isotope column.

Figure 3-20. Autofiltering active

You can select any element listed to apply a filter such that only records with the selected element in the Isotope column will be shown. Figure 3-22 shows the filtered list with the element O (oxygen), selected.

There are a few things to keep in mind when using autofiltering:

Excel detects your dataset automatically and assumes it ends when it encounters a blank row. Therefore, be sure not to include any blank rows in your data, so as not to confuse Excel.
Filtered lists are shown with row numbers in blue.
Filtering does not delete any data. Rows of data that don't meet the filter criteria are merely hidden.
You can add rows of data to the list simply by inserting or appending them to your existing data range.
You can perform calculations on your filtered data just as you normally would. For example, you could include a column of calculated fields in column D.
You can turn autofiltering off at any time by selecting Data images/U2192.jpg border=0> Filter images/U2192.jpg border=0> AutoFilter again.

Figure 3-21. Isotope filter

Figure 3-22. Filtered list

Occasionally, you may find you require a bit more flexibility when setting filter criteria. For example, say you wanted to see the isotopes for both hydrogen and oxygen at the same time. You can achieve this by setting up custom filter criteria. Take another look at Figure 3-21 and notice the third item in the exposed filter list is "(Custom ...)." You can select this item to open the Custom AutoFilter dialog box (shown in Figure 3-23).

Figure 3-23. Custom AutoFilter dialog box

In this case I set up a custom filter to show all records whose Isotope column contains O or H. The drop-down listboxes showing "equals" in Figure 3-21 allow you to select other criteria as well, such as "equals," "not equal," "greater than," "less than," "begins with," and "contains." This gives you quite a bit of flexibility. Pressing OK at this point will show a filtered list containing all records whose Isotope column contains O or H.

You should also be aware that you can specify criteria in more than one column at a time. Doing so will instruct Excel to show all records that meet the criteria specified in the first column and the next column and so on. Combine this capability with custom filters and you have a handy filtering mechanism at your disposal.