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.
Use Excel's built-in filter capability.
Figure 3-19 shows a spreadsheet that contains atomic weights for all elements and their isotopes.
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:
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.
Occasionally your data may be so complex that you require more advanced filtering capabilities. Excel facilitates this; see the help topic "Filter by using advanced criteria" in Excel's online help for more information.
Getting Acquainted with Visual Basic for Applications
Collecting and Cleaning Up Data
Time Series Analysis
Curve Fitting and Regression
Numerical Integration and Differentiation
Solving Ordinary Differential Equations
Solving Partial Differential Equations
Performing Optimization Analyses in Excel
Introduction to Financial Calculations