• Create BookmarkCreate Bookmark
  • Create Note or TagCreate Note or Tag
  • PrintPrint
Share this Page URL

Chapter 10. Working With Lists > Filtering a List

Filtering a List

Once a list gets to be of any significant length, you may want to be able to selectively view rows that meet specific criteria. Excel allows this by temporarily hiding the rows that do not meet your criteria. Unfortunately, the method doesn't work while using the List Manager.

To find certain values in a list:

If you're using List Manager, copy the list to a new worksheet by selecting the entire list, choosing Edit > Copy, choosing File > New, and choosing Edit > Paste.

Leave at least one cell in the list selected. It's okay to leave the entire list selected, which it will be after the Paste operation.

Choose Data > Filter > AutoFilter (Figure 10.17). Arrows for criteria selection appear next to each column head (Figure 10.18).

Figure 10.17. Invoking AutoFilter.

Figure 10.18. AutoFilter arrows appear next to column heads.

Click and hold on the AutoFilter arrows next to the title of the column in which you want to apply a filter.

You can hide rows of a column containing cell values that don't meet your selected criteria. Simply choose those values from the pop-up list (Figures 10.19, Figure 10.20).

Figure 10.19. Choose a cell value

Figure 10.20. All rows with any other value are hidden.

To show all the rows again, click and hold on the arrows again, and choose Show All (Figure 10.21).

Figure 10.21. Show All reveals hidden rows.

All the hidden rows are now visible again (Figure 10.22).

Figure 10.22. All rows are visible again.

To select rows based on more complex criteria, click and hold on the arrows, and choose Custom Filter (Figure 10.23).

Figure 10.23. Invoke custom filtering for the column.

In the Custom AutoFilter dialog box (Figure 10.24), you can specify up to two criteria for that column, using comparisons to a value (for example, "greater than") and connect them by And or Or. If you choose And, only rows that meet both criteria are shown (Figure 10.25). If you choose Or, rows that meet either criterion are shown.

Figure 10.24. Choose criteria for filtering.

Figure 10.25. The list is filtered by the criteria chosen.



Not a subscriber?

Start A Free Trial

  • Creative Edge
  • Create BookmarkCreate Bookmark
  • Create Note or TagCreate Note or Tag
  • PrintPrint