One of the most important tasks in database queries is the request to show some records and hide the others. This let's you see subsets of the total when these sub-collections are identified by criteria you specify. While Excel does not have the full array of selection and display features of powerful database programs, it can handle many tasks very well. Let's look at a table of data and explore the filter process on that table.
We begin by selecting a column. We will start with Column C. We anticipate the need to separate the "men from the women" for some task.
We
first click on Column C to select all of the entries in
that cell.
Then we click on Data in the Menu and highlight the Filter choice.
We follow the right arrow and select the AutoFilter choice circled above.
This combination of choices will bring out the Filters box and place it on the cell containing the column heading. Note the down-arrow indicating there are choices to be made.
 |
 |
Select
the F choice |
The Filter will
display all of the rows where Sex = F. Note the numbering scheme in Column A is not dynamic...these are the original values that were in the cells of Column A. |
 |
 |
Select
the M choice |
The
Filter will display all of the rows where Sex = M. |
In a similar manner, you could set a Filter on the Grade column and easily select all students in a particular class.
We now turn our attention to additional filters you can use. This process offers some very powerful Database query techniques and may be sufficient for many database uses without having to migrate to a full database program.
Note
that we have removed the Filter in Column C and added one in Column
F under the Scores column.
So far, the search criteria has been using the "equals" relationship. We will explore the possibilities of filtering on other relationships.
 |
 |
| Select Custom. | The Custom AutoFilter Panel will appear. Note the down-arrow. |
You may select from several different relationships for a filter. Highlight the relationship of choice and click OK to select it. |
 |
This lets you specify a custom criteria. In this case, we are asking to show only rows having a Score >= 90 Note the And/Or choice. This means you could combine two custom relationships with a filter like: >= 70 and <=90. |
 |
This would be the result of applying the Custom Filter of Score Greater Than or Equal to 90.
Return | See a Movie of the Process