O'Reilly logo

Excel Scientific and Engineering Cookbook by David M Bourg

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

3.10. Filtering 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.

Tip

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.

Unfiltered data

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 → Filter → 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 ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required